PDA

View Full Version : Run Dos Comand and store result in Excel.



Ash786
10-16-2015, 06:33 AM
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.
:banghead:
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. :friends:

Regards

Ash

Kenneth Hobs
10-16-2015, 07:02 AM
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
10-16-2015, 07:50 AM
What is the ultimate goal?

Ash786
10-16-2015, 09:25 AM
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
10-16-2015, 10:41 AM
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
10-16-2015, 02:40 PM
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
10-16-2015, 03:35 PM
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
10-16-2015, 04:14 PM
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
10-16-2015, 05:12 PM
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
10-16-2015, 05:30 PM
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
10-16-2015, 05:34 PM
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
10-17-2015, 04:15 AM
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
10-19-2015, 05:46 AM
Many thanks everyone, All works fine. Now I have different challenge, for which I will write an other post. :)