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

1 answer

RsA.Find Criteria:="Date='2024/12/15'", SearchDirection:=adSearchForward, Start:=adBookmarkFirst If RsA.EOF = False then Msgbox RsA.Fields.Item(<Field Name>).Value