Using vbscript to check if excel workbook is open

[JimmyWright]

Good morning to all,

I have been tasked with replacing an old report using excel 2000 with excel 2013. We are in the process of upgrading old (old!) computers that run all our reports. One of the reports is a daily shift report, and depending on the time of day it saves as a day shift report or a night shift report. The problem is that it is a blank excel workbook with no macros, and another excel has all the macros in it to preform all the functions needed. The excel workbook with the macros is called from the schedule task manager, runs the macros and closes itself. I would like to do away with having to use another excel workbook to do all the functions needed using a vbscript. I have everything I need to open the workbook and close the workbook, but I need a way to check if the workbook is open and if it is to close is so that I can open it again to run the macros. I find that leaving workbooks open all the time is not the best way of doing things.

My question is what script can I use to check if the workbook is open, and if so save and close it. I'm very green behind the ears with vb scripting and macros, I have been searching some but my time frame is getting shorter. Also this workbook is not and won't be shared. Everything will be done on the same PC.

Here are some of the places I've gone to get an idea but I cant seem to get something to work.
Determine if Excel workbook is already open
vbscript - Check if a specific Excel file is open when several Excel files are open and activate it - Stack Overflow
How to Close Excel file from VBScript without being prompted? - Stack Overflow

Examples of what I've tried:

ExcelFileName = "DayShiftRpt.xlsm"

On Error Resume Next
Set xl = GetObject("DayShiftRpt").Application 'attach to running Excel instance
If Err Then
If Err.Number = 429 Then
WScript.Echo "Workbook not open (Excel is not running)."
Else
WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
End If
WScript.Quit 1
End If
On Error Goto 0

Set wb = Nothing
For Each obj In xl.Workbooks
If obj.Name = ExcelFileName Then 'use obj.FullName for full path
Set wb = obj
Exit For
End If
Next
If wb Is Nothing Then
WScript.Echo "Workbook not open."
WScript.Quit 1
End If

And:

Function IsWorkBookOpen(ByVal OWB As String) As Boolean
IsWorkBookOpen = False
Dim WB As Excel.Workbook
Dim WBName As String
Dim WBPath As String
Err.Clear
On Error Resume Next
OWBArray = Split(OWB, "\")
Set WB = Application.Workbooks(OWBArray(UBound(OWBArray)))
WBName = OWBArray(UBound(OWBArray))
WBPath = WB.Path & "\" & WBName
If Not WB Is Nothing Then
If UBound(OWBArray) > 0 Then
If LCase(WBPath) = LCase(OWB) Then IsWorkBookOpen = True
Else
IsWorkBookOpen = True
End If
End If
Err.Clear
End Function

 

[JimmyWright]

Hey I've found this (part of another code) on another site and its works for what I need to start off with.

OWB = "C:\Users\RED\Documents\~$DayShiftRpt.xlsm"
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(OWB) Then
WScript.Echo "File open"
Else
WScript.Echo "File not open"
End If


How can I get the script to close the workbook in the if statement instead of (WScript.Echo "File open"). I'm having a difficult time figuring out how to come by closing the work book.

 

[JoeMo]

See if this works for you:

Sub IfOpenThenClose()
Dim sF As String
sF = "DayShiftRpt.xlsm"  '<-- Enter workbook Name.FileExtension between the quote marks
If WorkbookOpen(sF) Then
   With Workbooks(sF)
        .Save
        .Close
    End With
End If
End Sub

Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[JimmyWright]

See if this works for you:

Sub IfOpenThenClose()
Dim sF As String
sF = "DayShiftRpt.xlsm"  '<-- Enter workbook Name.FileExtension between the quote marks
If WorkbookOpen(sF) Then
   With Workbooks(sF)
        .Save
        .Close
    End With
End If
End Sub

Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Hey JoeMo, Thank you for the reply, is this a script that is within excel? or can it it be a separate vbscript ran in CMD?
Also "Enter workbook Name.FileExtension between the quote marks" does this mean that it should be written like "C:\User\RED\Documents\DayShiftRpt.xlsm"

 

[JoeMo]

Hey JoeMo, Thank you for the reply, is this a script that is within excel? or can it it be a separate vbscript ran in CMD?
Also "Enter workbook Name.FileExtension between the quote marks" does this mean that it should be written like "C:\User\RED\Documents\DayShiftRpt.xlsm"

It's a VBA script for use within Excel. You only need the workbook name and file extension like "DayShiftRpt.xlsm" not the full path & filename.

 

[JimmyWright]

Ok I see let me try it, one more question, This is to work in a separate excel workbook or in the same one?

 

[JoeMo]

Ok I see let me try it, one more question, This is to work in a separate excel workbook or in the same one?

You can run it from any open workbook, but from what you describe as your objective, it seems to me you want it in some workbook other than the one you are testing for being open.

 

[JimmyWright]

Thank you JoeMo it does work. Thank you for your help

 

[JoeMo]

Thank you JoeMo it does work. Thank you for your help

You are welcome - thanks for the reply.