6

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

enter image description here 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.

enter image description here

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

Community
  • 1
  • 1
sagar
  • 397
  • 10
  • 25

1 Answers1

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]
  • Hi, why not use `tbl.Range.Address(False, False, xlA1, xlExternal)` ? this will get you the worksheet's name as well – Shai Rado Oct 31 '17 at 08:09
  • The bracketing is wrong and it returns the workbooks name as well. –  Oct 31 '17 at 08:30
  • @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. –  Oct 31 '17 at 08:54