Run Excel SQL on Existing Sheet - Invalid Sheet/Table

Question

Goal: Run SQL against data in an existing Excel worksheet.

I'm running the following code on an existing Excel worksheet. All ADO connections are working but when I run the SQL statement, it tells me that my table is invalid. Should I be passing the name of the worksheet OR the name of the table? I have tried both. Nothing works.

It errors when opening the recordset ("rs.Open strSQL, cn")

When I use the name of the worksheet in the SQL, I receive the following: 'AG1' is not a valid name. make sure that it does not include invalid characters or punctuation and that is is not too long.

When I use the name of the table, I receive the following: The Microsoft Access database engine could not find the object 'Table4'. Make sure the object exists and that you spell it's name and the path name correctly. If 'Table4' is not a local object, check your network connection or contact the server administrator.

Thank you in advance.

Sub testSQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strCon As String

' Declare variables
Dim strFile: strFile = ThisWorkbook.FullName

' construct connection string
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

' create connection and recordset objects
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' open connection
cn.Open strCon

' construct SQL query
' "AG1" is the name of the sheet
' I've tried "Table4" (name of table) without luck
strSQL = "SELECT * FROM [AG1$] where [Language] = 'Spanish';"

' execute SQL query
rs.Open strSQL, cn

' close connection
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

question from:https://stackoverflow.com/questions/65851846/run-excel-sql-on-existing-sheet-invalid-sheet-table

Answer 1

Waitting for answers