Check closed Workbook to see if sheets exists

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. 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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub Tester()
  Dim sPath As String, sFile As String, sName As String
  sPath = ActiveWorkbook.Path & "\"
  sFile = "Data.xlsx"

  sName = "Data Log"
  If Not HasSheet(sPath, sFile, sName) Then
    MsgBox "The '" & sName & "' sheet does not exist"
  End If
  sName = "Report 1"
  If Not HasSheet(sPath, sFile, sName) Then
    MsgBox "The '" & sName & "' sheet does not exist"
  End If
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,347
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top