[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Pass a SUM formula from Access to Excel

I need to pass a =SUM formula from Access to Excel that totals the numbers in a column.  I already know how to do it if the number of rows is fixed (for example A3:A20).  However I need it to be coded to allow for a varying number of rows.  Sometimes when I pass the data over, there may be 100 records in my Access source table ( the data I am sending to Excel ).  Other times there may be 300 records or whatever.  The Excel spreadsheet that the data gets transferred to gets created during the transfer.  It does not pre-exist.   I need the total line to be 2 rows below the last data.   The total line must be a =SUM formula so that in the event I add or modify records in the Excel spreadsheet directly,  the total will be adjusted.

The code I currently have is listed below.  It works to total the data that was sent from the Access table but because the total line does not appear as a formula in Excel, any modifications or additions I make within Excel after the transfer do not affect the total line.  Therefore, I need a =SUM formula to be passed over instead. Can someone tell me how to modify the code below to use a =SUM formula instead?  But as I stated above, I need it to allow for a varying number of records.  I guess it has to be coded in such a way as to detect where the last data item appears within a spreadsheet in a given column and then write the total amount 2 rows below that line.  Is this possible?   Thanks.


objExcel_Application.ActiveSheet.Cells(lngRow + 2&, 4) = objExcel_Application.WorksheetFunction.Sum(objExcel_Application.ActiveSheet.Range("D4:" & objExcel_Application.ActiveSheet.Cells(lngRow, 4).Address))  
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 2
1 Solution
 
LucasCommented:
hi there...this is a little bit tricky but you have to keep track of row and colums.  If you are looping through the recordset you have to create a variable call it row and increment it by 1.

so you would have something like:

dim row as long
dim col as long

'set starting row and col ..in this case i'll use 11
'and your loop would be

'you would open up your recordset

do until rs.eof
   myExcel.activesheet.cells(row,col) = rs!yourValue
   row = row+1  'this increments your row
   rs.movenext
loop

'in the end you'll end up with your last row...but you want to add a total colums to it like this.
row = row + 1 'ADD another row

'here is a function that i created that will calculate the sum for you.

If you want to calculate a sum you have to pass the address value of the cell you have to pass the address portion of the cell to the function itself.  In my example i have a static 11 in there because thats where all my numbers start from.

--------------------------------------------------------------------------
Private Function CalcSum(ByVal row As Long, ByVal col As Long, wSheet As Worksheet) As Currency
'calculates the sum for each column


CalcSum = myExcel.WorksheetFunction.Sum(wSheet.Range(wSheet.Cells(11, col).Address & ":" & wSheet.Cells(row, col).Address))

End Function
----------------------------------------------------------------------------

myexcel.activesheet.cells(row,col) = CalcSum(row,col,myexcel.activesheet)

I'm trying to be as clear as i can....if you are still having problems you can find my email address in my profile.

I created this all using objects, so i declared myexcel variable as an object.
0
 
dbfromnewjerseyAuthor Commented:
Thanks.  I'll try to get it to work and write another reponse this evening.  The code you gave can be put in a module within Access?   Cause that's where I currently have the code that does the transferring.   Also, using your SUM line of code, the sum will be automatically adjusted if I modify the Excel spreadsheet by inserting additional rows of data or change some numbers with the column?  
0
 
LucasCommented:
Sure you can.   If you're inserting new rows you will have to call the function again to recalculate.  
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now