Is it possible to run a macro in Excel from external command?

Question

Let's say I want to program a VBA code in an external program that opens an Excel file, runs a macro, saves (and say yes to any pop up windows), and close Excel. Is it possible to do so? If so, how would I go about implementing it?

Answer 1

You can launch Excel, open a workbook and then manipulate the workbook from a VBScript file.

Copy the code below into Notepad.

Update the 'MyWorkbook.xls' and 'Sheet1' 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") 
  xlBook.Sheets("Sheet1").Cells(1, 1).Value = "My text"
  xlBook.Sheets("Sheet1").Cells(1, 1).Font.Bold = TRUE
  xlBook.Sheets("Sheet1").Cells(1, 1).Interior.ColorIndex = 6
  xlBook.Save
  xlBook.Close
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 

This code above launches Excel opens a workbook, enters a value in cell A1, makes it bold and changes the colour of the cell. The workbook is then saved and closed. Excel is then closed.

Answer 2

Depending on what you are trying to achieve you may also 'control' Excel via (OLE) Automation from another application such as Access or Word or from your own application written in another environment such as Visual Basic (6). It is also possible to achieve this via .Net using a language of your choice (although some are easier to implement than others).

Are you wanting to control Excel from an external application or simply trigger a VBA macro in an existing workbook from the outside?

Again, depending on your intention, the workbook could have an Auto Open macro which could be conditional run based on an external value (say an ini file or database value).