Excel VBA Check if File is open function

Question

This seems to be an easy function and solution should be straight forward, but I can't find the problem.

I have a function that gets called in a sub, it checks if the file is open, if not, to open it. The function runs perfectly but when it returns to the main sub that's calling it, the variable (True or False) loses its value and I get an error 9: subscript out of range on the line Set wb = Workbooks(MasterFileF) in the main sub.

Function wbOpen(wbName As String) As Boolean
Dim wbO As Workbook

    On Error Resume Next
        Set wbO = Workbooks(wbName)
        wbOpen = Not wbO Is Nothing
        Set wbO = Nothing

End Function



Sub Macro5()

Dim wb As Workbook
Dim path As String
Dim MasterFile As String
Dim MasterFileF As String


Application.ScreenUpdating = False

'Get folder path
path = GetFolder()
If path = "" Then
    MsgBox "No folder selected. Please start macro again and select a folder"
    Exit Sub
Else
End If


MasterFile = Dir(path & "\*Master data*.xls*")
MasterFileF = path & "\" & MasterFile

'Check if workbook open if not open it
If wbOpen(MasterFile) = True Then
    Set wb = Workbooks(MasterFileF)
Else
    Set wb = Workbooks.Open(MasterFileF)
End If

Where am I going wrong that the Function variables' values get lost when it returns to the main sub?

Youre also using On Error Resume Next but do reset it. Use On Error Goto 0 to do that. Also, I wouldn't use that approach, I would loop through the Workbooks in the Application.Workbooks collection to find the workbook. — DDV
– DDV, Commented Apr 6, 2020 at 12:19
That's how I'm currently running the code to see where the problem is. I use the watch window to see where the problem comes in. Everything is fine until it returns to the main sub, then wbOpen which is supposed to be True or False goes empty — Simone Evans
– Simone Evans, Commented Apr 6, 2020 at 12:20

DDV · Accepted Answer · 2020-04-06 12:27:28Z

3

I think the problem lies in your wbOpen function. You're setting that workbook object locally and not returning a value for the Boolean. See below:

Function wbOpen(ByVal wbName As String) As Boolean

    Dim wbO As Workbook

    For Each wbO In Application.Workbooks
        If InStr(1, wbO.Name, wbName) Then
            wbOpen = True
            Exit Function
        End If
    Next wbO

    wbOpen = False

End Function



Sub Macro5()

    Dim wb As Workbook
    Dim path As String
    Dim MasterFile As String
    Dim MasterFileF As String

    Application.ScreenUpdating = False

    'Get folder path
    path = GetFolder()
    If path = "" Then
        MsgBox "No folder selected. Please start macro again and select a folder"
        Application.ScreenUpdating = True
        Exit Sub
    End If

    MasterFile = Dir(path & "\*Master data*.xls*")
    MasterFileF = path & "\" & MasterFile

    'Check if workbook open if not open it
    If wbOpen(MasterFile) = True Then
        Set wb = Workbooks(MasterFileF)
    Else
        Set wb = Workbooks.Open(MasterFileF)
    End If

    Application.ScreenUpdating = True

End Sub

answered Apr 6, 2020 at 12:27

DDV

2,4155 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Simone Evans Over a year ago

I changed the code to the above but I'm still getting the same problem on the same code, and still losing the value for the boolean, unfortunately.

DDV Over a year ago

If you're getting error 9: subscript out of range on line Set wb = Workbooks(MasterFileF)... a) what is the value of MasterFileF at the error. Also, if it is reading that line then your function is returning true. So the error would lie in the String of MasterFileF...? Your trying to open a workbook with the incorrect address/ name/ doesn't exist somehow.

Simone Evans Over a year ago

MasterFileF gives me the string of the file name and path as expected. I changed the MasterfileF to MasterFile (which is just the file name), in the error line and that seems to work... I need to set it with the path file as well though. Any reason why that would be happening?

HTH Over a year ago

@SimoneFick " ...I changed the MasterfileF to MasterFile...", which precisely is my answer...

Simone Evans Over a year ago

@HTH sorry my webpage didn't update with your edited answer. I had to refresh my page. It worked thank you! I just replaced the path & "\" & MasterFile form your answer with MasterfileF

|

Instant Breakfast · Accepted Answer · 2022-04-12 12:43:38Z

I'd turn a little bit your code:

have the WbOpen() function return the open workbook, if found, via its arguments

Function wbOpen(wbName As String, wbO As Workbook) As Boolean
    On Error Resume Next
    Set wbO = Workbooks(wbName)
    wbOpen = Not wbO Is Nothing
End Function

and then in your main code simply go:

MasterFile = Dir(path & "\*Master data*.xls*")

If Not wbOpen(MasterFile, wb) Then Set wb = Workbooks.Open(path & "\" & MasterFile)

Edit

to add an enhanced version to handle workbook with same names but different paths

in this case you have to check both the file name and the path, but in different steps

so WbOpen() function becomes:

Function wbOpen(wbName As String, wbPath As String, wbO As Workbook) As Boolean
    On Error Resume Next
    Set wbO = Workbooks(wbName)
    On Error GoTo 0 ' restore error handling back
    
    If Not wbO Is Nothing Then ' in current excel session there already is an open workbook with same name (path excluded) as the searched one
    
        If wbO.path = wbPath Then ' the already open workbook has the same path as the searched one -> we got it!
            
            wbOpen = True
            
        Else ' the already open workbook has a different path from the searched one -> we must investigate ...
            
            If MsgBox("A workbook named after:" _
                       & vbCrLf & vbCrLf & vbTab & wbName _
                       & vbCrLf & vbCrLf & " is already open but its path is different from:" _
                       & vbCrLf & vbCrLf & vbTab & wbPath _
                       & vbCrLf & vbCrLf & "If you want to open the new found one, the already open one will be closed" _
                       & vbCrLf & vbCrLf & vbCrLf & "Do you want to open the new found one?", vbQuestion + vbYesNo) = vbYes Then
                
                wbO.Close True ' close the currently opened workbook with same name but different path from searched one
                               ' the opening of the new one will be made in the main sub, after this function returning 'False'
            Else
                wbOpen = True ' you chose not to open the searched one and stay with the currently open one -> return 'True' to say you are done
            End If
            
        End If
            
    End If
    
End Function

and the relevant part of your main code would change to:

MasterFile = Dir(path & "\*.xls*")

If Not wbOpen(MasterFile, path, wb) Then Set wb = Workbooks.Open(path & "\" & MasterFile)

@SimoneFick, see edited answer to deal with searched workbooks with same name but differet path then open ones

Collectives™ on Stack Overflow

Excel VBA Check if File is open function

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related