VBA Opening Workbooks/Checking if Workbook is Open Best Practice and Why

Question

I have made a macro that copies a worksheet from one workbook to another, and I have a couple questions about best practice when it comes to opening workbooks.

From previous programming experience I know that you should check to see if something is already open before trying to open it. So far that has proven true with VBA as my macro crashes if the worksheet I'm copying from was already open. I would like to know what is the best way to test if it is open. It would likely not be the active workbook and testing If Workbooks(file) Is Nothing Then .... has given me errors. (This was what I found online as a possible solution)

If I exclude the test mentioned above I get an error on:

Set copyFromWB = Workbooks.Open(file) Set copyFromWS = copyFromWB.Sheets(copyFromSheetName) 'ERROR HERE

when the file is already open, but it works perfectly when the file is closed. I would like to know why an error is raised when the file is already open, a 'behind the scenes' answer is really what I'm looking for here, something that explains what the computer is thinking, moreso theory.

Also on that note, I want to know why testing If Workbooks(file) Is Nothing Then .... doesn't work for me when I've seen it being suggested on multiple VBA help forums. Is this an issue with types or does it have to do with the version of excel I am using?

For reference, "file" and "copyFromSheetName" exist and no errors arise when the file did not start from open at run-time.

Summary:

Cleanest way to test if a workbook is open?
Why does an already opened Workbook cause a crash when trying to access its worksheets? (I'm thinking it actually has to do with the line above Set copyFromWB = Workbooks.Open(file)
Why won't comparing Workbooks(file) to Nothing work? Is it a type issue, excel version issue, or something else?

I'm looking for more than a coded solution. It won't be much use to me if I don't understand why your code works and mine does not.

score 0 · Accepted Answer · answered May 24 '17 at 17:51

This is the cleanest way i know: Detect whether Excel workbook is already open .

It does not actually crash, but you can only open a file once in read-write mode, if you open it twice it asked you to open it in read-only.

So if you try this, the workbook will open every time, but it says Read-Only in the title.

Sub OpenWbk()
Dim xl As Object
Set xl = CreateObject("Excel.Application")
Dim wbk As Workbook
Dim sht As Worksheet
xl.Visible = True
Set wbk = xl.Workbooks.Open("C:\Users\User\Desktop\Book1.xlsm")
Set sht = wbk.Sheets(1)
End Sub

It probably throws an error because it wants to access something that does not exists. If you set wbk = Workbooks("FileName") (throws error if not opened) and say On Error Resume Next and then check If wbk Is Nothingit will work.
So the error message is some kind of protection i guess.

score 0 · Answer 2 · answered May 24 '17 at 17:58

UGP Might have beat me to the punch. This code will check to see if your workbook is open. If it is then it will set it to the variable myWB and it doesn't crash on me. If it is not open it will open it with the same variable.

Sub wsdcvbhui()
Dim wb As Workbook
Dim wbs As Workbooks
Dim myWB As Workbook

Set wbs = Application.Workbooks

    For Each wb In wbs
      If wb.Path & "\" & wb.Name = "C:\Files\Bounce.xlsx" Then ' change this to your file path and name
            Set myWB = wb
            GoTo skipOpen
    End If
    Next wb

    Set myWB = Workbooks.Open("C:\Files\Bounce.xlsx")
skipOpen:
myWB.Activesheet.range("B2").value = "Hello"
myWB.save
myWB.Close Saved = True

Set wbs = Nothing
Set myWB = Nothing
Set wb = Nothing
End Sub

score 0 · Answer 3 · answered Mar 08 '20 at 18:01

To find out if a workbook is open, you can use a function for that purpose, which takes advantage of the "On Error" statement.

Sub testIsOpen()
    Dim sFN     As String
    Dim bFlag   As Boolean
    sFN = "FileName.xlsx"     ' Put the name of your workbook here
    bFlag = IsWkbOpen(sFN)
    ' Print in Immediate window True or False
    Debug.Print bFlag
End Sub

Public Function IsWkbOpen(ByVal sFileName As String) As Boolean
    Dim wkb         As Workbook
    Dim bReturn     As Boolean
    On Error GoTo EH   
    bReturn = True
    Set wkb = Workbooks(sFileName)
    IsWkbOpen = bReturn
    Exit Function
EH:
    bReturn = False
    Resume Next
End Function

VBA Opening Workbooks/Checking if Workbook is Open Best Practice and Why

3 Answers3