Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel vba create two dimension array and populate range with data

Posted on 2014-04-04
5
Medium Priority
?
957 Views
Last Modified: 2014-04-04
All,

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
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'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
    rst.MoveFirst
    
    i = 0
    
    If IsNull(rst![Code]) Then
        MsgBox = "Could not detect an records."
    Else
        '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
With wks.icbr
    '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)

End With


'release memory
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

'turn on updates
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

Open in new window


Any help appreciated.
TA
0
Comment
Question by:discogs
  • 3
5 Comments
 
LVL 37

Expert Comment

by:Kimputer
ID: 39979151
Would be helpful if you upload this excel file (fill with dummy data), and then give a few example inputs and expected output.
0
 

Author Comment

by:discogs
ID: 39979153
Okay. Here we go.. I have put together a small example of what I seek to achieve from this.

Please keep in mind that the data set is dynamic and that there are many other code routines that require consideration as well.

TA
Book1.xlsx
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 39979530
Do you need the array for some other pieces of code?  If not, I recommend avoiding the overhead of the array and just populating the cells directly while looping through the recordset.  I don't see in the sample the definition of the group for "icbr_code_start" so you may need to update that code to hit the correct cell.

Also, I like to just loop through the record set until I hit the end of the record set.  Try this code and see if this helps:

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
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'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

i = 1

' If record set EOF (End Of File) and BOF (Beginning of File) there are no records
Do While Not rst.EOF

    ' Just write directly to you cells
    'Start range = icbr_code_start
    Range("icbr_code_start").Offset(i, 0) = rst.Fields("Code").Value
    Range("icbr_code_start").Offset(i, 3) = rst.Fields("Totals").Value
    
    ' Increment Offset Counter
    i = i + 1
    
    rst.MoveNext
    
Loop

'release memory
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

'turn on updates
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

Open in new window

0
 

Author Comment

by:discogs
ID: 39979535
Hi. Thanks for getting back to me.

The answer to your question about using the array later in my code is a NO.

I agree that it would be better to use your method by writing the records directly to the worksheet as opposed to storing an array. I thought there must be an easier way and you have confirmed that with me.

I shall try out your code and post an update when finished. Hopefully it works out as I really need to complete this in order to move on with my work.
TA
0
 

Author Closing Comment

by:discogs
ID: 39979550
Absolutely stunning help on this one. Saved a lot of time without having to create an array.
Great help. Thanks. TA
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question