MS Access export to Excel and Total columns in Excel

[epiek]

Hi there
I have exported a recordset to Excel from Access.

What is the easiest way to write vba coding that will sum the columns

Lets say I have 15 columns and 300 rows

I pick up the amount of rows with:

totalrows = rs.recordcount

in a nested FOR loop how to I refer to i.e

objWkb.activesheet.cells(lastrow, j) = sum(A2:A300) how do I reference this sum formula in the FOR loop to refer to my row and cell?

Currently I am looping through all the rows and totaling each row in the column. There must be an easier way?

Help will be much appreciated..!

 

[Pat Hartman]

I don't have any specific code to share but when I need to automate Excel, I use the Excel Macro recorder to help generate the code.

I would use TransferSpreadsheet to create the spreadsheet and transfer the data. Then use OLE to open the workbook and fix it up.

Open Excel with the data you exported already in place.
Turn on the Macro recorder.
Go to the end of the data.
Insert a sum.
Copy the sum across all the columns
Do the same thing for adding the right side sum that sums the columns for each row.
Turn off the macro recorder.

The code can usually just be copied to Access but sometimes you might have to adjust references since the references from within Excel are slightly different from when you are referencing objects from outside Excel.

 

[epiek]

Thanks

I have already got all the coding.

The data has been transferred already via coding

When the sum function is recording from within Excel, i.e. =Sum(A2:A300),

I need to put that formula within the coding of MS Access, to automate it for any number of rows.

I tried changing the formula "=sum(Cells(2" & j & "," & j & "):Cells(" & k & "," & j & "))"

where K is the last row of the range that I want to sum
but the coding gives an error.

I am looking for the syntax for coding that will execute that sum function,

 

[Cronk]

If you are exporting k records and the column containing the values to be summed is j

Then you need to put the sum value in cell(k+1, j). So your code would be would be something like

rst.movelast
k = rst.recordcount
xlApp.cells(k + 1, j) = "=sum(" & chr(64 + j) & "1:" & chr(64 + j) & k & ")"

So if you are exporting 10 records and the column to be summed is 2, you should have in cell B11, = Sum(B1:B10)

Note that the syntax is Cells(R,C)

Also note that I am a bit of a hacker in Excel and there could well be a more elegant way. I'm like Pat - record macros in Excel and then modify for vba in Access. I've had to add the sum formula a number of times.

Another hack, rather than putting the formula in the cell, is to add an additional record in your Access table which contains only the sum of the rows being exported so this value would be exported too.

 

[Pat Hartman]

Excel has functions that find the end of the recordset. It might be something like Shift-End. I am not an Excel expert so I don't know the command off hand. Perhaps someone with more detail knowledge of Excel will chime in.

 

[sxschech]

Are you saying something like this?

   A    B    C
----------------------
1| 5    3     1
2| 5    7     4
3| 3    9     6 
4|Sum   Sum   Sum

You could do this:

I was doing averages, so you would change Average to Sum in the code below.
The code locates the last row inserts a formula in the cell (in this case is last row of Column A, but you can change to another col, if your totals start in Col C), then it copies that formula across all the cols until it reaches the end. In my case, the end col is known, so I put that in, if the last col is unknown, then change that to lastcol.

 'Add Total Row with avgs one line below actual last row for having blank line between data and totals
  .Cells(lastrow + 1, 1) = "TOTALS"
  .Range("A" & lastrow + 1 & ":AW" & lastrow + 1).Font.Bold = True
  .Range("K" & lastrow + 1).Formula = "=AVERAGE(K2:K" & lastrow & ")"
  .Range("K" & lastrow + 1 & ":AF" & lastrow + 1).FillRight

This is how I defined lastrow and lastcol

 lastrow = rs.RecordCount + 1 'assumes header row
 lastCol = .Range("A1").CurrentRegion.Columns.Count

Here is code to add formula down a col

 .Range("P2").Formula = "=AVERAGE(K2:O2)"
 .Range("P2:P" & lastrow).FillDown

 

[epiek]

Thanks to Cronk with this coding.


rst.movelast
k = rst.recordcount
xlApp.cells(k + 1, j) = "=sum(" & chr(64 + j) & "1:" & chr(64 + j) & k & ")"


that did the trick!!