Acces to Excel VBA Performance Question

[JeffK627]

I'm building an application that stores data in an Access database and displays it in an Excel spreadsheet. The data in the database is stored in the following columns: Product, BusinessUnit, ProductCode, State, Quarter, TypeAUnits, TypeBUnits.

The user wants the data broken out by Quarter, i.e., TypeAUnits and TypeBUnits for Quarter 1 of 2008, then Quarter 2 of 2008, etc. up to the current Quarter. There are over 300,000 total rows in the table, and about 26,000 rows per Quarter.

Where I run into a problem is that there aren't necessarily unit numbers for every Product Code in every Quarter, so I have to match the TypeAUnits and TypeBUnits up with the right ProductCode and State, row-by-row. The Product, BusinessUnit, ProductCode and State columns on the Excel sheet are already populated. This is the code I'm currently using, where rst1 is the recordset obtained by selecting all fields from the table where Quarter = the desired quarter:

For i = 11 To lastRow
  If rst1!ProductCode = Sheet1.Cells(i,3) AND rst1!State = Sheet1.Cells(i,4) Then
    Sheet1.Cells(i,5) = rst1!TypeAUnits
    Sheet1.Cells(i,6) = rst1!TypeBUnits
  End If
Next

This is taking forever to run. I have Screen Updating set to False and Calculation set to XLManual, can anyone think of another way to speed this up?

 

[Norie]

Have you tried doing this in Access?

It should be easy to summarize the data by quarter using an aggregrate query.

 

[JeffK627]

I'm actually not familiar with using Access that way, I'm a SQL, VB and .NET programmer. As such, I'm using Access as the database tier, VBA as the application tier and Excel as the display tier. Can you give me an example of how do to what you suggested and display the results in an Excel sheet as required by my client?

 

[SydneyGeek]

Have you tried using a pivot table in Excel?
Use an external data source pointing to your table, then build the PT and arrange the fields as required. That gives the client a whole bunch of options for filtering and displaying the data, and the update is pretty rapid.

Denis

 

[Fazza]

I might be on the wrong track, but what about changing the last two fields TypeAUnits, TypeBUnits - to become Type (either A or B) and Units.

Some sample data would help understanding.

There isn't a need to paste the whole recordset into Excel to be able to report from Excel. I'd need to better understand what is being done to suggest options - such as an Excel pivot table either with its data source in Access, or an Excel pivot table with its pivot cache populated directly (in code) by the recordset, or Excel pulling data via ADO recordset then pasted to a worksheet, or a query table on a worksheet, or other approaches using VBA.

 

[Fazza]

PS. I was thinking from an Excel perspective. It occurs to me though, why not do the whole task in Access, and if a report is required in Excel, just have some code do that single step of creating an Excel version of the report. F