Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Run Excel Macro using VBScript

Have you ever wanted to run an Excel Macro without actually opening Excel? Follow this tutorial to learn how you can make that happen with VBScript.

Example

Run Macro from Outside Excel

'Code should be placed in a .vbs file
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\Ryan\Desktop\Sales.xlsm'!SalesModule.SalesTotal"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

Tutorial

Are you ready to learn how to customize this to run your own Excel macros?

Initial File Setup

  1. Create a file anywhere on your computer and name it ReportSales.vbs
    The name of the file isn’t important. The important part is the file should have a .vbs extension.
  2. Open the file and paste the example macro into the file.

Customizing the VBScript

To run your own Excel macros, there’s only one line you’ll need to edit:
objExcel.Application.Run "'C:\Users\Ryan\Desktop\Sales.xlsm'!SalesModule.SalesTotal"

The Application.Run line looks for the 3 inputs described below:
objExcel.Application.Run "'full path to excel file'!module name.macro name"

For instance, if you had an Excel macro under Sheet1 of your workbook stored in My Documents, your Application.Run line would look something like:
objExcel.Application.Run "'C:\Users\Ryan\Documents\WorkbookName.xlsm'!Sheet1.MacroName"

Keep in mind, if your file name or file path has spaces, you’ll need to surround the path with double quotes.

Saving your Workbook

If you want to save your workbook after running your macro, add a new line and type objExcel.Application.Save in your VBScript, right before the line that reads objExcel.Application.Quit.

Opening a Read-Only Workbook

To open a read-only workbook, try adding the following line right before your objExcel.Application.Run line:
Set book = objExcel.Workbooks.Open("C:\Users\Ryan\Desktop\Sales.xlsm",,TRUE)

The path shown in the Open statement should match the file path shown in your Run statement.

Running your Macro

Once you’ve modified the Application.Run statement, save your file and close it. Now, all you have to do to run your macro is double click ReportSales.vbs!

The macro I referenced in my example calculates the total return on sales for the current month. It also pulls information from a database and reports how many new sales I made yesterday.

Run Macro without Opening Excel

Each morning, I’ll execute this VBScript code directly from Microsoft Outlook to get a quick snapshot of how my business is performing without having to wait for Excel to load! Do you want to try the same? Check out my tutorial on how to run applications from your Outlook Ribbon.

Application Ideas

There are many instances where you could copy your Excel Macro to a VBScript file and run it directly, but sometimes you don’t want to deal with that headache. It’s in these moments where it’s beneficial to create a VBScript file to run your existing macros from outside Excel. You already have a working VBA macro. Why reinvent the wheel to convert it to VBScript?

For example, if you already have an Excel Macro to print all your worksheets to a PDF, there’s no point rewriting it to work with VBScript. It’s also a waste of time to open your Excel workbook just to print the sheets to a PDF. With this method, you can print all your sheets to a PDF in the background, without waiting for Excel to load. You can even do it directly from Outlook, if you wanted.

This is just one example - Use your imagination and let me know what you come up with!

Comments

Before you go, I encourage you to check out CF Shapes - the new Excel Add-In which enables conditional formatting for shapes. Enjoy 20% off from now until 7/31/2015!

If you like what you see, subscribe to my email list, share this article with a friend, submit a comment below and follow me on Google+! Come back often to see more great VBA ideas.

Remember, if you or your company are struggling with VBA, just contact me. I look forward to working with you!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.