Excel VBA检查工作簿是否打开，如果不是，打开它

Question

下面的代码工作不正常。尝试运行宏时遇到错误400。你可以稍微回顾一下这段代码吗？我不确定问题是否与我引用的函数变量不同。

Sub AutoFinal()  
    Dim final_wb As Workbook, shop_stat_wb As Workbook 
    Dim book2 As String 
    book2 = "Workbook_I_need.xlsx" 
    Dim book2path As String 
    book2path = ThisWorkbook.Path & "\" & book2 
    Set final_wb = ThisWorkbook 
    If IsOpen(book2) = False Then Workbooks.Open (book2path) 
    Set shop_stat_wb = Workbooks(book2)  
End Sub 

Function IsOpen(strWkbNm As String) As Boolean  
    On Error Resume Next 

    Dim wBook As Workbook 
    Set wBook = Workbooks(strWkbNm) 

    If wBook Is Nothing Then 'Not open 
     IsOpen = False 
     Set wBook = Nothing 
     On Error GoTo 0 
    Else 
     IsOpen = True 
     Set wBook = Nothing 
     On Error GoTo 0 
    End If  
End Function 

Answer 1

IsOpen可以简化为：

Function IsOpen(strWkbNm As String) As Boolean 
    Dim wb As Workbook 
    On Error Resume Next 
    Set wb = Workbooks(strWkbNm) 
    IsOpen = Err.Number = 0 
    On Error GoTo 0 
End Function 

---

这里是我会怎么写呢：

Sub AutoFinal2() 
    Dim final_wb As Workbook, shop_stat_wb As Workbook 
    Dim WorkbookFullName As String 

    WorkbookFullName = ThisWorkbook.Path & "\" & book2 
    Set final_wb = ThisWorkbook 
    Set shop_stat_wb = getWorkbook(WorkbookFullName) 

    If shop_stat_wb Is Nothing Then 
     MsgBox "File not found:" & vbCrLf & WorkbookFullName, vbCritical, "AutoFinal2 Cancelled" 
     Exit Sub 
    End If 
End Sub 

Function getWorkbook(WorkbookFullName As String) As Workbook 
    Dim wb As Workbook 
    For Each wb In Workbooks 
     If wb.FullName = WorkbookFullName Then Exit For 
    Next 

    If wb Is Nothing Then 
     If Len(Dir(WorkbookFullName)) > 0 Then 
      Set wb = Workbooks.Open(WorkbookFullName) 
     End If 
    End If 
    Set getWorkbook = wb 
End Function