我有一个批处理文件,它做了几件事,最后一步是打开一个excel文档,运行其中包含的宏来更新文件的内容。宏只需单击一个按钮即可完美运行,但我希望在运行.bat文件时完成所有操作。
我知道我可以将宏附加到打开事件,这样它就会在你打开宏时运行,但我只希望它在你运行bat文件时自动更新,而不是每次你打开它的时候。
也许我可以传递一个参数,让它知道它是从.bat运行的?或者直接使用excel命令运行它?
like this?
run excel.exe /runMacro "mymacro"
我到处都找不到我需要的东西,谢谢。
发布于 2012-05-16 23:10:47
是的,基本上最简单的方法就是将"mymacro“中的内容移动到ThisWorkBook中。
Private Sub Workbook_Open()
为了安全起见,用户可能仍然需要单击“启用宏”按钮,除非您希望它是无人值守的。如果要将参数传递到打开的工作簿中,则可以通过分析命令行来执行此操作。您可以在Google上搜索"excel GetCommandLineW“作为代码示例。
Private Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Private Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
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
Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim CmdLine As String
CmdRaw = GetCommandLine
CmdLine = CmdToSTr(CmdRaw)
' From here you can parse the CmdLine
' ...snip...
发布于 2014-09-19 05:41:45
在我的Excel 2013版本(15.0.4649.1000 64位)上,我必须编写以下代码:
#If VBA7 Then
Private Declare PtrSafe Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As LongPtr
Private Declare PtrSafe Function lstrlenW Lib "kernel32" (ByVal lpString As LongPtr) As Long
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As LongPtr)
#Else
' 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)
#End If
#If VBA7 Then
Function CmdToSTr(Cmd As LongPtr) As String
#Else
Function CmdToSTr(Cmd As Long) As String
#End If
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
Private Sub Workbook_Open()
Dim CmdRaw As LongPtr
Dim CmdLine As String
Dim TabName As String
CmdRaw = GetCommandLine
CmdLine = CmdToSTr(CmdRaw)
MsgBox(CmdLine)
End Sub
https://stackoverflow.com/questions/10621299
复制相似问题