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) — user2140173
– user2140173, Commented Aug 12, 2013 at 8: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 — DennisH
– DennisH, Commented Aug 12, 2013 at 8: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
– Brian.D.Myers, Commented Aug 12, 2013 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
– DennisH, Commented Aug 13, 2013 at 6:19

DennisH · Accepted Answer · 2013-08-12 10:14:05Z

1

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

answered Aug 12, 2013 at 10:14

DennisH

3833 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing VBA out of Shell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related