# Read Command Line Parameters from VBA

Recently, I needed to read a command line parameter within a VBA code (it's for an Iconics SCADA application, but I developed it with Excel VBA which is essentially the same). It doesn't sound like a big deal but there were a few not-so-obvious tricks that I thought were worth sharing.

I started with the simplest solution using a "GetCommandLineA" Windows API function from kernel32.dll. It was supposed to return a pointer to the command line string, so I assumed that its return type would be string (sounds logical to me).

' The return type of the GetCommandLineA is long
' But it is supposed to be a pointer to the string.
' Assumming that all VBA strings are passed by pointers
' Just declare it's return type to be a string
Declare Function GetCommandLineA Lib "Kernel32" () As String

Dim strCmdLine As String ' Command line string
' Read command line parameters into the string
strCmdLine = GetCommandLineA

End Sub

Surprise - it caused exception and crashed the application. Hm, apparently VBA handles a pointer to the string differently than GetCommandLineA. Not wanting to spend time figuring out why, I decided to copy the string to VBA string using another kernel32.dll function "lstrcpynA".

' Declare the return type to be a pointer (long)
Declare Function GetCommandLineA Lib "Kernel32" () As Long
Declare Function lstrcpynA Lib "kernel32" ( _
ByVal pDestination As String, ByVal pSource As Long, _
ByVal iMaxLength As Integer) As Long

Dim pCmdLine as long     ' Pointer to the string
Dim strCmdLine As String ' Command line string

pCmdLine = GetCommandLineA
' Copy from the pointer to VBA-Style string
' 300 characters for command line seems to be enough
lstrcpynA strCmdLine , pCmdLine, 300

End Sub

This worked better by no longer crashing the application, but the strCmdLine was always empty. After short Google search I found this Microsoft article. Apparently lstrcpynA function (as other DLL functions returning strings) can't change the size of the VBA-style string. In order to reserve the space for that return data, we need to fill the string with a bunch of zeros (vbNullChar).

Declare Function GetCommandLineA Lib "Kernel32" () As Long
Declare Function lstrcpynA Lib "kernel32" ( _
ByVal pDestination As String, ByVal pSource As Long, _
ByVal iMaxLength As Integer) As Long

Dim pCmdLine as long     ' Pointer to the string
Dim strCmdLine As String ' Command line string

' Get the pointer to the command line string
pCmdLine = GetCommandLineA

' Fill the string with zeros
' 300 characters for command line seems to be enough
strCmdLine = String[DollarSign](300, vbNullChar)

' Copy from the pointer to VBA-style string
lstrcpynA strCmdLine , pCmdLine, Len(strCmdLine )

' At this point we got the string
' But rest of it filled with 0 characters.
strCmdLine = Left(strCmdLine , InStr(1, strCmdLine , _
vbNullChar) - 1)

End Sub

This code finally worked as expected, returning command line arguments.

# ImHere
¡Great! Thank you very much
# benny
THANKS! trust me, this saves more than 15 minutes of frustration! and it's incredibly clearly laid-out. thank you!

I found one variation on your code which appears to help me find the true length of the string:
http://www.motobit.com/tips/detpg_vba-getcommandline/

finally, I must agree with "amr:" your approach seems to have the limitation that it only includes up to the first filename argument.
let me know if you get different results, but when I tested (nearly identical to) your approach here were my results:

command: excel c:\foo.xls
result: excel c:\foo.xls

command: excel "c:\foo.xls"
result: excel "c:\fool.xls
note: it's incredibly strange this includes the first quote, and does not include the last quote.

command: excel c:\foo.xls c:\bar.xls
result: excel c:\foo.xls
note: I think this is like the case "amr" mentions. to be clear, I know both arguments are "working" because that command does indeed launch one excel instance which contains one open window each for those two different workbooks.

at any rate it's not urgent, but anyone please post a follow-up if you know what the heck is going on here, especially if you know how to get at "later" command line arguments than just the first ones.

# louie
No actually the code you have written is not working.
# amr
Dear Sir,
Thank you so much for your article this was really useful
but for me I need the text after excel file name (the parameters)

so what I write in cmd is as follows:
c:\> start excel.exe c:\com\book1.xlsm /e/par1/par2

so what I need to get is par1 and par2

any hint will be appreciated,

Regards,

Thanks! This was exactly was I was looking for.

Peter