Pass switch to Main procedure

[Tod]

With VB I can add a switch to the command-line and then use Command
within the VB code to read the switch and run code conditionally based
on the Command value.

Typically with Excel workbooks I use a vbscript to open the workbook
and run the code that is in the Sub Main of Module1. Is there a way I
can pass an argument to the sub from the vbscript?

tod

 

[Bob Phillips]

Tod,

From what I read there seems to be some dispute about whether this actually
works in VBA, VB yes, VBA not absolute. I tried this code and started Excel
with a /r switch, and I could see that in the result.

Private Declare Function GetCommandLine Lib "kernel32" _
Alias "GetCommandLineA" () As Long

Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenA" _
(ByVal lpString As Long) As Long

Private Declare Function lstrcpy Lib "kernel32" _
Alias "lstrcpyA" _
(ByVal lpString1 As String, _
ByVal lpString2 As Long) As Long

Private Sub Form_Load()
Dim lpszCommand As Long
Dim cChars As Long
Dim sCommand As String

lpszCommand = GetCommandLine()
cChars = lstrlen(lpszCommand)
sCommand = Space(cChars + 1)
lstrcpy sCommand, lpszCommand

MsgBox sCommand

End Sub

If that is not what you are meaning, maybe as you instantiate Excel in the
VBScript rather than launch the executable, you could set an environmental
variable and read that in VBA, using the Environ command.

--

HTH

RP
(remove nothere from the email address if mailing direct)