Run excel macro from batch file. Workbook Open

**Jonsby** · 02-24-2009, 07:33 AM

Hi all,
I wanted to run a batch file which does something like,

Dir /b >Test.csv
Start Test.csv

And then runs an Excel macro once the workbook is open.

I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.

I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.

I do however have personal.xlsb which opens every time if that helps.

Any help understanding this situation or ways of rectifying it will be greatly appreciated

.

Thanks!

**Cheeky Charlie** · 02-24-2009, 10:33 AM

You can create a class module in your personal macro workbook which handles the application level workbook open event. This could test the name of the opened file and run a predefined macro from there.

CC

**Jonsby** · 02-25-2009, 10:34 AM

Thanks that's exactly what i want!
Thing is now I know hardly anything about class modules. I got the class module to recognize when a workbook opens and then calls another module but I'm not sure what i can use to identify the name of the workbook open.
I tried:

If ActiveWorkbook.Name = "test.csv"

but then I keep on getting grief about Run-time error '91' Object variable or With block variable not set.
Thank you a lot for any help.

**Kenneth Hobson** · 02-25-2009, 11:00 AM

Would it not be more simple to just have the BAT file run an xls? The xls could then do whatever you need from its Open event.

**Cheeky Charlie** · 02-25-2009, 12:37 PM

but the file to be opened is not an xls, it's a csv, if you were to do it this way you'd have to pass the csv filename to the xls with the open command (is this possible?) or write the filename to some specifed location, for the xls to read it. But this is less robust than opening the csv and operating on it via the application_workbook_open?

**Kenneth Hobson** · 02-25-2009, 01:04 PM

If you write a static BAT file, then what is the difference in writing a static xls? There is nothing wrong with writing Test.bat, which opens Test.xls, which creates Test.csv, and then opens Test.csv in Excel and then closes Test.xls. It sounds complicated but it is not.

Yes, you could create a generic BAT file that accepts parameters and then open an xls workbook that would get the parameters from the BAT file's passed parameters in a command line or registry or external file or environment variable or other means. This is a more complete solution but takes more effort.

Here are some links for command line parameters passed to the excel application when the first is the workbook name with this code:
http://www.vbforums.com/showthread.php?t=366559
http://www.dailydoseofexcel.com/arch...uments-in-vba/
http://www.pcreview.co.uk/forums/thread-2004448.php

I have used the DOS Dir command in VBA but FSO methods in VBA or even the standard VBA Dir() command is fine for single folder iterations.

If you like to use DOS methods in VBA, I think you will benefit by reviewing this thread.

**Jonsby** · 02-26-2009, 08:14 AM

Thanks for all the help

.
I found a way of getting it to work with the following process (I'm not sure if its the best way but I know its a way that works pretty well)

In my Personal.XLSB workbook (thats hidden and opens everytime a workbook is open)
I added the following:
-Class Module (named it clsapp)
-Module
-Code inside 'ThisWorkbook'

-Class Module Code
Public WithEvents AppEvents As Application

Private Sub AppEvents_WorkbookActivate(ByVal Wb As Workbook)
Call WorkingFile(Wb)
End Sub

-Module Code
Dim AppObject As New clsapp

Sub Init()
'Called By Workbook_Open
Set AppObject.AppEvents = Application
End Sub

Sub WorkingFile(Wb)
'Checking if Opened Workbook is the correct Workbook
If ActiveWorkbook.Name <> "test.csv" Then End
End Sub

-Code inside 'ThisWorkbook'
Private Sub Workbook_Open()
Call Init
End Sub

I hope this is a help to anyone else who had the same problem as I did.

Thanks!