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 LongDim CmdLine As StringDim Msg     As String'get command line parameterCmdRaw = GetCommandLineCmdLine = CmdToSTr(CmdRaw)'find for 3 parametersOn 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 IntegerArgCount = 0Dim Args(2)Do While Err = 0Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)ArgCount = ArgCount + 1Args(ArgCount) = Mid(CmdLine, Pos1, _IIf(Err, Len(CmdLine), Pos2) - Pos1)Pos1 = Pos2 + 1Loop

被调用的子函数,用来将命令行参数转换成字符串类型:

'transfer command parms to string
Public Function CmdToSTr(Cmd As Long) As StringDim Buffer() As ByteDim StrLen   As LongIf Cmd ThenStrLen = lstrlenW(Cmd) * 2If StrLen ThenReDim Buffer(0 To (StrLen - 1)) As ByteCopyMemory Buffer(0), ByVal Cmd, StrLenCmdToSTr = BufferEnd IfEnd If
End Function

通过以上步骤就可以直接使用Args(1), Args(2)这个数组中的元素保存的参数赋值给变量了.