Pass a SUM formula from Access to Excel

Posted on 2005-05-06
Last Modified: 2008-02-01
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))  
Question by:dbfromnewjersey
    LVL 13

    Expert Comment

    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 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

    '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.

    Author Comment

    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?  
    LVL 13

    Accepted Solution

    Sure you can.   If you're inserting new rows you will have to call the function again to recalculate.  

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now