CODEWITHSUNDEEP
vbaexcelerror-handling
superzipp
superzipp

Reputation: 33

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.

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.

The code I've got is:

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!

Upvotes: 3

Views: 3789

Answers (3)

Anonymous
Anonymous

Reputation: 1

Just an update for Tim's Function for error Handling:

VBA:

Function HasSheet(fPath As String, fName As String, sheetName As String)
On Error Resume Next
Dim f As String

f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"

HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))
If Err.Number <> 0 Then
    HasSheet = False
End If
On Error GoTo 0 
End Function

Upvotes: 0

axel
axel

Reputation: 1

Sub Tester()

MsgBox (Not IsError(Application.ExecuteExcel4Macro("'C:\temp[temp.xlsm]Sheetxyz'!R1C1")))

End Sub

Upvotes: -2

Tim Williams
Tim Williams

Reputation: 166825

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

Upvotes: 7

Related Questions