Run Dos Comand and store result in Excel.

[Ash786]

Hi,

I am trying to create an Excel Macro to Run set of commands from excel and store result in excel. Please need your help, following are the steps I like to do.

a) Set of dos command is stored as a value in Cell D on Worksheet1, for instance "ipconfig"
b) Click run button to capture and run command from Cell D in Dos command
c) Capture the result and store the returned result in Cell F
d) this needs to keep on running until there is no value in Cell D

Thank you in advance.

Regards

Ash

[Kenneth Hobs]

It depends on what you are trying to achieve. It sounds like you may want to execute a series of shell commands. Those commands can require that they be ran in the same shell session.

If it is just a one-off deal for each cell, then sure, that can be done. Keep in mind that cell can only hold so much. e.g.

Sub ken()  
  With Range("A2")
    .Value2 = "ipconfig"
    .Offset(, 1).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll
    .Offset(, 1).EntireColumn.AutoFit
  End With
End Sub

[SamT]

What is the ultimate goal?

[Ash786]

Explanation

It depends on what you are trying to achieve. It sounds like you may want to execute a series of shell commands. Those commands can require that they be ran in the same shell session.

If it is just a one-off deal for each cell, then sure, that can be done. Keep in mind that cell can only hold so much. e.g.

Sub ken()  
  With Range("A2")
    .Value2 = "ipconfig"
    .Offset(, 1).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll
    .Offset(, 1).EntireColumn.AutoFit
  End With
End Sub

Thank you Kenneth, this will resolve half of the problem . how would I be able to returned a result in a specific cell?


SAMT: I am doing some network port testing and for each source I run commands to test several destinations and port numbers. I am trying to automate via excel where I have all the source IP, destination IP and port numbers. I have created a formula in excel to implement a commandline by using details for Source + Destination + Port cells. So I like to press a run button to go through each row, cell D "that contains the command line" and execute. then capture the returned result and store in a specific cell against each row.

Hope I have manage to explain my self,

Many thanks for your help guys. Really appreciated

[Kenneth Hobs]

The offset() does return the result. If you mean return the result of a text file that a bat file creates, that is easy enough.

e.g. Bat file commands:
ipconfig > c:\temp\ip.txt
dir x:\*.xls >> c:\temp\ip.txt

Note how I used >> to append to a text file.

[Ash786]

Private Sub Runbtn_Click()
   With Range("A2")
        .Select
        Do Until IsEmpty(ActiveCell)
            .Offset(, 3).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll
            .Offset(, 3).EntireColumn.AutoFit
            ActiveCell.Offset(1, 0).Select
        Loop
    End With
End Sub

Kenneth, can you please help... I have added a loop to keep on running until the cell value is empty. however the .offset line never change the value and always writing same value in the same cell for each loop. what do I change to ensure the .offset value change according to ActiveCell value.

[SamT]

Whenever possible, avoid use of "ActiveCell." Note that I also took the Column.Autofit out of the loop so it only runs once.

Private Sub Runbtn_Click() 
Dim Cel As Range
    Set Cel = Range("A2") 

        With Cel
        Do Until IsEmpty(Cel) 
            .Offset(, 3).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll 
            Set Cel = .Offset(1, 0)
        Loop 
        .Offset(, 3).EntireColumn.AutoFit 
    End With 
End Sub

[Ash786]

Whenever possible, avoid use of "ActiveCell." Note that I also took the Column.Autofit out of the loop so it only runs once.

Private Sub Runbtn_Click() 
Dim Cel As Range
    Set Cel = Range("A2") 

        With Cel
        Do Until IsEmpty(Cel) 
            .Offset(, 3).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll 
            Set Cel = .Offset(1, 0)
        Loop 
        .Offset(, 3).EntireColumn.AutoFit 
    End With 
End Sub

Sorry Sam your code is in forever loop, also have same issue the one I am facing in my code.

 .Offset(, 3).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll

above line always shows the same value it stored during the first execution. for example, I have "Hostname" command in A2, the line stored the value in D2 cell. then when the code move to A3 cell to execute next command "ipconfig" the value for the above code line doesn't gets updated and stores the same value from A2 command in D2.

hope I am making sense.

[SamT]

Try this one. The main thing I did was get rid of the With bracket

Private Sub Runbtn_Click()
Dim Cel As Range
Set Cel = Range("A2")


Do Until IsEmpty(Cel)
Cel.Offset(, 3).Value = CreateObject("Wscript.Shell").Exec("cmd /c " & Cel.Text).StdOut.ReadAll
Set Cel = Cel.Offset(1, 0)
Loop
Cel.Offset(, 3).EntireColumn.AutoFit
End Sub

[Kenneth Hobs]

Sub ken()  
  Dim r As Range, c As Range
  
  Range("A2").Value2 = "ipconfig"
  Range("A3").Value2 = "Dir " & """" & ThisWorkbook.Path & "\*.xl??" & """" & " /b"
  Range("A4").Value2 = "Help FindStr"
  
  Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
  For Each c In r
    With c
      If IsEmpty(c) Then Exit Sub
      .Offset(, 1).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & .Value2).StdOut.ReadAll
    End With
  Next c
  Columns("B:B").EntireColumn.AutoFit
End Sub

[Ash786]

Thanks Kenneth,

I have also put together the following code, not smart as yours but it works for me.

Private Sub Runbtn_Click()
    Dim Cel As Range
    Set Cel = Range("A2")
    With Cel
        Dim cel2 As Range
        Set cel2 = Range("D2")
            Do Until IsEmpty(Cel)
               If Cel.Value <> "" Then
                    With cel2
                        .Offset(, 0).Value2 = CreateObject("Wscript.Shell").Exec("cmd /c " & Cel.Value2).StdOut.ReadAll
                        .Offset(, 0).EntireColumn.AutoFit
                         Set cel2 = .Offset(1, 0)
                    End With
                End If
                Set Cel = Cel.Offset(1, 0)
            Loop
    End With
End Sub

[snb]

Too much code for a simple task:

sub M_snb()
   with CreateObject("Wscript.Shell")
     for each cl in sheet1.columns(1).specialcells(2).offset(1).specialcells(2)
       cl.offset(,3)= .Exec("cmd /c " & cl).StdOut.ReadAll
     next
   end with
End Sub

or (faster)

sub M_snb()
   sn=sheet1.columns(1).specialcells(2).offset(1).specialcells(2)

   with CreateObject("Wscript.Shell")
     for j=1 to ubound(sn) 
       sn(j,1)= .Exec("cmd /c " & sn(j,1)).StdOut.ReadAll
     next
   end with

   sheet1.columns(1).specialcells(2).offset(1).specialcells(2).offset(,3)=sn
End Sub

[Ash786]

Many thanks everyone, All works fine. Now I have different challenge, for which I will write an other post.