Executing an Excel Macro outside of Excel in a Bat or VB Script

Is there a way to execute a macro in Excel from at Bat File or VB Script file?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You can use VBScript to automate Excel and run the macro that way (though there can be issues if you want to do this with task scheduler), or you can put the macro into the Workbook_Open event and use a batch file to shell excel and load that workbook and the code will run automatically. Any preference?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
Rorya,

Can you give me an example of both ways? I would like to do this with task manager at some point, but it is not mandatory at this time.


morinia
0
RobSampsonCommented:
Hi, this VBS will open a file, run a macro, then close and save the file:

'==============
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Test.xls"
Set objExcel = CreateObject("Excel.Application")
Set objWB1 = objExcel.Workbooks.Open(strExcelFile, False, False)
objExcel.Visible = True
objWB1.Application.Run "Macro1"
objWB1.Close True
objExcel.Quit
'============

Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Rory ArchibaldCommented:
Your batch file could be as simple as:

START "C:\program files\microsoft office\office10\excel.exe" "c:\book1.xls"

changing paths as required.
Regards,
Rory
0
RobSampsonCommented:
Oh yeah, can't you also use a /m switch to run a specific macro?
START "C:\program files\microsoft office\office10\excel.exe" "c:\book1.xls" /m "MacroName"

I think I've read that somewhere?
Or would you have to use an Auto_Exec macro within that workbook?

Regards,

Rob.
0
Rory ArchibaldCommented:
/m is supposed to create a new workbook with a single macro sheet. I would use Workbook_Open rather than AutoExec but it's not a big deal in this case.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.