Connecting to Excel DataTables

Hi am currently wrtting a vba subroutine that i want to use in order to query a datatable in a worksheet, and then load the recordset into a different datatable in my current worksheet.

With my current connection string i am succesfull when i use the query "SELECT * FROM [worksheet_name$] WHERE [AktivId] = 'aktivid'

But this does not let me select specific columns from the worksheet, which i want.

Optimally i would be able to query the datatable specifically, and select specific columns from it where the condition is met.

I have also tried to specify the table worksheetname aswell as the table name, but this did not work either.

I cant seem to find any documentation on specifically querying excel datatables using ADO and VBA. Any Suggestions? Thanks in advance :)

My subroutine, and test subroutine
Sub test_behaktiv()

get_behaktiv "DK0010274414"

End Sub
'Henter alle kundegruppers beholdning i en given aktiv ud fra aktiv id
Sub get_behaktiv(aktivid As String)

'definer tabel som fyldes
Dim behaktiv_tbl As ListObject
Set behaktiv_tbl = ThisWorkbook.Worksheets("AktivBeholdninger").ListObjects("behaktiv")
 
'definer datatabel som vi vil query
Dim datatable As ListObject
Set datatable = ThisWorkbook.Worksheets("BeholdningData").ListObjects("aktivbeh_Data")
 
' dan query som filtrerer datatable på aktivid
Dim query As String
query = "SELECT [Navn], [Nominelt], [Kurs], [Valutakode], [Valutakurs], [Eksponering]" & _
        " FROM [" & datatable & "]" & _
        " WHERE [AktivId] = '" & aktivid & "'"
MsgBox query

'definer connection string
Dim connStr As String
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
          
' åben forbindelse til datatable
Dim conn As New ADODB.Connection
conn.Open connStr

' Create recordset and execute query
Dim rs As New ADODB.Recordset
rs.Open query, conn, 1, 3

' ryd beh aktiv tabel
behaktiv_tbl.DataBodyRange.ClearContents

' indsæt recordset
If Not rs.EOF Then
    behaktiv_tbl.DataBodyRange.Cells(1, 1).CopyFromRecordset rs
Else
    MsgBox " Fandt ingen kundegrupper med dette aktiv i beholdning."
End If

' luk forbindelsen
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

0 answers