VBA ADODB- Select query using the excel sheet of the same workbook as Database

Question

I am novice in VBA so please don't mind if the question is of low level.I am trying to run a SQL query where the data has to be extracted from one of the sheets of the same workbook.

SQL = "Select ProductNumber from [sData$] where ProductSource = " & pSource & "

'pSource is a string that stores Product Source
'sdata is a sheet named as Data in the workbook

dataPath = ThisWorkbook.Fullname

'Not sure if this is the value I shall send as datapath in getData function

Set rst = getData(dataPath,SQL)
rst.Open

The getData function is defines as below

Public funtion getData(path as String, SQL as string) as ADODB.Recordset
Dim rs as ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("Provider= Microsoft.Jet.OLEDB.4.0;" & _
           "DataSource= " & path & ";"&_
            "Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;""")
rs.ActiveConnection =cn
rs.Source= SQL
Set getData =rs
End Function

Now after I get the numbers from Data sheet, I need to find the corresponding ProductCompany from Relation sheet. 9 is for Amul, 5 is for Nestle and so on.

Relation:

I am not sure how to do that. The numbers corresponds to their respective Product company in order.

Store query results into array, loop through array and then run a JOIN statements based off the data in the array. — Doug Coats
– Doug Coats, Commented Nov 26, 2016 at 11:36
Hi Doug Coats :) My query is not getting results. I dont know what is wrong in my code. Can you check my code and tell me what is wrong?Next Storing the resultset in array and looping through logic, can you please help with the code? I am not really clear on that. I get the logic but doing it in vba, as flipping between sheets is what getting me confused. — Naina
– Naina, Commented Nov 26, 2016 at 11:38
The sheet name is data, in vba instead of Sheet1, I have changed it to sData using vba properties. — Naina
– Naina, Commented Nov 26, 2016 at 12:46

omegastripes · Accepted Answer · 2016-11-29 15:13:42Z

Take a look at the below example showing how to create ADODB connection to this workbook, get ADODB recordset from SQL query, retrieve key - value pairs from relation sheet, create and populate a dictionary, and output the values from the recordset and the corresponding values from the dictionary:

Option Explicit

Sub Test()

    Dim oCn As Object
    Dim oRs As Object
    Dim aKeys
    Dim aItems
    Dim i As Long
    Dim oDict As Object
    Dim dProdNum

    ' create ADODB connection to this workbook
    Set oCn = CreateObject("ADODB.Connection")
    oCn.Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "DataSource='" & ThisWorkbook.FullName & "';" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;"";"
    ' get ADODB recordset from SQL query
    Set oRs = oCn.Execute("SELECT DISTINCT ProductNumber FROM [Data$] WHERE ProductSource = 'A1'")

    ' retrieve key - value pairs from relation sheet
    With ThisWorkbook.Sheets("Relation")
        aKeys = Split(.Range("B1"), ",")
        aItems = Split(.Range("B2"), ",")
    End With
    ' create and populate a dictionary
    Set oDict = CreateObject("Scripting.Dictionary")
    For i = 0 To UBound(aKeys)
        oDict(Trim(aKeys(i)) + 0) = Trim(aItems(i))
    Next

    ' output the values from the recordset and the corresponding values from the dictionary
    oRs.MoveFirst
    Do Until oRs.EOF
        dProdNum = oRs.Fields(0).Value
        Debug.Print dProdNum & " - " & oDict(dProdNum)
        oRs.MoveNext
    Loop

End Sub

The output for me is as follows:

4 - Britanica
5 - Nestle
9 - Amul

Note, connection string in the above code shown for .xls file. In case .xlsm you should use:

    oCn.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source='" & ThisWorkbook.FullName & "';" & _
        "Extended Properties=""Excel 12.0 Macro;HDR=Yes;FMT=Delimited;IMEX=1;"";"

Collectives™ on Stack Overflow

VBA ADODB- Select query using the excel sheet of the same workbook as Database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related