检查另一个工作簿中是否存在工作表

Question

我对Visual Basic真的很陌生，我也不知道任何python，我正试图编写能够检查工作簿中是否存在工作表的代码…

Sub sheetexist()
    If Len(Dir(("C:My DataPerformance Spreadsheets[ABCD - Performance.xls]Jun 14"))) Then
        MsgBox "Sheet exist"
    Else
        MsgBox "Sheet does not exist"
    End If
End Sub

ABCD确实有6月14日的工作表，但是代码只返回"工作表不存在"，是否有其他方法检查其他工作簿中的工作表?

Answer 1

我想你误用了Dir函数。

检查工作表是否存在的最简单方法是使用错误处理。

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

在工作表单元格中像这样调用函数:

=SheetExists("C:My DataPerformance SpreadsheetsABCD - Performance.xls", "Jun 14")

或从VBA中:

Debug.Print SheetExists("C:My DataPerformance SpreadsheetsABCD - Performance.xls", "Jun 14")

在不打开工作簿的情况下，您可以使用这里的代码。

如果公式的任何部分不能求值(例如，如果你传递了一个不存在的工作表的名称，一个错误的文件路径等)，这将引发一个错误，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

称之为:

Sub Test()
Dim path As String
Dim filename As String
Dim sheetName As String
Dim cellAddress As String
path = "c:usersyoudesktop"
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