Close a Specific Workbook from a Batch File

[M_S_Jones]

Hello,

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:

C:
CD\Program Files\Microsoft Office\Office10
Excel.exe "\\Sellit-server\routeone\PRD Reports\CloseDespSched.xls"
EXIT

The Wookbook_Open() macro consists of the following:

On Error GoTo OpenWorkBook:
Workbooks("Despatch_Schedule.xls").Activate

OpenWorkBook:
If Err.Number = 9 Then
    Workbooks.Open FileName:="\\Sellit-server\routeone\PRD Reports\Current\Despatch_Schedule.xls"
    Resume
End If
Workbooks("Despatch_Schedule.xls").Close True
ActiveWorkbook.Close

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.

Thanks,

Matthew

 

[DCrake]

May sound silly but here is a function that checks if an Excel workbook is Open.

Function IsXLBookOpen(strName As String) As Boolean
     
     'Function designed to test if a specific Excel
     'workbook is open or not.
     
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
     
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
     
     'Set all to False
    IsXLBookOpen = False
     
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     
     'Release the instance
    Set XLAppFx = Nothing
     
End Function

If you run this and find that it is then you can terminate it by using the normal quit command

David

 

[M_S_Jones]

Thank you for your reply, David.

I have just tried using that in an if statement and I still have the same problem in that it runs fine as long as I open the file myself. As soon as the batch file comes into the equation I get a problem. The problem I am getting now is 'subscript out of range' and when I debug I am shown the line within my if statement that closes the workbook. I have tried closing just the file name, also the name and full path, but I get the same error for both. I have also found that I cannot set focus to that workbook upon confirming that it is open, i.e.

If IsXLBookOpen("Despatch_Schedule.xls") Then
       Workbooks("Despatch_Schedule.xls").Activate
End If

I get the same error there too.

Thanks,

Matthew

 

[M_S_Jones]

If no one has any further ideas on how to get this to work, can anyone suggest another way of doing this? Is there a way to call a macro in a spreadsheet from a batch file or VB Script that would execute the macro within the open workbook, and not try to open a new instance of it? If so, then I could add a macro to close the workbook and save changes, and schedule this macro to be called.

Thanks,

Matthew