Visual Basic，检查另一个工作簿中是否存在一个工作表 [英] Visual Basic, Check if a sheet exists in another workbook

问题描述

我真的很喜欢Visual Basic，我也不知道任何python，我正在尝试编写能够检查工作簿中是否存在工作表的代码...

  Sub sheetexist（）
如果Len（Dir（（C：\My Data\Performance Spreadsheets\ [ABCD  -  Performance.xls ] 6月14日）））然后
 MsgBoxSheet exists
 Else 
 MsgBoxSheet不存在
 End If 
 End Sub 
  

ABCD确实有6月14日的工作表，但代码只返回Sheet不存在，是否有另一种方法来检查对于其他工作簿中的工作表？

解决方案

我认为你错误地使用了目录函数。

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

 功能SheetExists（wbPath as String，shName as String）
 Dim wb as Workbook 
 Dim val 
 
'假设工作簿未打开
设置wb = Workbooks.Open（wbPath）
在错误恢复下一步
 val = wb.Worksheets（shName）.Range A1）。值
 SheetExists =（Err = 0）
 
'关闭工作簿
 wb.Close 
 
结束函数
  

从工作表单元格调用这样的功能：

  = SheetExists（C：\My Data\Performance Spreadsheets\ABCD  -  Performance.xls，Jun 14）
  pre> 
 
 
或从VBA开始，如：
  Debug.Print SheetExists C：\My Data\Performance Spreadsheets\ABCD  -  Performance.xls，Jun 14）
  
不打开工作簿，您可以使用代码这里。
 
 
 
这将引发错误如果公式的任何部分无法评估（例如，如果您传递不存在的表单的名称，错误的文件路径等，则错误2023 ：
 私有函数GetInfoFromClosedFile（ByVal wbPath As String，_ 
 wbName As String，wsName As String，cellRef As String）As Variant 
 Dim arg As String 
 GetInfoFromClosedFile =
如果Right（wbPath，1）<> \然后wbPath = wbPath& \
如果Dir（wbPath&\& wbName）=Then Exit Function 
 arg ='& wbPath& [& wbName& ]& _ 
 wsName& ！ &安培; Range（cellRef）.Address（True，True，xlR1C1）
 On Error Resume Next 
 GetInfoFromClosedFile = ExecuteExcel4Macro（arg）
结束函数
  pre> 
 
 
调用它：
  Sub Test（）
 Dim path As String 
 Dim filename As String 
 Dim sheetName As String 
 Dim cellAddress As String 
 
 path =c：\users\you\桌面
 
 filename =file.xlsx
 
 sheetName =6月14日
 
 cellAddress =A1
 
 Dim v As Variant'必须是可变的，因此它可以包含错误值
 
v = GetInfoFromClosedFile（path，filename，sheetName，cellAddress）
如果IsError（v）Then MsgBoxSheet或文件名不存在！ 
 
 
 End Sub 
  
 
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...
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?
解决方案
I think you're mis-using the Dir function.

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:
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: 
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


                        
这篇关于Visual Basic，检查另一个工作簿中是否存在一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！