Running a Macro from a Batch File

[azizrasul]

Is their a way of creating a batch file which will open an Excel workbook and run a single macro?

 

[Pgmer]

With Excel 2003, you could add the 'Autpen' macro. When Excel opens a spreadsheet that has macros, it looks for one called 'Autpen'. If it finds this macro, it automatically runs. This macro is stored with the spreadsheet, so each spreadsheet can have its own 'Autpen' macro. To avoid the 'enable macros' prompt when the spreadsheet is opened, you would need to set the macro security to 'low'.

 

[random_noise]

Aziz,

you can create a vbscript file which opens your Excel file.

If you put your macro code into the workbook_open it will rin in the same manner which pgmer mentioned.

I DO NOT recommend setting macro security to low. Doing this mean you could open a file with a macro which will harm your computer without you even knowing it was doing so.

Create your xls file with the workbook_open code doing wat you want it to then launch it by typing the following code into notepad then saving it as filename.VBS (this makes the file a vb script which windows will run if it's double clicked)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\Test\book1.xls")
objExcel.Quit

change the path to point to your excel file.

Lauching your excel file in this way will not ask you to enable macros, but still lets you keep macro security set to medium

 

[John_w]

See my posts here --> http://www.mrexcel.com/forum/showthread.php?t=302970

 

[azizrasul]

random noise, when I double click the vb script file, the Excel file opens but it's not visible?

I'll look at the code in your link John_W.

 

[random_noise]

aziz,

yes the code I gave you is to just open, run a macro & then close it.

You can change the last line to

objExcel.Visible = True

if you want Excel to be visible (note workbook_open macro will run before excel appears).

 

[azizrasul]

OK that did the job.

What additional code can I add to run a macro called 'Macro 1'?

 

[Mike Blackman]

Hi Aziz,

Before the quit line you should be able to call a macro from within the code like this.

Call Macro 1

Or Simply

Macro 1

I assume this will work with RandomNoise's code. I don't know if its important where the macro is stored or not off the top of my head.

 

[azizrasul]

It didn't like that.

Also I've realised you can't have a macro called 'Macro 1' as it will not allow any spaces.

 

[Mike Blackman]

Hi,

I assume you've also tried calling a macro with a valid name in the same way and that hasn't worked also?

Generally you can call other macro's from within the current one.