I have a worksheet in excel and want to import data from a query into it but not sure how to get the recordset part working, the connection has been made succesfully using code from EE.
Dim cnn As ADODB.Connection
Dim strQuery As String
Dim rst As New ADODB.Recordset
Dim strPathToDB As String
Dim wks As Worksheet
Dim i As Long
Set wks = Sheets("CW Data")
' change database path and query name as required
strPathToDB = "N:\data\SimpleStats2.mdb"
strQuery = "QryTotalCWCarsYearsBreakdown"
Set cnn = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
' create new recordset
Set rst = CreateObject("ADODB.Recordset")
' open recordset using query string and connection
.Open strQuery, cnn
' check for records returned
If Not (.EOF And .BOF) Then
'Populate field names
For i = 1 To .Fields.Count
wks.Cells(1, i) = .Fields(i - 1).Name
' Copy data starting from A2
wks.Cells(2, 1).CopyFromRecordset rst
help would be much appreciated.
Set cnn = Nothing