check whether a workbook is already open using VBA

Dear Experts,

below nice code (courtesy of Subodh Tiwari (Neeraj) ) checks for the existence of a specific file as well as the existence of a specific worksheet in that file.

The macro works just fine. I have a new requirement for this code and it would be great if some expert could tweak the code accordingly.

LIne 16 says: Set wb = Workbooks.Open(FilePath): The file may already be open and in these case no Open-Action should occur.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub File_Exist_With_Dir()
    Dim FilePath As String
    Dim FileExists
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SheetFound As Boolean
    Application.ScreenUpdating = False    
    FilePath = "C:\MyFolder\MyDocuments\Isele\ProductList.xlsx"
    On Error Resume Next
       FileExists = Dir(FilePath)
    On Error GoTo 0
    If FileExists = "" Then
        MsgBox "File doesn't exist", vbInformation, "File not found"
        Exit Sub
    Else
        Set wb = Workbooks.Open(FilePath)
        On Error Resume Next
        Set ws = wb.Sheets("Overall_List")
        On Error GoTo 0
        If Not ws Is Nothing Then
            SheetFound = True
        End If
        wb.Close False
        If Not SheetFound Then
            MsgBox "File exists but the Sheet Overall_List doesn't exist in it.", vbExclamation
            Exit Sub
        Else
            MsgBox "File and Sheet both exist", vbInformation, "File exists"
            Call MyNewFile
        End If
    End If
    Application.ScreenUpdating = False
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Here's an article that might be useful to you
0
Fabrice LambertFabrice LambertCommented:
Hi,

Simply try to reference the workbook via the workbooks collection
Dim filePath As String
FilePath = "C:\MyFolder\MyDocuments\Isele\ProductList.xlsx"

    '// extract the file name
Dim fileName As String
fileName = mid(filePath, inStrRev(filePath, "\") + 1)
If(existInCollection(fileName, application.workbooks)) Then
    '// workbook already open
Else
    '// workbook isn't open
End If

Open in new window

Public function existInCollection(byval key As String, byRef col As Collection) As Boolean
    existInCollection = existInCollectionByVal(key, col) or existInCollectionByRef(key, col)
End Function

    '// check if the item exist as a value
Private function existInCollectionByVal(byval key As String, byRef col As Collection) As Boolean
On Error goto Error
    Dim item As Variant
    item = col(key)
    existInCollectionByVal = True
Exit Function
Error:
    existInCollectionByVal = False
End Function

    '// Check if the item exist as a reference
Private function existInCollectionByRef(byval key As String, byRef col As Collection) As Boolean
On Error goto Error
    Dim item As Variant
    Set item = col(key)
    existInCollectionByVal = True
Exit Function
Error:
    existInCollectionByVal = False
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewCommented:
@Fabrice,

If I have two workbooks with the same base name, let's say "c:\dir1\test.xls" and "c:\dir2\test.xls", and I have "c:\dir1\test.xls" open, but not "c:\dir2\test.xls", how does you approach differentiate between the two of those and not always report that any "test.xls" is already open?


»bp
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewCommented:
Here is an approach you could try.

Function WorkbookIsOpen(strPath As String) As Boolean
    Dim wb As Workbook
    Dim path As String
    path = LCase(strPath)
    WorkbookIsOpen = False
    For Each wb In Application.Workbooks
        If LCase(wb.FullName) = path Then
            WorkbookIsOpen = True
            Exit Function
        End If
    Next wb
End Function

Open in new window


»bp
0
Fabrice LambertFabrice LambertCommented:
@Bille Prew:
@Fabrice,

If I have two workbooks with the same base name, let's say "c:\dir1\test.xls" and "c:\dir2\test.xls", and I have "c:\dir1\test.xls" open, but not "c:\dir2\test.xls", how does you approach differentiate between the two of those and not always report that any "test.xls" is already open?
My approach will report that the workbook is already open. Excel have the same behavior: Prevent you from openning two workbooks with the same name, no matter the path.
0
Bill PrewCommented:
Stepping back a bit, and reading the question again, I'm wondering why you want to add this logic?

As written the routine seems to be checking that the file exists, and if it does that the sheet "Overall_List" exists.

Both of those things can happen fine even if the workbook is currently open, and this procedure doesn't change the workbook, it immediately "closes" it after it is done (which won't actually close it if it's open already in Excel).

Just trying to better understand what your need is...


»bp
0
Andreas HermleTeam leaderAuthor Commented:
Dear Experts:

Thank you very much for your overwhelming support. I was able to integrate Fabrice's code into the existing code and it works fine.

Nevertheless I suggest awarding points to the other participants as well since they contributed nicely to my quesiton.

Bill, thank you very much for your insights into this subject.

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Again, thank you very much for your great support. I really appreciate your professional and swift expertise.

Great forum, great experts!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.