CODEWITHSUNDEEP
excelvbafunctionvariablesboolean
Simone Evans
Simone Evans

Reputation: 311

Excel VBA Check if File is open function

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?

Upvotes: 1

Views: 18791

Answers (2)

HTH
HTH

Reputation: 2031

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)

Upvotes: 2

DDV
DDV

Reputation: 2395

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

Upvotes: 3

Related Questions