我在下面放置的代码无法正常工作。尝试运行宏时出现错误 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
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)