Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
Hi is there a way to pipe a command to the cmd (or run an exe) in
silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
there are a few things you can do
1) using a shell command execute cmd.exe and pass the parameters (the filename to execute) shell("c:\windows\system32\cmd.exe c:\temp\mycommand.exe") 2) Put the exe file in a bat file and run the bat file using a sheet command 3) You ucan execute any DLL from visual basic by defining the library. There are standard DLLs which can spawn processes. "Francogrex" wrote: Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
Here is one option using the WScript.Shell object (borrowed from scripting).
In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
This is AWESOME, simple and to the point. Something I've wanted for a long
time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
Kevin,
The Exec method is more convenient because you have immediate access to the output stream (as well as the ability to supply an input stream or check error output) but the Run method gives more control over the console window. The disadvantage of the Run method is that you have to create a temporary text file as a vehicle to capture the output text stream. Below is a modification that should run the same ping command but keep the console window completely hidden. '------------------------------------ Sub FetchPingOutput() Const window_hidden = 0 Const For_Reading = 1 Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set fso = CreateObject("Scripting.FileSystemObject") myTempName = fso.GetTempName() wsh.Run "%comspec% /c ping 66.102.7.104 " _ & myTempName, window_hidden, True Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) Do While Not objTextFile.AtEndOfStream strLine = objTextFile.ReadLine() Cells(R, 1).Value = strLine R = R + 1 Loop objTextFile.Close fso.DeleteFile (myTempName) Set objTextFile = Nothing Set wsh = Nothing Set fso = Nothing End Sub '----------------------------------- Steve Yandl "Kevin" wrote in message ... This is AWESOME, simple and to the point. Something I've wanted for a long time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
Awesome!!! Thanks much!!
"Steve Yandl" wrote: Kevin, The Exec method is more convenient because you have immediate access to the output stream (as well as the ability to supply an input stream or check error output) but the Run method gives more control over the console window. The disadvantage of the Run method is that you have to create a temporary text file as a vehicle to capture the output text stream. Below is a modification that should run the same ping command but keep the console window completely hidden. '------------------------------------ Sub FetchPingOutput() Const window_hidden = 0 Const For_Reading = 1 Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set fso = CreateObject("Scripting.FileSystemObject") myTempName = fso.GetTempName() wsh.Run "%comspec% /c ping 66.102.7.104 " _ & myTempName, window_hidden, True Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) Do While Not objTextFile.AtEndOfStream strLine = objTextFile.ReadLine() Cells(R, 1).Value = strLine R = R + 1 Loop objTextFile.Close fso.DeleteFile (myTempName) Set objTextFile = Nothing Set wsh = Nothing Set fso = Nothing End Sub '----------------------------------- Steve Yandl "Kevin" wrote in message ... This is AWESOME, simple and to the point. Something I've wanted for a long time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
well, I go to show off to a coworker and he is running the 64 bit OS.
I get a cant find path at Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) is there a different path to specify? I'm not really sure what the "%comspec% means either.... "Kevin" wrote: Awesome!!! Thanks much!! "Steve Yandl" wrote: Kevin, The Exec method is more convenient because you have immediate access to the output stream (as well as the ability to supply an input stream or check error output) but the Run method gives more control over the console window. The disadvantage of the Run method is that you have to create a temporary text file as a vehicle to capture the output text stream. Below is a modification that should run the same ping command but keep the console window completely hidden. '------------------------------------ Sub FetchPingOutput() Const window_hidden = 0 Const For_Reading = 1 Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set fso = CreateObject("Scripting.FileSystemObject") myTempName = fso.GetTempName() wsh.Run "%comspec% /c ping 66.102.7.104 " _ & myTempName, window_hidden, True Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) Do While Not objTextFile.AtEndOfStream strLine = objTextFile.ReadLine() Cells(R, 1).Value = strLine R = R + 1 Loop objTextFile.Close fso.DeleteFile (myTempName) Set objTextFile = Nothing Set wsh = Nothing Set fso = Nothing End Sub '----------------------------------- Steve Yandl "Kevin" wrote in message ... This is AWESOME, simple and to the point. Something I've wanted for a long time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
Kevin,
I tested the routine on a system with 64 bit Vista so it's some other issue. On your coworker's system, if he runs ping from a command prompt and opts to direct output to a new text file, where does the text file appear? Basically, when the file system object does 'GetTempName' it generates a random name that includes the extension 'tmp'. There is no folder given. You could create a folder, for example, "C:\Test" and then in the line where we run ping, you could change the destination to "C:\Test\" & myTempName which would send the output to the text file in C:\Test but then you need to append that path to lines further down in the script as well. The %comspec% is an variable that represents the command interpreter for the system. If you have Win98 systems, that would be command.com but newer systems will be using cmd.exe. At this point in time, it's generally pretty safe to use cmd.exe instead of the %comspec%. Steve Yandl "Kevin" wrote in message ... well, I go to show off to a coworker and he is running the 64 bit OS. I get a cant find path at Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) is there a different path to specify? I'm not really sure what the "%comspec% means either.... "Kevin" wrote: Awesome!!! Thanks much!! "Steve Yandl" wrote: Kevin, The Exec method is more convenient because you have immediate access to the output stream (as well as the ability to supply an input stream or check error output) but the Run method gives more control over the console window. The disadvantage of the Run method is that you have to create a temporary text file as a vehicle to capture the output text stream. Below is a modification that should run the same ping command but keep the console window completely hidden. '------------------------------------ Sub FetchPingOutput() Const window_hidden = 0 Const For_Reading = 1 Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set fso = CreateObject("Scripting.FileSystemObject") myTempName = fso.GetTempName() wsh.Run "%comspec% /c ping 66.102.7.104 " _ & myTempName, window_hidden, True Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) Do While Not objTextFile.AtEndOfStream strLine = objTextFile.ReadLine() Cells(R, 1).Value = strLine R = R + 1 Loop objTextFile.Close fso.DeleteFile (myTempName) Set objTextFile = Nothing Set wsh = Nothing Set fso = Nothing End Sub '----------------------------------- Steve Yandl "Kevin" wrote in message ... This is AWESOME, simple and to the point. Something I've wanted for a long time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pipe to cmd from excel and back
ok, what if I want to run a command on a switch that requires a login.
Is there a way to pass a username, then wait a sec, thean pass the password, then a comand? Kind of like expect but with VBA... "Steve Yandl" wrote: Kevin, I tested the routine on a system with 64 bit Vista so it's some other issue. On your coworker's system, if he runs ping from a command prompt and opts to direct output to a new text file, where does the text file appear? Basically, when the file system object does 'GetTempName' it generates a random name that includes the extension 'tmp'. There is no folder given. You could create a folder, for example, "C:\Test" and then in the line where we run ping, you could change the destination to "C:\Test\" & myTempName which would send the output to the text file in C:\Test but then you need to append that path to lines further down in the script as well. The %comspec% is an variable that represents the command interpreter for the system. If you have Win98 systems, that would be command.com but newer systems will be using cmd.exe. At this point in time, it's generally pretty safe to use cmd.exe instead of the %comspec%. Steve Yandl "Kevin" wrote in message ... well, I go to show off to a coworker and he is running the 64 bit OS. I get a cant find path at Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) is there a different path to specify? I'm not really sure what the "%comspec% means either.... "Kevin" wrote: Awesome!!! Thanks much!! "Steve Yandl" wrote: Kevin, The Exec method is more convenient because you have immediate access to the output stream (as well as the ability to supply an input stream or check error output) but the Run method gives more control over the console window. The disadvantage of the Run method is that you have to create a temporary text file as a vehicle to capture the output text stream. Below is a modification that should run the same ping command but keep the console window completely hidden. '------------------------------------ Sub FetchPingOutput() Const window_hidden = 0 Const For_Reading = 1 Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set fso = CreateObject("Scripting.FileSystemObject") myTempName = fso.GetTempName() wsh.Run "%comspec% /c ping 66.102.7.104 " _ & myTempName, window_hidden, True Set objTextFile = fso.OpenTextFile(myTempName, For_Reading) Do While Not objTextFile.AtEndOfStream strLine = objTextFile.ReadLine() Cells(R, 1).Value = strLine R = R + 1 Loop objTextFile.Close fso.DeleteFile (myTempName) Set objTextFile = Nothing Set wsh = Nothing Set fso = Nothing End Sub '----------------------------------- Steve Yandl "Kevin" wrote in message ... This is AWESOME, simple and to the point. Something I've wanted for a long time. One question though... Is there a way to make the command window open up in the background so that as the spreadsheet is sending and recieving commands that I can be reading my email or in some other application? I'm using this method to run queries against a storage array and it takes about 20 minutes or so to go through all of the commands needed and with the window going back and forth from excel to cmd I can't do anything else until it finishes... Thanks in advance!! Thhis is a great help!! Kevin Green "Steve Yandl" wrote: Here is one option using the WScript.Shell object (borrowed from scripting). In the example below, ping is run on Google and the output is written to Column A on the active sheet starting in A1. The cmd.exe window does appear briefly. '____________________________________________ Sub FetchPingOutput() Dim strLine As String Dim R As Integer R = 1 Set wsh = CreateObject("WScript.Shell") Set wshExecPng = wsh.Exec("ping 66.102.7.104") Set wshStdOut = wshExecPng.StdOut Do Until wshStdOut.AtEndOfStream strLine = wshStdOut.ReadLine Cells(R, 1).Value = strLine R = R + 1 Loop Set wsh = Nothing End Sub '____________________________________________ Steve Yandl "Francogrex" wrote in message ... Hi is there a way to pipe a command to the cmd (or run an exe) in silent mode from excel and have the output of the exe returned into excel spreadsheet? For those who are familiar with SAS, there is a macro called %xlst, I was hoping to find something similar in excel. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer some letter from one cell(pipe/mech or mech/pipe (reqd: pi) | Excel Worksheet Functions | |||
Pipe Character - Any problems using in excel or uses in excel? | Excel Discussion (Misc queries) | |||
Save excel file with pipe separated | Excel Discussion (Misc queries) | |||
Where to find Excel sheet with Pipe & Forging charts and sizes. | Excel Worksheet Functions | |||
How can I save Excel am spreadsheet as pipe delimiter? | Excel Discussion (Misc queries) |