How to open and run an excel macro using a batch file

Question

I have a batch file that does several things and the last step is to open an excel document and run the macro contained in it which updates the contents of the file. The macro runs perfectly with the click of a button but I want it all to be done when you run the .bat file.

I know I could attach the macro to the open event so it runs when you open the macro but I only want it to update automatically when you run the bat file, not every time you open the thing.

Maybe I could pass a parameter to let it know that it's been run from a .bat? or run it directly with an excel command?

like this?
run excel.exe /runMacro "mymacro"   

I can't find what I need anywhere, thanks.

Solution 1

Yes, basically the easy way is to move the contents of your "mymacro" into your ThisWorkBook

Private Sub Workbook_Open()

With security, the user may still have to click the enable macros button unless you want this to be unattended. If you want to pass arguments into the workbook open, then you can do this by parsing the command line. You can search code examples on Google for "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...

Solution 2

On my version of Excel 2013 (15.0.4649.1000 64 bits), I was obliged to write the following code :

#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