Access VBA creating an Excel spreadsheet with a total line

[PSULionRP]

I have an Access module which dynamically creates an Excel spreadsheet. Now
the Business User has asked that we put a Total Line in the Excel
spreadsheet. The dynamic portion of this is an ADO Query that goes out to
Oracle and runs a query and returns a result set and then copies it to a
local record set and then to an Excel spreadsheet.

How do I within Access VBA determine the range, because the spreadsheet will
never be the same, and then use that range to create a formula to insert to
the local recordset which dynamically creates the Excel spreadsheet with the
Total Line???

Thanks in advance for your review and help and hopeful for a reply.

PSULionRP

 

[Daryl S]

This can be done with Access. You can get the recordcount from the ADO Query
to know how many rows are being entered into the spreadsheet, and adding a
title row, can determine the Excel formula to enter into the total line - you
would usually leave a blank row in Excel. For example, if the query
returned 100 rows, then in Excel there would be a header on row 1, then data
in rows 2 through 101. You would want to put the total line on row 103 with
the formula to sum rows 2 through 102 (or 101, depending on how the
spreadsheet will be used).

If you are just exporting the query results to Excel, you will need to
define the spreadsheet as an object and open it, then you can add the total
expressions into the proper cells. The code would be something like this:


Sub UpdateExcelFile()
' Open an excel spreadsheet and add total lines (row numbers assumed from
query we exported)
Dim strColumn As String
Dim intLoopCounter As Integer
Dim xlsAPP As Object
Dim xlsRange As Excel.Range
Dim xlsSheet As Excel.Worksheet
Dim strXLSPathFileName As String 'should probably pass in as a
parameter
Dim intQryRows As Integer 'should probably pass in as a
parameter
Dim intNumColumns As Integer 'should probably pass in as a
parameter

intQryRows = 26
intSumRow = intQryRows + 3
intNumColumns = 5

Set xlsAPP = CreateObject("Excel.Application")
strXLSPathFileName = "C:\Documents and Settings\Daryl.Schmitt\My
Documents\PlayDenverDirectory.xls"
xlsAPP.Workbooks.Open strXLSPathFileName
Set xlsSheet = xlsAPP.Worksheets(1)


With xlsAPP
For intLoopCounter = 2 To intNumColumns 'Ignores column A and starts
sum on column B
strColumn = Chr(intLoopCounter + 64) 'Converts intLoopCounter
to strColumn
With xlsSheet.Range(strColumn & intSumRow)
Set xlsRange = xlsSheet.Range(strColumn & intSumRow) 'e.g.
A101 to contain sum
If Not xlsRange Is Nothing Then
xlsAPP.Range(xlsRange.Address) = "=Sum(" & strColumn &
"2:" & strColumn & intQryRows + 1 & ")" 'Sum formula for column
End If
End With
Next intLoopCounter
End With
Set xlsRange = Nothing
xlsAPP.ActiveWorkbook.Close
xlsAPP.Application.Quit
Set xlsAPP = Nothing
End Sub

Good Luck!

 

[Rob Wills]

'many ways to do this...

'I'm assuming you're using access to export the query / Table...

'The trick then is to open an instance of Excel (I'll assume you don't have
any)
'You'll need to reference the Excel library

dim appExcel as Excel.application
set appExcel = new excel.application

'next you'll need to open the Excel workbook
dim wkb as Excel.Workbook
set wkb = appExcel.Workbooks.Open("C:\Filename.xls")

'Identify your last cell - assuming you have 1 sheet
dim i as integer
i = wkb.sheets(1).range("A1").SpecialCells(xlCellTypeLastCell).Row

'do you want a row space before you write your totals?
Dim iTotal_Row
iTotal_Row = i+2

'How many columns do you want totals for? below shows B:M totalled
dim icol
for icol = 2 to 13
cells(iTotal_Row, iCol).formulaR1C1 = "=sum(R[-2]C[0]:R[-" & i & "]C[0])"
next icol

wkb.save
wkb.close
appexcel.quit
set wkb = nothing
set appexcel = nothing


HTH's
Rob