Checking for a worksheet in an external workbook

[prospectator]

This seems like a simple task, but can't figure it out. I want to validate whether a worksheet exists in an external unopened workbook. The active worksheet has the external workbook name and sheet name within. So I know where the file is (the file always exists), but the worksheet may or may not exist. If it doesn't exist, my macro will just skip the current sheet. If the worksheet does exist it will put a formula in select cells to retrieve data from that workbook.

My thought was simple, on worksheet activate place a match() formula (or any other formula referencing an external range) within a cell and pass the lookup array (which can be built based on the information within the worksheet). If it's an error, just move on, if not insert the appropriate formulas.

I can successfully pass the match() to a cell with a valid path. With error handling in VBA I can get past the error returned by the match() with a bad lookup array. The one thing I cant fix, is if the lookup array isn't valid each time I activate the sheet I get a pop up window asking me to select the appropriate worksheet, knowing the worksheet doesn't exist.

Sorry if I didn't explain that very well.

 

[NdNoviceHlp]

Here's some VBA to check if a sheet exists in a closed wb. HTH. Dave

Dim FSO As Object, FilDir As Object, sht As Worksheet
Set FSO = CreateObject("scripting.filesystemobject")
Set FilDir = FSO.GetFile(ThisWorkbook.Path & "YourFileName.xlsm")
Workbooks.Open FileName:=FilDir
For Each sht In FilDir.Sheets
If sht.Name = "YourSheetName" Then
MsgBox "Sheet Exists"
Exit For
End If
Next sht
Workbooks(FilDir.Name).Close SaveChanges:=False
End If
Set FilDir = Nothing
Set FSO = Nothing

ps. Welcome to the Board!

 

[prospectator]

Thanks for the reply! I"ll give that a shot. Is there any way to keep from having to open the workbook? Due to their size it's takes them a while to open on our computer network. Thanks again!

 

[NdNoviceHlp]

Well that code didn't exactly work as I thought it would. You won't ever see the closed wb with this code. This function should work for you. Dave

Function SheetExists(ShtName As String, FileNm As String) As Boolean
Dim FilDir As Object, sht As Worksheet
'ie. If SheetExists("Sheet1", ThisWorkbook.Path & "\" & "test.xlsm") Then
On Error GoTo ErFix
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set FilDir = Workbooks.Open(FileNm)
For Each sht In Workbooks(FilDir.Name).Worksheets
If sht.Name = ShtName Then
SheetExists = True
Exit For
End If
Next sht
Workbooks(FilDir.Name).Close SaveChanges:=False
ErFix:
Set FilDir = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If Err.Number <> 0 Then
MsgBox "Error"
End If
End Function

To operate...

If SheetExists("Sheet1", ThisWorkbook.Path & "\" & "test.xlsm") Then
MsgBox "Sheet Exists"
'code whatever
End If

Note that the full file path and file extension is required

 

[Peter_SSs]

Not sure if there is a better way, but I think this should do what you want. I have assumed full path and file name is in A1 of the active sheet and sheet name in B1.

Sub TestIfSheetExists()
  Dim sFilename As String, sSheeName As String
  Dim vTest As Variant
  
  sFilename = Range("A1").Value
  sSheeName = Range("B1").Value
  On Error GoTo Failed:
  vTest = ExecuteExcel4Macro("'" & StrReverse(Replace(StrReverse(sFilename), "\", "[\", 1, 1)) & "]" & sSheeName & "'!R1C1")
  MsgBox "Sheet exists"
  Exit Sub
Failed:
  MsgBox "Sheets does not exist"
End Sub

 

[Peter_SSs]

You won't ever see the closed wb with this code.

But since it still actually opens the other workbook it won't resolve this issue:

Is there any way to keep from having to open the workbook? Due to their size it's takes them a while to open on our computer network.

 

[DanteAmor]

Check if the following works for you.

Change the 12 for the version number, check your version here:

Microsoft Excel - Wikipedia

en.wikipedia.org

Change the folder, the name of the book and the name of the sheet.

Sub test_check_sheet()
  Dim wFile As String, wSheet As String
  Dim conexion As Object, datos As Variant
 
  wFile = "D:\books\test.xlsx"            'folderName and bookName
  wSheet = "sheet1"                           'sheetName
 
  Set conexion = CreateObject("adodb.connection")
  Set datos = CreateObject("adodb.recordset")
  conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & wFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
 
  On Error Resume Next
  Set datos = conexion.Execute("SELECT * FROM [" & wSheet & "$]")
  If Err.Number = 0 Then
    MsgBox "sheet exists"
  Else
    MsgBox "The sheet does not exists " & Err.Description
  End If
End Sub

 

[prospectator]

Not sure if there is a better way, but I think this should do what you want. I have assumed full path and file name is in A1 of the active sheet and sheet name in B1.

Sub TestIfSheetExists()
  Dim sFilename As String, sSheeName As String
  Dim vTest As Variant
 
  sFilename = Range("A1").Value
  sSheeName = Range("B1").Value
  On Error GoTo Failed:
  vTest = ExecuteExcel4Macro("'" & StrReverse(Replace(StrReverse(sFilename), "\", "[\", 1, 1)) & "]" & sSheeName & "'!R1C1")
  MsgBox "Sheet exists"
  Exit Sub
Failed:
  MsgBox "Sheets does not exist"
End Sub

This worked perfectly! This is exactly what I needed.

Thanks to everyone who replied to help resolve my issue. It's greatly appreciated.

 

[prospectator]

Not sure if there is a better way, but I think this should do what you want. I have assumed full path and file name is in A1 of the active sheet and sheet name in B1.

Sub TestIfSheetExists()
  Dim sFilename As String, sSheeName As String
  Dim vTest As Variant
 
  sFilename = Range("A1").Value
  sSheeName = Range("B1").Value
  On Error GoTo Failed:
  vTest = ExecuteExcel4Macro("'" & StrReverse(Replace(StrReverse(sFilename), "\", "[\", 1, 1)) & "]" & sSheeName & "'!R1C1")
  MsgBox "Sheet exists"
  Exit Sub
Failed:
  MsgBox "Sheets does not exist"
End Sub

After I tested this a little bit it was working great. Unfortunately I had the workbook (whose worksheet I was checking for) open. When I closed the workbook it always returned "Sheet exists". Although vtest was 0 if the sheet existed and it was Error 2023 if it didn't, it would never jump to the "Failed" label. I changed it slightly and it seems to work if the workbook is closed or open. Even though the solution below seems to work, I don't know why I had to go this route, It would seem if vtest was returning Error 2023 it should have worked as designed. So below if the worksheet is open, it won't jump to Failed so I have to perform an if then to check for the error. If the file is closed, it will jump directly to the failed label if it doesn't exist. Does this make sense?

Sub TestIfSheetExists()
Dim sFilename As String, sSheeName As String
Dim vTest As Variant


sFilename = Range("C4").Value
sSheeName = Range("B4").Value


On Error GoTo Failed:
vTest = ExecuteExcel4Macro("'" & StrReverse(Replace(StrReverse(sFilename), "\", "[\", 1, 1)) & "]" & sSheeName & "'!R1C1")

If IsError(vTest) Then
MsgBox "Sheets does not exist"
Exit Sub
Else
MsgBox "Sheets exists"
Exit Sub
End If

Failed:
MsgBox "Sheets does not exist"

End Sub

 

[Peter_SSs]

I'm not sure why you got that behaviour but I guess if you have something that is now working, that is the main thing.