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

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\" & "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.

This question is related to excel vba command-line vbscript

The answer is


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")
'~~> 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

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.


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


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")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open(curDir & "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 & "\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


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" & counter & ".xlsx"
  xl.Application.Visible = True
  xl.Application.run "'a.xlsm'!eraserow" 
  Set xl = Nothing


  Loop Until counter = 517

  WScript.Echo "Finished."
  WScript.Quit

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 > 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 > 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 

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:\<pathOfXlaFile>\MyMacro.xla"
xlApp.AddIns("MyMacro").Installed = True

'
Open Excel workbook
Set xlBook = xlApp.Workbooks.Open("<pathOfXlsFile>\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.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to command-line

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Flutter command not found Angular - ng: command not found how to run python files in windows command prompt? How to run .NET Core console app from the command line Copy Paste in Bash on Ubuntu on Windows How to find which version of TensorFlow is installed in my system? How to install JQ on Mac by command-line? Python not working in the command line of git bash Run function in script from command line (Node JS)

Examples related to vbscript

How to run VBScript from command line without Cscript/Wscript How to set recurring schedule for xlsm file using Windows Task Scheduler How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31') How do I rename a file using VBScript? How to get two or more commands together into a batch file How to run vbs as administrator from vbs? Find specific string in a text file with VBS script Getting current directory in VBScript Run Command Line & Command From VBS Permission denied on CopyFile in VBS