I am trying to create a Metrics report in Excel 7. The data for the report is
in an MKS application History. MKS does not provide a way to collect the
history data other than using a command line interface.
I have manually typed in the command line and run it redirecting the result
to a text file. I have written a macro tha loads the text file into excel and
parses the data for the information I need.
I have to create the report weekly and would like to be able to run the
command line from the macro.
My approach is to create the command line in the macro, save as a text file
with the .bat extension, run the bat file and then load the result and parse
it for the data I need.
The piece I do not know how to do is execute the external file from the macro.

If there is a way to run a command line inside Excel that would work as well.

Thanks for any help.

Hi -

Try the shell() function, which I use sometimes like the following.

Sub run_proc()
on error resume next

dim rc, out_file
out_file = "c:\windows\temp\pgm.out"

rc = Shell("perl -S GetFileList.pl " & out_file, vbNormalFocus)
if err then msgbox error$
if rc <= 0 then msgbox "other err"

' may want to chk w others on using the following
DoEvents( )

End Sub



"RockyMountainHiker" wrote:

I am trying to create a Metrics report in Excel 7. The data for the report is
in an MKS application History. MKS does not provide a way to collect the
history data other than using a command line interface.
I have manually typed in the command line and run it redirecting the result
to a text file. I have written a macro tha loads the text file into excel and
parses the data for the information I need.
I have to create the report weekly and would like to be able to run the
command line from the macro.
My approach is to create the command line in the macro, save as a text file
with the .bat extension, run the bat file and then load the result and parse
it for the data I need.
The piece I do not know how to do is execute the external file from the macro.

If there is a way to run a command line inside Excel that would work as well.

Thanks for any help.

Hi

Thanks, I modified it to run the command line directly after passing it the
CLI string and the Output file.
It works great

Sub run_proc(CLI As String, outFile As String)
On Error Resume Next

Dim rc As Variant

rc = Shell("cmd.exe /C" & CLI & " " & outFile, vbHide)
If Err Then MsgBox Error$
If rc <= 0 Then MsgBox "other err"

End Sub

"MeistersingerVonNurnberg" wrote:

Hi -

Try the shell() function, which I use sometimes like the following.

Sub run_proc()
on error resume next

dim rc, out_file
out_file = "c:\windows\temp\pgm.out"

rc = Shell("perl -S GetFileList.pl " & out_file, vbNormalFocus)
if err then msgbox error$
if rc <= 0 then msgbox "other err"

' may want to chk w others on using the following
DoEvents( )

End Sub



"RockyMountainHiker" wrote:

I am trying to create a Metrics report in Excel 7. The data for the report is
in an MKS application History. MKS does not provide a way to collect the
history data other than using a command line interface.
I have manually typed in the command line and run it redirecting the result
to a text file. I have written a macro tha loads the text file into excel and
parses the data for the information I need.
I have to create the report weekly and would like to be able to run the
command line from the macro.
My approach is to create the command line in the macro, save as a text file
with the .bat extension, run the bat file and then load the result and parse
it for the data I need.
The piece I do not know how to do is execute the external file from the macro.

If there is a way to run a command line inside Excel that would work as well.

Thanks for any help.