JimmyWright
New Member
- Joined
- Jun 13, 2014
- Messages
- 5
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
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
Last edited: