I'm trying to ensure that a particular workbook (named Despatch_Schedule) on a particular machine is closed at certain times
of the day. The workbook is populated with data from an SOP, and needs to be closed while the update takes place.
I have made a new workbook and given it a Workbook_Open() macro which closes Despatch_Schedule. I planned to schedule a batch
file to open this new workbook for me from the machine it is on, which in turn will close Despatch_Schedule, save the changes
and then close itself. This works fine as long as I open the new workbook myself. If I try to open the new workbook from the
batch file, it does not.
The batch file simply opens the workbook like this:
CDProgram FilesMicrosoft OfficeOffice10
Excel.exe "Sellit-serverrouteonePRD ReportsCloseDespSched.xls"
The Wookbook_Open() macro consists of the following:
On Error GoTo OpenWorkBook:
If Err.Number = 9 Then
Workbooks.Open FileName:="Sellit-serverrouteonePRD ReportsCurrentDespatch_Schedule.xls"
If I have my main Despatch_Schedule workbook open and then open my new workbook, it will close it. If I have
Despatch_Schedule open and then run the batch file that calls the new workbook, it will open another instance of
Despatch_Schedule (i.e. go into the error trap, as if there wasn't an instance of it open), and close that. I didn't think
that I could have two workbooks with the same name open at the same time, but I've put an exit sub in the code and had a
look, and it does.
I'd be grateful of any assistance that anyone can offer.