Exporting data to Excel and formatting using VB

[tas72]

I have a report that exports data to excel (it is too big for a regular
report). I then open the excel file in VB and reformat it. I am getting an
error when I attempt to have subtotals added to the report. The error states
that Excel cannot determine which row contains column headings (row 1). How
can I indicate that the first row is the column headings? Here's the code:

Set xlObj = GetObject(FilePathName)
Set xlSht = xlObj.Sheets(SheetName)
Set xlApp = xlObj.Parent.Parent
Set msExcel = CreateObject("Excel.Application")

' set first row to bold and underline
xlSht.Rows("1:1").Font.Bold = True
xlSht.Rows("1:1").Font.Underline = 2
'xlUnderlineStyleSingle

xlSht.Columns("A:R").EntireColumn.AutoFit
xlSht.Columns("A:R").EntireColumn.VerticalAlignment = -4160 '
xlTop
xlSht.Columns("H:H").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("L:L").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("P").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("T:T").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("X:X").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AB:AB").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AF:AF").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AJ:AJ").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AN:AN").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AR:AR").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AV:AV").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Columns("AZ:AZ").EntireColumn.NumberFormat = "$#,##0.00"
xlSht.Rows("2:20000").RowHeight = 13
xlSht.Columns("A:AZ").EntireColumn.Subtotal GroupBy:=1,
Function:=-4157, TotalList:=Array(8, 12, 16, 20, 24, 28), Replace:=True,
PageBreaks:=False, SummaryBelowData:=True

 

[Douglas J. Steele]

While you may be running this code in Access, realistically this is an Excel
question. I suspect you'll have more luck asking in a newsgroup related to
Excel.

 

[tas72]

After some experimenting I found that the following did the Subtotaling
without error:

xlSht.Range("A1").Subtotal GroupBy:=1, Function:=-4157, TotalList:=Array(8,
12, 16, 20, 24, 28), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Thanks for help.