检查关闭的工作簿中是否存在工作表

Question

我正在使用"检查外部关闭工作簿中是否存在工作表"中的代码

Function HasSheet(fPath As String, fName As String, sheetName As String)
On Error Resume Next
Dim f As String
f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"
Debug.Print f
HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))
If Err.Number <> 0 Then
HasSheet = False
End If
Debug.Print Err.Number
On Error GoTo 0
End Function

由传递给函数的变量构建的变量f如下所示：

C:UsersMyNameMyFolder[MyFile.xlsx]MySheet'!R1C1

当我将包含我要检查的工作表的文件名传递给函数时，结果总是一样的：

HasSheet=错误/错误。编号=0

Answer 1

组合最佳答案：

打开Excel文件以在不显示的情况下使用VBA进行读取
检查工作表是否存在

sub SheetExistsInClosedWorkbook()
Dim app as New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(fileName)
Dim SheetExists As Boolean
SheetExists= WorksheetExists("somesheet", "someWb" )
book.Close SaveChanges:=False
app.Quit
Set app = Nothing
if SheetExists then
msgbox "somesheet" & " exists in " & "someWb"
else
msgbox "somesheet" & " does not Exist in " & "someWb"  
End if
End sub
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
WorksheetExists = Not sht Is Nothing
End Function

Answer 2

我使用了原始代码，并对上面提到的语法进行了更正：

Function HasSheet(fPath As String, fName As String, sheetName As String)
Dim f As String
f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"
HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))
End Function

谢谢你的帮助！

Answer 3

的另一种方法

静噪放大器：检查表是否存在

Sub vba_check_sheet()
Dim wb As Workbook
Dim sht As Worksheet
Dim shtName As String
shtName = InputBox(Prompt:="Enter the sheet name", _
Title:="Search Sheet")
Application.ScreenUpdating = False
Set wb = Workbooks.Open _
("C:UsersDellDesktopsample-file.xlsx")
For Each sht In wb.Worksheets
If sht.Name = shtName Then
wb.Close SaveChanges:=True
MsgBox "Yes! " & shtName & " is there in the workbook." _
, vbInformation, "Found"
Exit Sub
End If
Next sht
Application.ScreenUpdating = true
MsgBox "No! " & shtName _
& " is not there in the workbook.", _
vbCritical, "Not Found"
End Sub