execute macro from command line

[Guest]

Hello,

I would like to run a macro in a workbook from the command line. The macro
modifies the contents of the workbook. This is so I can automate a task
rather than open the workbook, run macro, and save.

Thanks!

 

[Guest]

it's unusual to execute a vba macro from the command line.
how about coding windows shell program that automates the excel?
shell programming uses vbscript(*.vbs) or javascript(*.js)
if you're familiar with vba, you would not have difficulty with that.

plz refer http://msdn2.microsoft.com/en-us/library/aa286544.aspx

 

[Steve Yandl]

I'm not sure that you can do this directly but you can use a vbs file as an
intermediary and that might accomplish what you want.

For the example, I have a macro named "macroA" that is in the workbook
"C:\Test\Bookx.xls". I then use notepad to create a file named
"C:\Test\LaunchMacro.vbs" (the vbs file would not have to be in the same
folder, but you will need to know the path for your command line entry).
The contents of LaunchMacro.vbs are shown between the lines below:

______________________________________

myMacro = WScript.Arguments.Item(0)

Set objXL = CreateObject("Excel.Application")
Set objWkbk = objXL.Workbooks.Open("C:\Test\Bookx.xls")
objXL.Run myMacro

objWkbk.Save
objWkbk.Close
objXL.Quit

_________________________________________

Now, if I launch the vbs file and hand it the name of the macro as the
argument, the workbook will open hidden, run the macro, save changes and
close. The command line in this case would be:

C:\Test\LaunchMacro.vbs "macroA"


Steve

 

[NickHK]

Whilst I would consider the other replies better, you can start a new
instance of Excel, passing arguments, that are parsed by your WB.
See the earlier thread in this NG "Reading command line parameters in a VBA
program".

NickHK

 

[Guest]

Thanks!

Seems to work except the macro is not deleting worksheets. Is this because
there is a prompt that asks if you really want to delete the worksheet? Any
workaround to that?

 

[Guest]

Nevermind! I set

Application.DisplayAlerts = False

At the start of the macro and set to true at the end. Working 100%