Run Excel Macro from Outside Excel Using VBScript From Command Line

Question

I m trying to run an Excel macro from outside of the Excel file  I m currently using a   vbs  file run from the command line  but it keeps telling me the macro can t be found  Here is the script I m trying to use  Set objExcel   CreateObject  Excel Application   Set objWorkbook   objExcel Workbooks Open  test xls    objExcel Application Visible   True objExcel Workbooks Add objExcel Cells 1  1  Value    Test value   objExcel Application Run  Macro TestMacro    objExcel ActiveWorkbook Close   objExcel Application Quit WScript Echo  Finished   WScript Quit   And here is the Macro I m trying to access   Sub TestMacro    first set a string which contains the path to the file you want to create   this example creates one and stores it in the root directory MyFile    C  Users username Desktop    amp   TestResult txt   set and open file for output fnum   FreeFile   Open MyFile For Output As fnum  write project info and then a blank line  Note the comma is required Write  fnum   I wrote this  Write  fnum   use Print when you want the string without quotation marks Print  fnum   I printed this  Close  fnum End Sub   I tried the solutions located at Is it possible to run a macro in Excel from external command  to get this far  and modified of course  but it didn t seem to work  I keep getting the error  Microsoft Office Excel  The macro  Macro TestMacro  cannot be found   EDIT  Excel 2003

User · Answer

If you are trying to run the macro from your personal workbook it might not work as opening an Excel file with a VBScript doesnt automatically open your PERSONAL.XLSB. you will need to do something like this:

Dim oFSO
Dim oShell, oExcel, oFile, oSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")
Set oExcel = CreateObject("Excel.Application")
Set wb2 = oExcel.Workbooks.Open("C:\..\PERSONAL.XLSB") 'Specify foldername here

oExcel.DisplayAlerts = False


For Each oFile In oFSO.GetFolder("C:\Location\").Files
    If LCase(oFSO.GetExtensionName(oFile)) = "xlsx" Then
        With oExcel.Workbooks.Open(oFile, 0, True, , , , True, , , , False, , False)



            oExcel.Run wb2.Name & "!modForm"


            For Each oSheet In .Worksheets



                oSheet.SaveAs "C:\test\" & oFile.Name & "." & oSheet.Name & ".txt", 6


            Next
            .Close False, , False
        End With
    End If



Next
oExcel.Quit
oShell.Popup "Conversion complete", 10

So at the beginning of the loop it is opening personals.xlsb and running the macro from there for all the other workbooks. Just thought I should post in here just in case someone runs across this like I did but cant figure out why the macro is still not running.

User · Answer

I think you are trying to do this   TRIED AND TESTED   This code will open the file Test xls and run the macro TestMacro which will in turn write to the text file TestResult txt  Option Explicit  Dim xlApp  xlBook  Set xlApp   CreateObject  Excel Application       gt  Change Path here Set xlBook   xlApp Workbooks Open  C  Test xls   0  True  xlApp Run  TestMacro  xlBook Close xlApp Quit  Set xlBook   Nothing Set xlApp   Nothing  WScript Echo  Finished   WScript Quit

User · Answer

I tried the above methods but I got the  macro cannot be found  error  This is final code that worked   Option Explicit  Dim xlApp  xlBook  Set xlApp   CreateObject  Excel Application   xlApp Visible   True      Import Add-Ins xlApp Workbooks Open  C   lt pathOfXlaFile gt  MyMacro xla  xlApp AddIns  MyMacro   Installed   True    Open Excel workbook Set xlBook   xlApp Workbooks Open   lt pathOfXlsFile gt  MyExcel xls   0  True     Run Macro xlApp Run  Sheet1 MyMacro   xlBook Close xlApp Quit  Set xlBook   Nothing Set xlApp   Nothing  WScript Quit   In my case  MyMacro happens to be under Sheet1  thus Sheet1 MyMacro

User · Answer

I tried to adapt  Siddhart s code to a relative path to run my open form macro  but it didn t seem to work   Here was my first attempt   My working solution is below   Option Explicit  Dim xlApp  xlBook dim fso dim curDir set fso   CreateObject  Scripting FileSystemObject   curDir   fso GetAbsolutePathName      set fso   nothing  Set xlApp   CreateObject  Excel Application       gt  Change Path here Set xlBook   xlApp Workbooks Open curDir  amp   Excels CLIENTES xlsb   0  true  xlApp Run  open form  xlBook Close xlApp Quit  Set xlBook   Nothing Set xlApp   Nothing  WScript Echo  Finished     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

Since my related question was removed by a righteous hand after I had killed the whole day searching how to beat the  macro not found or disabled  error  posting here the only syntax that worked for me  application run didn t  no matter what I tried   Set objExcel   CreateObject  Excel Application      Didn t run this way from the Modules  objExcel Application Run  c  app Book1 xlsm Sub1    Didn t run this way either from the Sheet  objExcel Application Run  c  app Book1 xlsm Sheet1 Sub1    Nor did it run from a named Sheet  objExcel Application Run  c  app Book1 xlsm Named Sheet Sub1     Only ran like this  from the Module1   Set objWorkbook   objExcel Workbooks Open  c  app Book1 xlsm   objExcel Run  Sub1    Excel 2010  Win 7

User · Answer

Ok  it s actually simple  Assuming that your macro is in a module not in one of the sheets  you use     objExcel Application Run  test xls dog      notice the format of  workbook name  macro   For a filename with spaces  encase the filename with quotes   If you ve placed the macro under a sheet  say sheet1  just assume sheet1 owns the function  which it does       objExcel Application Run   test 2 xls  sheet1 dog    Notice  You don t need the macro testfunction notation you ve been using

User · Answer

Hi used this thread to get the solution   then i would like to share what i did just in case someone could use it   What i wanted was to call  a macro that change some cells and erase some rows  but i needed for more than 1500 excels  approximately spent 3 minuts for each file   Mainly problem  -when calling the macro from vbe   i got the same problem  it was imposible to call the macro from PERSONAL XLSB  when the script oppened the excel didnt execute personal xlsb and wasnt any option in the macro window   I solved this by keeping open one excel file with the macro loaded a xlsm  before executing the script   Then i call the macro from the excel oppened by the script      Option Explicit     Dim xl     Dim counter     counter  10      Do    counter   counter   1    Set xl   GetObject    Excel Application     xl Application Workbooks open  C  pruebas macroxavi IA 030-08-026   amp  counter  amp    xlsx    xl Application Visible   True   xl Application run   a xlsm  eraserow     Set xl   Nothing     Loop Until counter   517    WScript Echo  Finished     WScript Quit

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

[excel] Run Excel Macro from Outside Excel Using VBScript From Command Line

Examples related to excel

Examples related to vba

Examples related to command-line

Examples related to vbscript