Excel VBA write a SQL query off of data from an excel table

Question

I have a table in a worksheet in MS Excel, and I am trying to write a query off table1 and paste the results in another worksheet. (there are other details in the table that make it possible to have 2 rows with the same supplier and product).

Table1:
Product     Supplier   
apple       A           
apple       A           
apple       B           
apple       C           
banana      A

I want to do a query like this "SELECT Supplier FROM Table1 WHERE Product = 'apple' GROUP BY Supplier". At first I just tried to write the query with the table names and column names but that didn't work. So I tried to use ranges instead of the table names and column names.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim tblRng As Range
Dim clmRng1 As Range
Dim clmRng2 As Range

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

tblRng = Sheets("sheet1").ListObjects("table1").DataBodyRange
clmRng1 = Sheets("sheet1").ListObjects("table1").ListColumns("Supplier").DataBodyRange
clmRng2 = Sheets("sheet1").ListObjects("table1").ListColumns("Product").DataBodyRange

strSQL = "SELECT " & clmRng1 & " FROM " & tblRng & " WHERE " & clmRng2 & " = 'apple' GROUP BY " & clmRng1
rs.Open strSQL, cn

Debug.Print rs.GetString

I am getting an error "Object variable or With block variable not set". My main concern is that I feel like this is a very indirect way to do this and I was wondering if there was an easier way to accomplish this task?

You're missing Set in front of your range objects: Set tblRng = ..., Set clmRng1 = ..., Set clmRng2 = ...... but then strSQL = "SELECT " & clmRng1 & " FROM " & tblRng & " WHERE " & clmRng2 & " = 'apple' GROUP BY " & clmRng1 is going to be quite problematic. — BigBen
– BigBen, Commented Apr 22, 2020 at 0:24
Yeah that’s what I was thinking when I wrote that. Any alternative approach come to mind by any chance? — J. Doe
– J. Doe, Commented Apr 22, 2020 at 2:42

Dy.Lee · Accepted Answer · 2020-04-22 22:32:11Z

Try,

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim objList As ListObject
'    Dim tblRng As Range
'    Dim clmRng1 As Range
'    Dim clmRng2 As Range
    Dim s As String

    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

'    tblRng = Sheets("sheet1").ListObjects("table1").DataBodyRange
'    clmRng1 = Sheets("sheet1").ListObjects("table1").ListColumns("Supplier").DataBodyRange
'    clmRng2 = Sheets("sheet1").ListObjects("table1").ListColumns("Product").DataBodyRange
    Set objList = Sheets(1).ListObjects("table1")
    s = objList.Range.Address(0, 0)

    'strSQL = "SELECT " & clmRng1 & " FROM " & tblRng & " WHERE " & clmRng2 & " = 'apple' GROUP BY " & clmRng1
    strSQL = "SELECT supplier FROM [Sheet1$" & s & "]  WHERE  Product = 'apple' GROUP BY supplier "

    rs.Open strSQL, cn

Collectives™ on Stack Overflow

Excel VBA write a SQL query off of data from an excel table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related