Use Table name in SQL query in VBA Excel

Question

Below is the excel table i want to manipulate via SQL query in VBA.

Please find my VBA code.

Sub SQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT [Sr] FROM [Table1$] WHERE [Sr] >= 3 AND [Sr] <= 8;"

rs.Open strSQL, cn

Sheet5.Range("D1").CopyFromRecordset rs

End Sub

I am getting below error for my above code.

Please guide how can i manipulate excel table in SQL query in VBA.

@Dy.Lee Plase how can i use directly Table name (ListObject's name) in the query.. — sagar
– sagar, Commented Oct 31, 2017 at 7:30
Following works strSQL = "SELECT [Sr] FROM [Sheet1$A1:B15] WHERE [Sr] >= 3 AND [Sr] <= 8" So issue is near Table1 portion. — shrivallabha.redij
– shrivallabha.redij, Commented Oct 31, 2017 at 7:46
Okay, but what "works" isn't what was asked. Tables don't require you to know the range as they adjust automatically - so this isn't really workable where named tables are used, unless every single query gets updated every time any table adds or removes rows or columns. This is likely to be unacceptable. — RBerman
– RBerman, Commented Mar 19, 2024 at 20:51

score 5 · Accepted Answer · 2017-10-31 08:52:31Z

5

Querying the ListObject's range using a table alias will work.

SQL

SELECT [Sr] FROM [Sheet1$A1:D15] AS [Table1] WHERE [Sr] >= 3 AND [Sr] <= 8;

Code

Sub SQL()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
             & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    strSQL = "SELECT [Sr] FROM " & getListObjectSQLAdress(Sheet1.ListObjects(1)) & " WHERE [Sr] >= 3 AND [Sr] <= 8;"

    rs.Open strSQL, cn

    Sheet5.Range("D1").CopyFromRecordset rs

End Sub

Function getListObjectSQLAdress(tbl As ListObject) As String
    getListObjectSQLAdress = "[" & tbl.Parent.Name & "$" & tbl.Range.Address(False, False) & "] AS [" & tbl.Name & "]"
End Function

Alternative method to build a valid SQL Query Table name from a ListObject.Range

Function getListObjectSQLAdress2(tbl As ListObject) As String
    Dim s As String
    s = tbl.Range.Address(False, False, xlA1, xlExternal)
    s = Replace(s, "'[", "`")
    s = Replace(s, "]", "`.[")
    s = Replace(s, "'!", "$")
    getListObjectSQLAdress2 = s & "] AS [" & tbl.Name & "]"
End Function

Table Name Output

`Untitled (4).xlsx`.[Sheet1$A1:D15] AS [Table1]

edited Oct 31, 2017 at 8:52

answered Oct 31, 2017 at 7:40

user6432984

Sign up to request clarification or add additional context in comments.

3 Comments

Shai Rado Over a year ago

Hi, why not use tbl.Range.Address(False, False, xlA1, xlExternal) ? this will get you the worksheet's name as well

user6432984 Over a year ago

The bracketing is wrong and it returns the workbooks name as well.

user6432984 Over a year ago

@ShaiRado I just appended an alternate function that uses your method. This method will come in handy if you need to query multiple workbooks in a single statement.

Collectives™ on Stack Overflow

Use Table name in SQL query in VBA Excel

1 Answer 1

SQL

Code

Table Name Output

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SQL

Code

Table Name Output

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related