vba Visual Basic，检查工作表是否存在于另一个工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24916234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Visual Basic, Check if a sheet exists in another workbook
提问by packy
I'm really new to Visual Basic and I don't know any python either, I'm trying to write code that is able to check if a worksheet exists in a workbook...
我真的是 Visual Basic 的新手，我也不知道任何 python，我正在尝试编写能够检查工作簿中是否存在工作表的代码......
Sub sheetexist()
If Len(Dir(("C:\My Data\Performance Spreadsheets\[ABCD - Performance.xls]Jun 14"))) Then
MsgBox "Sheet exist"
Else
MsgBox "Sheet does not exist"
End If
End Sub
ABCD does have the sheet for Jun 14 however the code only returns "Sheet does not exist", is there another way to check for worksheets in other workbooks?
ABCD 确实有 6 月 14 日的工作表，但是代码只返回“工作表不存在”，还有其他方法可以检查其他工作簿中的工作表吗？
回答by David Zemens
I think you're mis-using the Dirfunction.
我认为您误用了该Dir功能。
The easiest way to check if a sheet exists is with error-handling.
检查工作表是否存在的最简单方法是错误处理。
Function SheetExists(wbPath as String, shName as String)
Dim wb as Workbook
Dim val
'Assumes the workbook is NOT open
Set wb = Workbooks.Open(wbPath)
On Error Resume Next
val = wb.Worksheets(shName).Range("A1").Value
SheetExists = (Err = 0)
'Close the workbook
wb.Close
End Function
Call the function like this from a worksheet cell:
从工作表单元格调用这样的函数：
=SheetExists("C:\My Data\Performance Spreadsheets\ABCD - Performance.xls", "Jun 14")
Or from VBA like:
或者从 VBA 像：
Debug.Print SheetExists("C:\My Data\Performance Spreadsheets\ABCD - Performance.xls", "Jun 14")
Without opening the workbook, you could use the code here.
无需打开工作簿，您可以在此处使用代码。
This will raise an error if any part of the formula can't evaluate (e.g., if you pass the name of a non-existent sheet, a bad file path, etc., Error 2023:
如果公式的任何部分无法计算，这将引发错误（例如，如果您传递不存在的工作表的名称、错误的文件路径等，Error 2023：
Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
Call it:
称它为：
Sub Test()
Dim path As String
Dim filename As String
Dim sheetName As String
Dim cellAddress As String
path = "c:\users\you\desktop"
filename = "file.xlsx"
sheetName = "Jun 14"
cellAddress = "A1"
Dim v As Variant 'MUST BE VARIANT SO IT CAN CONTAIN AN ERROR VALUE
v = GetInfoFromClosedFile(path, filename, sheetName, cellAddress)
If IsError(v) Then MsgBox "Sheet or filename doesn't exist!"
End Sub
