Hi,

Here's one way you can try. It requires that you add a reference to the
Excel in your project (Tools References Microsoft Excel 8.0 Object
Library).
After the query has been exported to Excel, it opens it (instead of
being opened by Application.Hyperlink). A date variable is created for the
Save As and then it's sorted & sub-totaled & closed.
The original file is then re-opened and the query results deleted.
Hopefully this works for you, or at least helps.


Sub Ex1()

Dim stDocName As String
stDocName = "C:\VB 2008\CallDetail.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryCallDetails", stDocName, , "WeeklyCalls"

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook

Set xl = New Excel.Application
Set xlwb = xl.Workbooks.Open(stDocName)

xl.Visible = True

Dim dtSave As String
dtSave = Format(Now(), "mmddyy")
xlwb.SaveAs ("C:\VB 2008\CallDetail" & dtSave & ".xls")

Dim WeeklyCalls As Range
Set WeeklyCalls = xlwb.Sheets("WeeklyCalls").Range("A1").CurrentRegi on

With xlwb.Sheets("WeeklyCalls").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange WeeklyCalls
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

With xlwb.Sheets("WeeklyCalls").Range("WeeklyCalls")
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

xlwb.Close SaveChanges:=True

Set xlwb = xl.Workbooks.Open(stDocName)
xl.DisplayAlerts = False
xlwb.Sheets("WeeklyCalls").Delete
xl.DisplayAlerts = True
xlwb.Close SaveChanges:=True

xl.Quit

Set xl = Nothing

End Sub