Executing VBA out of Shell

Question

I am generating Excel Files with Pentaho Data Integration and I want to start a Macro automaticly after creation. Until now, the Macro is started while opening the file. But this is not a good way: Some users dont have permissions to execute Macros and each time you open the file Excel is asking if you want to save the changes.

I am wondering if there is a way to execute a VBA Macro in MS Excel out of the Windows Shell. What I found is this code to open a file:

Shell "C:\Programme\Office2k\Office\EXCEL.EXE " & _"C:\...\MyExcelFile.xls"

But this is not what I want. I want to start the Macro exactly one time, and before any user opened it.

Do you have any ideas?

you can't run a macro from a closed file on a closed file. There is a `Workbook_Open()` function you could look into but that's just an alternative (requires opening) — , Aug 12 '13 at 08:55
mehow : That is what I do now. Not a good way. @d-stroyer : Thanks, VBScript could be the answer. I found a possible way here [Link](http://stackoverflow.com/questions/10232150/run-excel-macro-from-outside-excel-using-vbscript-from-command-line) — DennisH, Aug 12 '13 at 08:59
I assume the macro does something not related to the data in the spreadsheet correct? If it's doing something with data, why no do that in PDI? — Brian.D.Myers, Aug 12 '13 at 17:14
@Brian.D.Myers The Macro is does some automatic corrections on the data and is marking them in the sheet.In PDI, as far as i know, i can only load data into the Excel File. I can't mark some values and also i can't load excel-functions in some cells in a special case. — DennisH, Aug 13 '13 at 06:19

score 1 · Accepted Answer · answered Aug 12 '13 at 10:14

The solution with vbscript looks like this (Open, Save, Close without User Interaction):

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\dev\testo.xls")

objExcel.Application.Run "testo.xls!test"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close 

objExcel.Application.Quit
WScript.Quit

Executing VBA out of Shell

1 Answers1