PDA

View Full Version : Issue with code to check whether an excel file is open or not



CharlieG
01-04-2020, 03:43 PM
Hi Everyone,

need proper code to check whether a specific workbook (incl. hardcoded location) is already open in any instance of excel on the local machine before going further.

Various options found online have not worked and confused me further.

Will run from button on the ribbon so need to first as per the above (exiting the macro without an error message if the choice is made not to open the file.
If the file is either active or the choice is made to open it, it then has to be made the active sheet and then run the macro from my PERSONAL.XLSB file which already works fine (unless the targeted workbook is not open)

Huge thanks in advance


Sub IsWorkBookOpen()


'check whether tech library is open (option to open if not)
'
Function OpenFileCheck(Name As String) As Boolean
Dim xWb As Workbook
On Error Resume Next
Set xWb = Application.Workbooks.Item(Name)
isLibraryOpen = (Not xWb Is Nothing)


End Function


Dim xRet As Boolean
xRet = OpenFileCheck("Form 25 Iss 04 - Technical library.xlsx")

If xRet Then
Windows("Form 25 Iss 04 - Technical library.xlsx").Activate

Else
Msg = "Do you want to open the the technical library?"
Ans = MsgBox(Msg, vbYesNo + vbQuestion, "Open Technical Library?")

If Ans = vbNo Then
Exit Function
If Ans = vbYes Then
Workbooks.Open Filename:= _
"C:\Users\charlieg\Desktop\stuff for test bed and updates\Technical Library\Form 25 Iss 04 - Technical library.xlsx"
Windows("Form 25 Iss 04 - Technical library.xlsx").Activate
End If
End Function


Sub CallAnotherMacro()
' Call another macro once technical library has been opened
'
Application.Run "'PERSONAL.XLSB'!DuplicateLibraryTemplateSheet"


End Sub