Search code examples
shellvbaexcelpentaho

Executing VBA out of Shell

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?

Solution

  • 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