检查工作表是否存在于外部封闭工作簿中 [英] Checking If A Sheet Exists In An External Closed Workbook

问题描述

我要测试当前工作簿中的某些工作表是否在另一个已关闭的工作簿中，并返回一条消息，指出哪些工作表导致错误。

I want to test whether certain sheets in the current workbook exist in another closed workbook and return a message saying which sheet/s are causing errors.

我不想打开/关闭工作簿，所以我试图在随机单元格中更改公式以链接到文件路径（fp）的工作簿，以测试是否工作表存在。

I prefer not to open/close the workbook so I'm trying to change the formula in a random cell to link to the workbook of filepath (fp) to test whether the sheet exists.

我已经用另一个工作表测试了一个虚拟工作表，我知道它在其他工作簿中不存在，并且可以工作，但是当我有多个工作表导致错误时，我获取应用程序定义或对象定义的错误。在第二次迭代中，我相信错误处理的编写方式会导致崩溃，但我并不完全了解其工作原理。

I've tested this with a dummy sheet that I know doesn't exist in the other workbook and it works but when I have more than one sheet that causes errors I get an "Application-defined or object-defined error". On the second iteration I believe the way the error handling is written causes the crash but I don't exactly understand how that works.

我得到的代码是：

Sub SheetTest(ByVal fp As String)
Dim i, errcount As Integer
Dim errshts As String

For i = 2 To Sheets.Count
    On Error GoTo NoSheet
        Sheets(1).Range("A50").Formula = "='" & fp & Sheets(i).Name & "'!A1"
    GoTo NoError
NoSheet:
errshts = errshts & "'" & Sheets(i).Name & "', "
errcount = errcount + 1
NoError:
Next i

Sheets(1).Range("A50").ClearContents

If Not errshts = "" Then
    If errcount = 1 Then
        MsgBox "Sheet " & Left(errshts, Len(errshts) - 2) & " does not exist in the Output file. Please check the sheet name or select another Output file."
    Else
        MsgBox "Sheets " & Left(errshts, Len(errshts) - 2) & " do not exist in the Output file. Please check each sheet's name or select another Output file."
    End If
    End
End If

End Sub

希望你们能在这里帮助我，谢谢！

Hopefully you guys can help me out here, thanks!

推荐答案

这是一种略有不同的方法：

Here's a slightly different approach:

Sub Tester()

    Dim s As Worksheet

    For Each s In ThisWorkbook.Worksheets

        Debug.Print s.Name, HasSheet("C:\Users\blah\Desktop\", "temp.xlsm", s.Name)

    Next s


End Sub



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

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