CODEWITHSUNDEEP
excelvba
J. Doe
J. Doe

Reputation: 199

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

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?

Upvotes: 0

Views: 269

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 1

Related Questions