Windows下Batch脚本传递参数给Excel VBA

1.调用方式: "C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE" "MacroName.xlsm" /e/parm1/parm2/

因为各种原因,大概是安全最重要, Office 2007中用xlsm作为含有macro的电子表格文件后缀名, /e表示后面开始传递参数,并用/隔开, parm1和parm2就是可以传递的参数了,当然可以传递更多个.

2.接收方式:

首先声明:

Option Base 0 Option Explicit 'get parameters from command line Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
然后保存参数到数组中:

Dim CmdRaw As Long Dim CmdLine As String Dim Msg As String 'get command line parameter CmdRaw = GetCommandLine CmdLine = CmdToSTr(CmdRaw) 'find for 3 parameters On Error Resume Next 'for the wksht-function "Search" Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1 'search "/e" Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st param start 'fetch into array var. Dim ArgCount As Integer ArgCount = 0 Dim Args(2) Do While Err = 0 Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1) ArgCount = ArgCount + 1 Args(ArgCount) = Mid(CmdLine, Pos1, _ IIf(Err, Len(CmdLine), Pos2) - Pos1) Pos1 = Pos2 + 1 Loop
被调用的子函数,用来将命令行参数转换成字符串类型:

'transfer command parms to string Public Function CmdToSTr(Cmd As Long) As String Dim Buffer() As Byte Dim StrLen As Long If Cmd Then StrLen = lstrlenW(Cmd) * 2 If StrLen Then ReDim Buffer(0 To (StrLen - 1)) As Byte CopyMemory Buffer(0), ByVal Cmd, StrLen CmdToSTr = Buffer End If End If End Function
通过以上步骤就可以直接使用Args(1), Args(2)这个数组中的元素保存的参数赋值给变量了.