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.

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.

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.

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.

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.

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.

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.