How to check if a worksheet exists in an excel file

Question

I am receiving an excel(Book1.xls) file from the user and sometimes one of the worksheets name is XYZ and sometimes its name is XYZ1.

I am doing

 Dim Conn,Rs   
 Set Conn = CreateObject("ADODB.Connection")
 Conn.ConnectionTimeout = 10
 Conn.CommandTimeout = 300
 Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";"
 Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")

and when the worksheets name is XYZ1, the last line in the above code is blowing up with an error message: The Microsoft Access database engine could not find the object 'XYZ$CB1:B1'. Make sure the object exists and that you spell its name and the path name correctly. If 'XYZ$CB1:B1' is not a local object, check your network connection or contact the server administrator.

Instead of the last line I would like to do something like

sheetNameString = Conn.Execute("SELECT Join(SheetName) from Book1.xls")

If InStr(sheetNameString,"XYZ1") Then
     Set RS = Conn.Execute("SELECT * FROM [XYZ1$B1:B1]")
Else
     Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")
End If

Related: [Delete worksheet if it exists and create a new one](https://stackoverflow.com/q/40206232/3357935) — Stevoisiak, Sep 05 '18 at 14:59

score 2 · Answer 1 · answered Sep 25 '13 at 20:59

2

Adding the following code after the conn.open worked for me:

Dim Cat, Tbl, Tabs
Cat = CreateObject("ADOX.Catalog")
Cat.ActiveConnection = Conn
Tabs = Cat.Tables
For Each Tbl In Tabs
  'Tbl.Name holds the name
Next

answered Sep 25 '13 at 20:59

Steve

5,585
2
18
32

Sorry, took me a while to research, write and test so I was sure it worked. Didn't see the other answer. – Steve Sep 25 '13 at 21:12
Your answer was also helpful. No problem Thanks for the help. – nikhil koganti Sep 25 '13 at 21:25
1

@Steve - +1 because it is *tested* code (and not air code like mine). – Ekkehard.Horner Sep 25 '13 at 21:30
Where do you define the worksheet name in this code? – Stevoisiak Sep 05 '18 at 14:15

Ekkehard.Horner · Answer 2 · 2013-09-25T21:07:28.950

You can use the "ADOX.Catalog" to get a list of the tables in a database:

Public Function getTableNames(oConn)
  Dim aRVal    : aRVal          = Array()
  Dim oCatalog : Set oCatalog   = CreateObject( "ADOX.Catalog" )
  Set oCatalog.ActiveConnection = oConn
  Dim oTable
  For Each oTable In oCatalog.Tables
      ReDim Preserve aRVal( UBound( aRVal ) + 1 )
      aRVal( UBound( aRVal ) ) = oTable.Name
  Next
  getTableNames = aRVal
End Function

If "XYZ1$" = getTableNames(Conn)(0) Then
     Set RS = Conn.Execute("SELECT * FROM [XYZ1$B1:B1]")
Else
     Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")
End If

(not tested, assumes just one sheet/table, needs improvements (e.g. ReDim aRVal(oCatalog.Tables.Count - 1) instead of ReDim Preserve), not sure whether the trailing $ will be kept)

Another way:

Public Function getTableNames(oConn)
  Const adSchemaTables = 20
  Dim oRS   : Set oRS = oConn.OpenSchema(adSchemaTables)
  Dim aRVal : aRVal   = Array()
  Do Until oRS.EOF
     ReDim Preserve aRVal(UBound(aRVal) + 1)
     aRVal(UBound(aRVal)) = oRS.Fields("TABLE_NAME").Value
     oRS.MoveNext
  Loop
  getTableNames = aRVal
End Function

How to check if a worksheet exists in an excel file

2 Answers2