Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing argument from cmd.exe to Excel VBA
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 -- Edmund (Using Excel XP) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing argument from cmd.exe to Excel VBA
look up the 'command' function in help. You would then check the args within
your workbook on open sub hth "Edmund" wrote: 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 -- Edmund (Using Excel XP) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing argument from cmd.exe to Excel VBA
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" wrote in message ... 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 -- Edmund (Using Excel XP) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing argument from cmd.exe to Excel VBA
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! -- Edmund (Using Excel XP) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing an argument | Excel Programming | |||
Passing a UDF as an argument to a UDF | Excel Discussion (Misc queries) | |||
Passing argument to Excel | Excel Programming | |||
Passing argument to excel | Excel Programming | |||
Passing argument to another Sub | Excel Programming |