Passing argument from cmd.exe to Excel VBA

[Edmund]

How can I pass an argument from cmd.exe to an Excel macro?

I tested with VB6 using the below sample & successfully passed Arg1 & Arg2
over to Macro1. But how to do this if I start from cmd.exe instead of VB6?

'======================
' The code in VB6
'======================
Sub Command3_Click()
Dim XL As Object
Dim Ans As String

Set XL = CreateObject("Excel.Application")

With XL
.Workbooks.Open "C:\TheFileContainingMacro1.xls"
Ans = .Run("Macro1", "Value_Of_Arg1", "Value_Of_Arg2")
.Quit
End With
Set XL = Nothing
End
End Sub

'======================
' The code in "C:\TheFileContainingMacro1.xls!Macro1
'======================
Function Macro1(FromExternal_1, FromExternal_Arg2)

With ActiveSheet
.Range("A1") = FromExternal_1
.Range("A2") = FromExternal_2
End With
ActiveWorkbook.Save

End Function


But how to do this if I start from cmd.exe instead of VB6?

Thanks a million

 

[HatesIT]

look up the 'command' function in help. You would then check the args within
your workbook on open sub

hth

 

[Steve Yandl]

Edmund,

I've always used a vbScript file as a sort of helper file to do this sort of
thing.

For my test, I created a workbook named "C:\Test\TestBk1.xls". In module 1
I created a subroutine named "TestMacro" that takes two text string
arguments.

Now I create a text file that I name "C:\Test\LaunchSub.vbs". The contents
of this script is shown between the lines below,
________________________________________
arg1 = WScript.Arguments(0)
arg2 = WScript.Arguments(1)


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWkbk = objXL.Workbooks.Open("C:\Test\TestBk1.xls")
objXL.run "TestMacro", CStr(arg1), CStr(arg2)
__________________________________________

Now, the command line that I can use at the prompt for cmd.exe or at the
'Start > Run' line for Windows would be:
WScript.exe "C:\Test\LaunchSub.vbs" "dog" "cat"
Note, that I am using WScript.exe with the name of my vbs file as the first
argument. After that is a space followed by my first argument to go to the
vbs script and then a second space followed by my final argument. Those
arguments will be retrieved in the WScript.Arguments collection. In the
script, all the arguments will be treated as variant type so you will want
to convert them to the appropriate data type before sending them to your
Excel macro.


Steve Yandl

 

[Edmund]

Dear Steve,

Thank You is just a simple word, but I mean it very sincerely. "Thank You"
so very much for the extra miles in detailing the explanations step-by-step.
Perfectly clear!