Way to run Excel macros from command line or batch file

Question

I have an Excel VBA macro which I need to run when accessing the file from a batch file  but not every time I open it  hence not using the open file event   Is there a way to run the macro from the command line or batch file  I m not familiar with such a command   Assume a Windows NT environment

User · Answer

Instead of directly comparing the strings  VB won t find them equal since GetEnvironmentVariable returns a string of length 255  write this   Private Sub Workbook Open            If InStr 1  GetEnvironmentVariable  InBatch     TRUE   vbTextCompare  Then         Debug Print  Batch            Call Macro     Else             Debug Print  Normal           End If   End Sub

User · Answer

Robert  I have tried to adapt your code with a relative path  and created a batch file to run the VBS   The VBS starts and closes but doesn t launch the macro    Any idea of where the issue could be   Option Explicit  On Error Resume Next  ExcelMacroExample  Sub ExcelMacroExample       Dim xlApp    Dim xlBook     Set xlApp   CreateObject  Excel Application     Set objFSO   CreateObject  Scripting FileSystemObject     strFilePath   objFSO GetAbsolutePathName         Set xlBook   xlApp Workbooks Open strFilePath   Excels CLIENTES xlsb     0  True     xlApp Run  open form      Set xlBook   Nothing    Set xlApp   Nothing   End Sub   I removed the  Application Quit  because my macro is calling a userform taking care of it   Cheers  EDIT  I have actually worked it out  just in case someone wants to run a userform  alike  a stand alone application   Issues I was facing   1 - I did not want to use the Workbook Open Event as the excel is locked in read only   2 - The batch command is limited that the fact that  to my knowledge  it cannot call the macro   I first wrote a macro to launch my userform while hiding the application   Sub open form    Application Visible   False  frmAddClient Show vbModeless End Sub   I then created a vbs to launch this macro  doing it with a relative path has been tricky    dim fso dim curDir dim WinScriptHost set fso   CreateObject  Scripting FileSystemObject   curDir   fso GetAbsolutePathName      set fso   nothing  Set xlObj   CreateObject  Excel application   xlObj Workbooks Open curDir  amp    Excels CLIENTES xlsb  xlObj Run  open form    And I finally did a batch file to execute the VBS      echo off pushd   dp0 cscript Add Client vbs   Note that I have also included the  Set back to visible  in my Userform QueryClose   Private Sub cmdClose Click   Unload Me End Sub  Private Sub UserForm QueryClose Cancel As Integer  CloseMode As Integer      ThisWorkbook Close SaveChanges  True     Application Visible   True     Application Quit End Sub   Anyway  thanks for your help  and I hope this will help if someone needs it

User · Answer

I generally store my macros in xlam add-ins separately from my workbooks so I wanted to open a workbook and then run a macro stored separately   Since this required a VBS Script  I wanted to make it  portable  so I could use it by passing arguments  Here is the final script  which takes 3 arguments     Full Path to Workbook Macro Name  OPTIONAL  Path to separate workbook with Macro   I tested it like so    C  Temp runmacro vbs   C  Temp Book1 xlam   Hello    C  Temp runmacro vbs   C  Temp Book1 xlsx   Hello    AppData  Microsoft Excel XLSTART Book1 xlam    runmacro vbs   Set args   Wscript Arguments  ws   WScript Arguments Item 0  macro   WScript Arguments Item 1  If wscript arguments count  gt  2 Then  macrowb  WScript Arguments Item 2  End If  LaunchMacro  Sub LaunchMacro      Dim xl   Dim xlBook      Set xl   CreateObject  Excel application     Set xlBook   xl Workbooks Open ws  0  True    If wscript arguments count  gt  2 Then    Set macrowb  xl Workbooks Open macrowb  0  True    End If    xl Application Visible   True   Show Excel Window   xl Application run macro    xl DisplayAlerts   False    suppress prompts and alert messages while a macro is running    xlBook saved   True   suppresses the Save Changes prompt when you close a workbook    xl activewindow close   xl Quit  End Sub

User · Answer

You can check if Excel is already open   There is no need to create another isntance     If CheckAppOpen  excel application    Then             MsgBox  App Loaded              Set xlApp   GetObject    excel Application         Else               MsgBox  App Not Loaded              Set  wrdApp   CreateObject   excel Application         End If

User · Answer

