Excel ListObject (Format as Table) in SQL-CodePudding

You can use SQL in Excel VBA to query worksheets like this:

Dim relConn As ADODB.Connection
Dim rs As ADODB.Recordset
Set relConn = CreateObject("ADODB.Connection")
relConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT [MyWorksheet$].MyFirstLineHeader FROM [MyWorksheet$]", relConn
While (Not rs.EOF)
  ...
Wend

→ Worksheets are referred to as [WorksheetName$] in SQL.

In SQL, how can I refer to a ListObject which is created by selecting a range and choosing Format as Table from the Home ribbon? In VBA they are accessed like this: ThisWorkbook.Sheets("MyWorksheet").ListObjects("MyTableName").

CodePudding user response：

Try

Dim s as string
s = ThisWorkbook.Sheets("MyWorksheet").ListObjects("MyTableName").Range.Address(0, 0)
rs.Open "SELECT [MyFirstLineHeader] FROM [MyWorkSheet$" & s & "]", relConn