FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
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.
VBA Code:
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
VBA Code:
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