Sending MS Access Data to Excel

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Sending MS Access Data to Excel - Part 5

A reader wrote and asked, "How can I create a 'totals' line in the Excel output?"

Good question.

First, let's remind ourselves how Excel does it normally. Open an existing Excel workbook that has a Totals line, or quickly create one. Here is what shows up in the formula bar when I point to a cell with a total in it.

=SUM(C10:C34)

Now that we know what it should look like, it's quite easy to create a totals cell based on a variable height column. The key is to keep track of what row you are on.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Remember, this code will only work if you have Excel references turned on. The References section is found under the Tools menu in the VBA editor. See the above mentioned article on DAO for more details on the References menu. Also, I have left out a lot of code, as the first 4 articles in this series should have you to the point where you understand the missing parts.

iRow = 10

Set rst = CurrentDb.OpenRecordset("SELECT * FROM myTable " & _
   "WHERE ((somefield)= 'somevalue') " & _
   "order by field1, field2;")
rst.MoveFirst

Do While Not rst.EOF
   objSht.Cells(iRow, 1).Value = rst!field1
   objSht.Cells(iRow, 2).Value = rst!field2
   objSht.Cells(iRow, 3).Value = rst!field3
   iRow = iRow + 1
   rst.MoveNext
Loop

'--- a special total is required in column 'C'
objSht.Cells(iRow + 2, 3) = "=SUM(C10:C" & iRow + 1 & ")"

Note that there are 3 parts to the creation of the total formula.

"=SUM(C10:C" is 'hard-coded' to start the total from cell C10 and ending somewhere else in column C
iRow + 1 determines the final row that gets included in the total
")" adds the final required parenthesis

Thus, if the iRow counter has the value '33' in it at the end of the loop, then the formula created by the above code will be =SUM(C10:C34) and this will be placed in cell C35

Entire series ...

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.