The simplest way to do it is to   1  Start Excel from your batch file to open the workbook containing your macro   EXCEL EXE  e  c  YourWorkbook xls    2  Call your macro from the workbook s Workbook Open event  such as   Private Sub Workbook Open       Call MyMacro1            Call your macro     ActiveWorkbook Save      Save the current workbook  bypassing the prompt     Application Quit         Quit Excel End Sub   This will now return the control to your batch file to do other processing

User · Answer

I have always tested the number of open workbooks in Workbook Open     If it is 1  then the workbook was opened by the command line  or the user closed all the workbooks  then opened this one      If Workbooks Count   1 Then        execute the macro or call another procedure - I always do the latter       PublishReport      ThisWorkbook Save      Application Quit  End If

User · Answer

The method shown below allows to run defined Excel macro from batch file  it uses environment variable to pass macro name from batch to Excel   Put this code to the batch file  use your paths to EXCEL EXE and to the workbook    Set MacroName MyMacro  C  Program Files Microsoft Office Office15 EXCEL EXE   C  MyWorkbook xlsm    Put this code to Excel VBA ThisWorkBook Object   Private Sub Workbook Open       Dim strMacroName As String     strMacroName   CreateObject  WScript Shell   Environment  process   Item  MacroName       If strMacroName  lt  gt     Then Run strMacroName End Sub   And put your code to Excel VBA Module  like as follows   Sub MyMacro       MsgBox  MyMacro is running     End Sub   Launch the batch file and get the result     For the case when you don t intend to run any macro just put empty value Set MacroName  to the batch

User · Answer

If you re more comfortable working inside Excel VBA  use the open event and test the environment  either have a signal file  a registry entry or an environment variable that controls what the open event does   You can create the file setting outside and test inside  use GetEnviromentVariable for env-vars  and test easily  I ve written VBScript but the similarities to VBA cause me more angst than ease     more   As I understand the problem  you want to use a spreadsheet normally most some of the time yet have it run in batch and do something extra different  You can open the sheet from the excel exe command line but you can t control what it does unless it knows where it is  Using an environment variable is relatively simple and makes testing the spreadsheet easy   To clarify  use the function below to examine the environment  In a module declare   Private Declare Function GetEnvVar Lib  kernel32  Alias  GetEnvironmentVariableA         ByVal lpName As String  ByVal lpBuffer As String  ByVal nSize As Long  As Long  Function GetEnvironmentVariable var As String  As String Dim numChars As Long      GetEnvironmentVariable   String 255            numChars   GetEnvVar var  GetEnvironmentVariable  255   End Function   In the Workbook open event  as others    Private Sub Workbook Open       If GetEnvironmentVariable  InBatch      TRUE  Then         Debug Print  Batch      Else         Debug Print  Normal      End If End Sub   Add in active code as applicable  In the batch file  use  set InBatch TRUE

User · Answer

you could write a vbscript to create an instance of excel via the createobject   method  then open the workbook and run the macro   You could either call the vbscript directly  or call the vbscript from a batch file   Here is a resource I just stumbled accross  http   www codeguru com forum showthread php t 376401

User · Answer

You can launch Excel  open the workbook and run the macro from a VBScript file   Copy the code below into Notepad   Update the  MyWorkbook xls  and  MyMacro  parameters   Save it with a vbs extension and run it   Option Explicit  On Error Resume Next  ExcelMacroExample  Sub ExcelMacroExample       Dim xlApp    Dim xlBook     Set xlApp   CreateObject  Excel Application      Set xlBook   xlApp Workbooks Open  C  MyWorkbook xls   0  True     xlApp Run  MyMacro    xlApp Quit     Set xlBook   Nothing    Set xlApp   Nothing   End Sub    The key line that runs the macro is   xlApp Run  MyMacro

User · Answer

I m partial to C   I ran the following using linqpad  But it could just as easily be compiled with csc and ran through the called from the command line   Don t forget to add excel packages to namespace   void Main         var oExcelApp    Microsoft Office Interop Excel Application System Runtime InteropServices Marshal GetActiveObject  Excel Application        try          var WB   oExcelApp ActiveWorkbook          var WS    Worksheet WB ActiveSheet            string   Range WS Cells 1 1   Value  Dump  Cell Value      cel A1 val         oExcelApp Run  test macro name   Dump  macro              finally          if oExcelApp    null              System Runtime InteropServices Marshal ReleaseComObject oExcelApp           oExcelApp   null

[excel] Way to run Excel macros from command line or batch file?

Examples related to excel

Examples related to vba

Examples related to batch-file

Examples related to command-line