ExcelBanter - Running Macros

Hi Cindy,

We use Excel to "dump" information into from our program.
After we are done we need to run a macro while Excel is
still open. Can anyone tell me how to do this from VB
outside of Excel?

Firstly, if you want to do anything in Excel, you have to do it with Excel. Whether the actual code sits within an Excel workbook or in
VB is up to you - though within the workbook is likely to be quicker. Something like the following should get you started:

1. Add a project reference to the Excel Object library, then use:

Dim oXL As Excel.Application
Dim oDataWB As Excel.Workbook
Dim oCodeWB As Excel.Workbook

'Create a new Excel instance for us to use
Set oXL = New Excel.Application

'Open the data file
Set oDataWB = oXL.Workbooks.Open("Path\YourDataFile.xls")

'Open the workbook containing the macro to run
Set oCodeWB = XL.Workbooks.Open("Path\WorkbookContainingYourCode .xls")

'Run the macro, passing in the name of data workbook,
'so in a standard module in the Code workbook, you'd have
'
'Public Sub RoutineInCodeWorkbook(sDataWBName As String)
'
oXL.Run "RoutineInCodeWorkbook", oDataWB.Name

'Close and save the data workbook
oDataWB.Close True

'Close without saving the macro workbook
oCodeWB.Close False

'Clear down our object variables
Set oDataWB = Nothing
Set oCodeWB = Nothing

'Close Excel and clear down our object reference
oXL.Quit
Set oXL = Nothing

Alternatively, instead of the two lines that open the code workbook and run the routine, you can include all the code to format the
report within the VB module.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk