Adding Subtotals automatically to a TransferSpreadsheet Exported Excel File.

[slscanlon3]

Is it possible within a Macro to add subtotals to an exported
spreadsheet in Excel? I use TransferSpreadsheet to export the file, but
the data is raw and I would like to give it more a report feel to it by
adding subtotals automatically. There may be a better way to do this,
but the end user needs to be able to have this output with a click of a
button. Any help please????

 

[Guest]

You can't do that with a TransferSpreadsheet. You will have to write VBA
code that will create a workbook, load the data into a worksheet, and create
the subtotals programmatically using the Subtotal method. Getting the data
into the sheet is the easy part. You can use the CopyFromRecordset method.

 

[slscanlon3]

That's what I thought... does anyone have any example code I could work
from?

 

[slscanlon3]

I could get it to work using code by running the code in a Macro in
excel, but I am not sure how to get this code to work through Access.
How do I create a Module that runs these subtotals in Access on an
excel file that has already been outputed?

Here is my code in excel:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(18), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

End Sub


Thanks for the help!

 

[Guest]

Creates a new workbook:
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
xlBook.Activate

Loads the data:
Set qdf = CurrentDb.QueryDefs("qselSCCBrpt")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstSCCB = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
xlSheet.Cells(intX + 3, 1).CopyFromRecordset rstSCCB
lngDetailCount = rstSCCB.RecordCount
rstSCCB.Close
Set rstSCCB = Nothing
Set qdf = Nothing

Creates the subtotals:
With xlSheet
.Range(.Cells(lngFirstDataRow, 1), _
.Cells(lngLastDataRow, 19)).Subtotal groupBy:=1,
Function:=xlSum, _
totalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19)
End With

There is quite a bit more to creating a complete worksheet. You will have
to define your cell formatting, put in your header row, etc., but you should
be able to get the details from browisng the Excel Object model and any other
reference material you have on coding for the Excel Object Model in VBA.