I know there are pitfalls of using the ADO object model, however, in the preceding example, it is the only way I see possible to achieve my needs.
In short, I am using ADO to query a table (dynamic range) called tbl_cashbook. I group each code and sum the values of each code based on the parameters of a start and finish date as well as a name.
What I need to do is to collect all this data into an array and then populate the values into a predefined range in my workbook. My array should contain 2 fields, Code and Value.
That said, I enclose the following code as my starting point and seek some guidance as to how I can: 1). create the array, and 2). populate the array into my worksheet range.
Public Sub popicbr(uName As String, uStartDate As String, uEndDate As String)
'collects the code and sum of each code into an array and populates them into the icbr range
'Specifying connections ->>
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
'turn off updates to speed up code execution
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
'Identify the workbook we are using ->>
wkb = Application.ThisWorkbook.FullName
'Open connection to the workbook ->>
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & wkb & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
'Load the range into a recordset ->>
rs.Open "SELECT [Code], Sum([Value]) AS Totals " & _
"From [tbl_cashbook] WHERE ((([Date]) >='" & uStartDate & "' And ([Date]) <='" & uEndDate & "') And (([Details]) =" & Name & ")) " & _
"GROUP BY [Code]", cnn, adOpenStatic
If rst.RecordCount > 0 Then
i = 0
If IsNull(rst![Code]) Then
MsgBox = "Could not detect an records."
'collect records into an array containing the code and the sum of each code
'----ARRAY GOES HERE
End If 'Null test
End If 'rs count
'populate the data into the range
'Populate the array into the workbook range
'Loop here to populate the data
'Start range = icbr_code_start
Range("icbr_code_start").Offset(1, 0) = Array(1)
Range("icbr_code_start").Offset(1, 3) = Array(1)
Set rst = Nothing
Set cnn = Nothing
'turn on updates
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
Any help appreciated.