Hello

I use the code below to open and run ipconfig /all to file. It woks fine on
my laptop but is hit-and-miss on other laptops, sometimes running; sometimes
producing Enter only; sometimes nothing more than the open cmd window. Is
there a way to ensure the code does its job? I was thinking of While Dir
produces nothing and shortening the wait time but the thought of the
SendKeys disappearing into buffers or elsewhere worries me - running the
code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & " {ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to 1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

Steve,

Here is an alternate approach which is a VBA adaption of a vbScript I use.
In the example, I simply have the output placed on the active sheet in
Column A beginning at row 1 but that is all pretty easy to modify.

'--------------------------------------
Sub RunIPCONFIG()
Dim r As Integer
Dim strLine As String
Set wsh = CreateObject("WScript.Shell")
Set wshExec = wsh.Exec("ipconfig /all")
Set objStdOut = wshExec.StdOut
r = 1

Do Until objStdOut.AtEndOfStream
strLine = objStdOut.ReadLine
Cells(r, 1).Value = strLine
r = r + 1
Loop

Set wshExec = Nothing
Set wsh = Nothing
End Sub


'--------------------------------------

Steve Yandl



"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks fine
on my laptop but is hit-and-miss on other laptops, sometimes running;
sometimes producing Enter only; sometimes nothing more than the open cmd
window. Is there a way to ensure the code does its job? I was thinking of
While Dir produces nothing and shortening the wait time but the thought
of the SendKeys disappearing into buffers or elsewhere worries me -
running the code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & " {ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to 1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

Thank you. I am sure I tried something similar before asking the question.
The laptops seem to have scripting locked on user accounts. I need to run
the audit from user logon ... I plan to try again with your code on the slim
chance I did something wrong before.

--
Steve

"Steve Yandl" wrote in message
...
Steve,

Here is an alternate approach which is a VBA adaption of a vbScript I use.
In the example, I simply have the output placed on the active sheet in
Column A beginning at row 1 but that is all pretty easy to modify.

'--------------------------------------
Sub RunIPCONFIG()
Dim r As Integer
Dim strLine As String
Set wsh = CreateObject("WScript.Shell")
Set wshExec = wsh.Exec("ipconfig /all")
Set objStdOut = wshExec.StdOut
r = 1

Do Until objStdOut.AtEndOfStream
strLine = objStdOut.ReadLine
Cells(r, 1).Value = strLine
r = r + 1
Loop

Set wshExec = Nothing
Set wsh = Nothing
End Sub


'--------------------------------------

Steve Yandl



