Pass a SUM formula from Access to Excel
Posted on 2005-05-06
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))