3

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!

0m3r
  • 12,286
  • 15
  • 35
  • 71
superzipp
  • 33
  • 1
  • 5
  • Interesting to see an example of you call this sub - what do you pass to the fp String? I was fairly sure that you couldn't access WorkSheet values if the Workbook was not open – dbmitch Jun 15 '16 at 00:32
  • The fp string contains the path of the external workbook with the [ ] brackets as you'd have in any typical linked cell. – superzipp Jun 15 '16 at 00:56

3 Answers3

7

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    Thanks so much for the speedy reply Tim!! This is indeed much more elegant and simple than what I had. I adapted the IsError(Application.ExecuteExcel4Macro(f)) line in place of the error checker and it works perfectly. – superzipp Jun 15 '16 at 01:17
  • FWIW Excel 4 macros are now disabled by default. – chris neilsen Jul 10 '22 at 23:06
0

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
Anonymous
  • 1
  • 1
-2

Sub Tester()

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

End Sub

axel
  • 1