"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks fine
on my laptop but is hit-and-miss on other laptops, sometimes running;
sometimes producing Enter only; sometimes nothing more than the open cmd
window. Is there a way to ensure the code does its job? I was thinking of
While Dir produces nothing and shortening the wait time but the thought
of the SendKeys disappearing into buffers or elsewhere worries me -
running the code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & "
{ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to 1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

SendKeys is unreliable at the best of times and even more dodgy with the
command window. Why not pipe the results of ipconfig to a text file with the
Shell (and use vbHide), then open the text file in notepad or even directly
into cells. Post back if not sure how to do that.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks fine
on my laptop but is hit-and-miss on other laptops, sometimes running;
sometimes producing Enter only; sometimes nothing more than the open cmd
window. Is there a way to ensure the code does its job? I was thinking of
While Dir produces nothing and shortening the wait time but the thought
of the SendKeys disappearing into buffers or elsewhere worries me -
running the code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & " {ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to 1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

Thank you. If you mean along the lines of Steve Yandl's reply, I tried
something similar before asking the question. It worked great on my home PC
but failed to run on the laptops that require audit. These are locked down
tight.

I since discovered the precise moment the code was passed by watching the
Excel status bar when the cmd window (supposedly) had focus. This led to me
touching the cmd window with the mouse before it fired (why is that not a
logical step <g). This made the command pass every time. So, this issue
seems to be that the cmd window is either not achieving focus or is losing
focus - despite outward appearances. I guess the next logical question is
how to check whether cmd has focus and switch focus if it does not have it.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
SendKeys is unreliable at the best of times and even more dodgy with the
command window. Why not pipe the results of ipconfig to a text file with
the Shell (and use vbHide), then open the text file in notepad or even
directly into cells. Post back if not sure how to do that.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks fine
on my laptop but is hit-and-miss on other laptops, sometimes running;
sometimes producing Enter only; sometimes nothing more than the open cmd
window. Is there a way to ensure the code does its job? I was thinking of
While Dir produces nothing and shortening the wait time but the thought
of the SendKeys disappearing into buffers or elsewhere worries me -
running the code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & "
{ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to 1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

The WScript approach would be simplest providing it is available, which it
isn't always in corporate systems. But I wasn't suggesting that, if you
re-read.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Thank you. If you mean along the lines of Steve Yandl's reply, I tried
something similar before asking the question. It worked great on my home
PC but failed to run on the laptops that require audit. These are locked
down tight.

I since discovered the precise moment the code was passed by watching the
Excel status bar when the cmd window (supposedly) had focus. This led to
me touching the cmd window with the mouse before it fired (why is that not
a logical step <g). This made the command pass every time. So, this issue
seems to be that the cmd window is either not achieving focus or is losing
focus - despite outward appearances. I guess the next logical question is
how to check whether cmd has focus and switch focus if it does not have
it.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
SendKeys is unreliable at the best of times and even more dodgy with the
command window. Why not pipe the results of ipconfig to a text file with
the Shell (and use vbHide), then open the text file in notepad or even
directly into cells. Post back if not sure how to do that.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks fine
on my laptop but is hit-and-miss on other laptops, sometimes running;
sometimes producing Enter only; sometimes nothing more than the open cmd
window. Is there a way to ensure the code does its job? I was thinking
of While Dir produces nothing and shortening the wait time but the
thought of the SendKeys disappearing into buffers or elsewhere worries
me - running the code in break mode experience :-) Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & "
{ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to
1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

If that is not what you meant, I am in need of enlightenment on piping to
file (temp.txt) using Shell. From there, I am already reading the result and
appending required data to rows.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
The WScript approach would be simplest providing it is available, which it
isn't always in corporate systems. But I wasn't suggesting that, if you
re-read.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Thank you. If you mean along the lines of Steve Yandl's reply, I tried
something similar before asking the question. It worked great on my home
PC but failed to run on the laptops that require audit. These are locked
down tight.

I since discovered the precise moment the code was passed by watching the
Excel status bar when the cmd window (supposedly) had focus. This led to
me touching the cmd window with the mouse before it fired (why is that
not a logical step <g). This made the command pass every time. So, this
issue seems to be that the cmd window is either not achieving focus or is
losing focus - despite outward appearances. I guess the next logical
question is how to check whether cmd has focus and switch focus if it
does not have it.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
SendKeys is unreliable at the best of times and even more dodgy with the
command window. Why not pipe the results of ipconfig to a text file with
the Shell (and use vbHide), then open the text file in notepad or even
directly into cells. Post back if not sure how to do that.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks
fine on my laptop but is hit-and-miss on other laptops, sometimes
running; sometimes producing Enter only; sometimes nothing more than
the open cmd window. Is there a way to ensure the code does its job? I
was thinking of While Dir produces nothing and shortening the wait
time but the thought of the SendKeys disappearing into buffers or
elsewhere worries me - running the code in break mode experience :-)
Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & "
{ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to
1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve

Try something like this -

Sub GetIPconfig()
Dim bGotIt As Boolean
Dim ret As Long
Dim t As Single
Dim sCmd As String
Dim sFile As String
Const Q As String = """"
Const timeOut As Single = 5

sFile = Application.DefaultFilePath & "\ipConfig.txt"
sCmd = "cmd.exe /k ipconfig.exe /all "
On Error Resume Next
Kill sFile
On Error GoTo 0

ret = Shell(sCmd & Q & sFile & Q, vbHide)

' loop until the file exists or time-out
t = Timer + timeOut
Do
bGotIt = FileExists(sFile)
Loop While Not bGotIt And t Timer

If bGotIt Then
MsgBox "Got ipConfig" ' hmm, seem to need a little break here
FileToCells sFile, Range("A1")
Kill sFile
Else
MsgBox "failed !"
End If

End Sub

Private Function FileExists(ByVal sFile As String) As Boolean
Dim nAttr As Long

On Error Resume Next
nAttr = GetAttr(sFile)
FileExists = (Err.Number = 0) And ((nAttr And VBA.vbDirectory) = 0)
On Error GoTo 0

End Function

Sub FileToCells(sFile As String, rng As Range)
Dim nLen As Long
Dim sTxt As String
Dim FF As Integer
Dim arr

FF = FreeFile
Open sFile For Binary Access Read As #FF
nLen = LOF(FF)
'if nlen = 0 then ?
sTxt = String(nLen, 0) '
Get FF, , sTxt
Close FF

sTxt = Replace(sTxt, vbCr, "")
arr = Application.Transpose(Split(sTxt, vbLf))

rng.Resize(UBound(arr)).Value = arr

End Sub

For some reason seem to need to break the code before getting the textfile,
not sure why, probably missing something simple.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
If that is not what you meant, I am in need of enlightenment on piping to
file (temp.txt) using Shell. From there, I am already reading the result
and appending required data to rows.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
The WScript approach would be simplest providing it is available, which
it isn't always in corporate systems. But I wasn't suggesting that, if
you re-read.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Thank you. If you mean along the lines of Steve Yandl's reply, I tried
something similar before asking the question. It worked great on my home
PC but failed to run on the laptops that require audit. These are locked
down tight.

I since discovered the precise moment the code was passed by watching
the Excel status bar when the cmd window (supposedly) had focus. This
led to me touching the cmd window with the mouse before it fired (why is
that not a logical step <g). This made the command pass every time. So,
this issue seems to be that the cmd window is either not achieving focus
or is losing focus - despite outward appearances. I guess the next
logical question is how to check whether cmd has focus and switch focus
if it does not have it.

--
Steve

"Peter T" <peter_t@discussions wrote in message
...
SendKeys is unreliable at the best of times and even more dodgy with
the command window. Why not pipe the results of ipconfig to a text file
with the Shell (and use vbHide), then open the text file in notepad or
even directly into cells. Post back if not sure how to do that.

Regards,
Peter T


"AltaEgo" <Somewhere@NotHere wrote in message
...
Hello

I use the code below to open and run ipconfig /all to file. It woks
fine on my laptop but is hit-and-miss on other laptops, sometimes
running; sometimes producing Enter only; sometimes nothing more than
the open cmd window. Is there a way to ensure the code does its job? I
was thinking of While Dir produces nothing and shortening the wait
time but the thought of the SendKeys disappearing into buffers or
elsewhere worries me - running the code in break mode experience :-)
Is there another way?


Sub RunCMD()
Dim ReturnValue

sCmd = "ipconfig /all " & ActiveWorkbook.Path & "\" & fName & "
{ENTER}"

ReturnValue = Shell("CMD.EXE", 1)

' possibly, While the file doesn't exist
Application.OnTime Now + TimeSerial(0, 0, 5), "typeKeys" ' reduce 5 to
1?
'Wend
End Sub



Private Sub typeKeys()
SendKeys sCmd
End Sub


--
Steve