Excel VBA检查是否打开了工作簿,如果没有打开,则将其打开 [英] Excel VBA checking if workbook is opened and opening it if not
本文介绍了Excel VBA检查是否打开了工作簿,如果没有打开,则将其打开的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
代码无法正常工作.尝试运行宏时出现错误400.您能否对此代码做一点评论?我不确定问题是否出在我要引用的函数变量上.
code I put below is not working properly. I am getting error 400 when try to run macro. Could you take a little review of this code? I am not sure if problem is not with function variable I am refering to.
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
推荐答案
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
这是我的写法:
Here is how I would write it:
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
这篇关于Excel VBA检查是否打开了工作簿,如果没有打开,则将其打开的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文