vba - 如果尚未打开，请打开工作簿；如果已打开，则获取该引用

Question

我有一个场景，可以在另一个工作簿路径中的工作簿中进行一些更改。但问题是我需要检查工作簿是否已经打开。如果不是，我需要将该打开的实例获取到工作簿变量。

这是我用来检查工作簿是否打开的代码，然后是打开的代码

Function IsFileOpen(fileFullName As String)
    Dim FileNumber As Integer
    Dim errorNum As Integer

    On Error Resume Next
    FileNumber = FreeFile()   ' Assign a free file number.
    ' Attempt to open the file and lock it.
    Open fileFullName For Input Lock Read As #FileNumber
    Close FileNumber       ' Close the file.
    errorNum = Err         ' Assign the Error Number which occured
    On Error GoTo 0        ' Turn error checking on.
    ' Now Check and see which error occurred and based
    ' on that you can decide whether file is already
    ' open
    Select Case errorNum
        ' No error occurred so ErroNum is Zero (0)
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied." is 70
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' For any other Error occurred
        Case Else
            Error errorNum
    End Select

End Function
Public Function getConsolidatedDataFile() As Workbook
    Dim p As String
    p = ActiveWorkbook.Path
    Dim cf As String
    cf = printf("{0}\ConsolidatedData.xlsx", p)
    Dim wb As Workbook
    Dim fo As Boolean
    fo = IsFileOpen(cf)
    If fo = False Then wb = Workbooks.Open(filename:=cf)
    ''I need to get the code for this place of fo is true
    getConsolidatedDataFile wb

End Function

因此，如果文件打开，我需要将该工作簿放入该 wb 变量中。

Answer 1

我有办法了

If fo = False Then
    Set wb = Workbooks.Open(filename:=cf)
Else
    Dim w As Workbook
    For Each w In Workbooks
        If w.FullName = cf Then
            Set wb = w
        End If
    Next
End If

这是在循环中遍历所有工作簿，如果有则引用该引用..