Excel VBA 파일이 열려 있는지 확인 기능 (Excel VBA Check if File is open function)

문제 설명

Excel VBA 파일이 열려 있는지 확인 기능 (Excel VBA Check if File is open function)

이것은 쉬운 기능인 것 같고 솔루션은 간단해야 하지만 문제를 찾을 수 없습니다.

서브에서 호출되는 함수가 있는데 파일이 열지 않으면 엽니다. 함수는 완벽하게 실행되지만 호출하는 기본 하위 항목으로 반환되면 변수(True 또는 False)의 값이 손실되고 행에 아래 첨자가 범위를 벗어남이라는 오류가 발생합니다. 메인 서브에서 wb = Workbooks(MasterFileF)를 설정합니다.

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

메인 서브로 돌아올 때 함수 변수의 값이 손실되는 것이 제가 어디에서 잘못되고 있습니까?

참조 솔루션

방법 1:

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)

방법 2:

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

(by Simone Evans、HTH、Dean)

참조 문서

1. Excel VBA Check if File is open function (CC BY‑SA 2.5/3.0/4.0)