call the cmd prompt with VBA

[t1hodges]

is it possible to call the cmd prompt with VBA, run a command like IPCONFIG /ALL, copy the results from the window and paste the results into a spreadsheet.

 

[CautionMP]

t1hodges,
It's rough but it works. Based on file in the format:
[tt]

Windows IP Configuration

Host Name . . . . . . . . . . . . : data here
Primary Dns Suffix . . [/tt]

Sub testIPConfig()
Dim objShell As Object
Dim intFile As Integer, intRow As Integer, intColumn As Integer
Dim strCurrentLine As String
'Shell "cmd.exe" "ipconfig/all > ipcfg.txt"
Set objShell = CreateObject("Wscript.Shell")
objShell.Run ("%comspec% /c ipconfig /all > C:\ipcfg.txt")
Set objShell = Nothing
'Wait 3 seconds for file to appear
Application.Wait Now + 0.00003

'Get file and worksheet information
intFile = FreeFile
intRow = ActiveCell.Row
intColumn = ActiveCell.Column
Open "C:\ipcfg.txt" For Input As #intFile

'Read the file
Do
  Line Input #intFile, strCurrentLine
  Select Case Len(strCurrentLine)
    Case 0
      'Line is blank
      intRow = intRow - 1
    Case Is < 43
      Cells(intRow, intColumn) = strCurrentLine
    Case Else
      Cells(intRow, intColumn + 1) = Mid(strCurrentLine, 8, 26)
      Cells(intRow, intColumn + 2) = Mid(strCurrentLine, 45)
  End Select
  intRow = intRow + 1
Loop Until EOF(intFile)
Close #intFile
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[t1hodges]

during runtime i have two issues
1. application.wait
what library reference needs to be set for this property to be available?

 

[CautionMP]

t1hodges,
Microsoft Excel 9.0 Object Library. I developed the above code in Excel 2000.

If your working in Access you can use a timer event.

And the second issue...

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[t1hodges]

I figured it out that I only needed a part of your code and came up with this.

shell ("C:\WINDOWS\SYSTEM32\cmd.exe /k ipconfig/all > test.xls")

This works fine for me. Thanks a bunch!!!