check for existing sheetname without opening workbook

[VicRauch]

I have an application that is extracting data from a large group of workbooks, cell by cell using a defination table in Access to define which cells to extract data from. I have run into a problem if a sheet has been renamed from the original name that it should still have. I have not found a way to check for the error. To get the data from each individually defined cell, I do not open the workbook, but just refer to the path\workbookname SheetName and then cell location. But I have not found a way to check if the SheetName is actually in the workbook before referencing it. Is there a way to check the sheet names without opening the workbook?

 

[TheChad5347]

Re: check for existing sheetname without opening workbook some extra ideas..I like the value=value trick

I just tried to use Tom's code and I got an error saying that the "external table is not in the expected format". I did put the correct file pathway into the macro. The Debug is taking me to "objConn.Open sConnString". I have the the following references turned on. Any suggestions?

Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft ADO Ext. 2.8 for DDl Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Option Explicit
Public mySheetname$
Public bln As Boolean
 
 
 
 
Function GetSheetsNames(WBName As String) As Collection
Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sConnString As String
Dim sSheet As String
Dim Col As New Collection
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WBName & ";" & _
"Extended Properties=Excel 8.0;"
Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn
For Each tbl In objCat.Tables
sSheet = tbl.Name
sSheet = Application.Substitute(sSheet, "'", "")
sSheet = Left(sSheet, InStr(1, sSheet, "$", 1) - 1)
On Error Resume Next
Col.Add sSheet, sSheet
On Error GoTo 0
Next tbl
Set GetSheetsNames = Col
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Function
 
Sub SheetExistsClosed()
mySheetname = InputBox("Enter sheet name to check for existence:", "Sheet name verification", "Sheet1")
If mySheetname = "" Then Exit Sub
 
bln = False
Dim Col As Collection, Book As String, i As Long
Book = "C:\Your\File\Path\YourFileName.xls"
Set Col = GetSheetsNames(Book)
 
For i = 1 To Col.count
If mySheetname = Col(i) Then
bln = True
Exit For
End If
Next i
 
If bln = True Then
'Sheet name does exist in that workbook of interest
MsgBox mySheetname & " exists in the subject workbook.", 64, "OK to proceed"
Else
'Sheet name does NOT exist in that workbook of interest
MsgBox mySheetname & " does NOT exist in the subject workbook.", 64, "Do not proceed"
End If
End Sub

Thanks in Advance,
Chad

 

[HarryS]

Re: check for existing sheetname without opening workbook some extra ideas..I like the value=value trick

to just test exists

Function SheetExistsFDB(ShtName$, WbPath$) As Boolean
       Dim GV, ParentFolder$, FileName$, PD%
      'Split to folder and file name
      
       PD = InStrRev(WbPath, "\")
       ParentFolder = Left(WbPath, PD - 1)
       FileName = Mid(WbPath, PD + 1)
       
       
        ' also can be used to  get the value RV from a specified Row Col if you need it
        GV = ExecuteExcel4Macro("'" & ParentFolder & "\[" & FileName & "]" & ShtName & "'!R1C1")
        SheetExistsFDB = CStr(GV) <> "Error 2023"
       ' MsgBox CStr(GV)
End Function



[\code]