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.接收方式:

首先声明:

<span style="font-family: Calibri;">
Option Base 0
Option Explicit</span>

'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)这个数组中的元素保存的参数赋值给变量了.