We have a BO database where we download a query of workbook information. The query in BO is setup with the name of the workbook and the name of the two worksheets. Since this will be used by many people and I can't ensure they won't change the worksheet names, I would like to check for those two sheets even with the workbook closed. I found this which will tell me if the two sheets exist, but it displays it in the Debug window. Instead, how can it tell me if one of those two sheets is missing which could simply mean it's spelled wrong.
Sub Tester() Debug.Print "Data Log", HasSheet(ActiveWorkbook.Path & Application.PathSeparator, "Data.xlsx", "Data Log") Debug.Print "Report 1", HasSheet(ActiveWorkbook.Path & Application.PathSeparator, "Data.xlsx", "Report 1") 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