Trouble creating a subtotal in Excel from Access

[BillDickenson]

I have a table that I exported from Access to Excel. Now I need to set up a subtotal on the table. I was hoping to use the excel subtotal command and created a macro. However, it just doesn't work. I've tried about 4 other techniques without any luck. Searched a few different topics. I get a variation on "Runtime Error 91. Object Variable or With block variable not set". I've tried setting to a couple of things, even fully qualified it. No luck. I'm obviously missing something very basic. Here is the code

Public Function subTotalArea()
'Public Function subTotalArea(iRows as Integer, ifilename as String)
'comment out the way it's called so we can test

'Create Excel Objects
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim mydb As Database
Dim szSheetName As String
Dim iRows As Integer
Dim ifilename As String
szSheetName = "Summary"

'Comment these out for Production

iRows = 8
ifilename = "C:\Ops\Test\R-1-MODZILLA.xls"


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then
'Excel was not open
Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo 0


Set xlWB = xlApp.WorkBooks.Open(ifilename)
xlApp.Visible = True

With xlWS
.Range("C16:Q16").Cells(index, 0).Select
.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False, SummaryBelowData _
:=True
End With
End Function


Someone want to clue me in ? This is driving me a bit nuts.

 

[SkipVought]

Hi,

Set xlWB = xlApp.WorkBooks.Open(ifilename)
xlApp.Visible = True

[b]Set xlWS = ActiveSheet[/b]  'you may need to quallify ActiveSheet???

[b]'where is index set???[/b]

xlWS.Range("C16:Q16").Subtotal _
      GroupBy:=1, _
      Function:=xlSum, _
      TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15), _
      Replace:=True, PageBreaks:=False, _
      SummaryBelowData:=True

End Function

I don't see what this statement does...

With xlWS
   .Range("C16:Q16").Cells(index, 0).Select
...
End With
code]
It it's the contiguous area you want to apply the subtotal to...
[code]
xlWS.Range("C16:Q16").CurrentRegion.Subtotal _

You don't need to use the Select method.

How Can I Make My Code Run Faster? faq707-4105

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[BillDickenson]

Sorry; you are right. I got caught between versions. I cleand it up and used your suggestion. Now I get the "subtotal method of Range Class failed" (1004).
This is the revised code. Thanks for looking at this.

Public Function subTotalArea()
'Public Function subTotalArea(iRows as Integer, ifilename as String)

'Create Excel Objects
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim szSheetName As String
Dim iRows As Integer
Dim ifilename As String
szSheetName = "Summary"
'Comment these out for Production
iRows = 8
ifilename = "C:\Ops\Test\R-1-MODZILLA.xls"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then
'Excel was not open
Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo 0
Set xlWB = xlApp.WorkBooks.Open(ifilename)
Set xlWS = xlWB.worksheets(szSheetName)

xlApp.Visible = True

xlWS.Range("C16:Q16").Cells(iRows, 0).Subtotal _
GroupBy:=1, Function:=xlSum, _
TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15), Replace:=True, _
PageBreaks:=False, SummaryBelowData _
:=True
End Function

 

[SkipVought]

I get no error when running the subtotal statement of yours. I'm puzzled???


Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[sfm6s524]

If you can't get the direct handling of the Excel object to work, perhaps using a method I thought up for my "I want the Excel export file to look just like this" crowd. Basically, I use the Access report itself to open the Excel object on the On Open event, write the headings, details, and footing controls on their On Print event, using the formatting of the Access report controls, font, size, bold, etc., even lines. Then close Excel object on reports Close event. Excel formatting only occurs if a global boolean flag is set, so it can be printed or previewed like normal.
I use a 'DoCmd.OutPutTo acOutputReport, "rptName", acFormatTXT, TempFile, False' to route to temporary file, and each control that you want exported to Excel has its Tag property set to the row offset within that section, and the desired column, which is probably the most time comsuming part of it. So a Total field on the Access footer may have a Tag setting of ExcelColumn=C;RowOffset=1, which means write its value to column C, 2nd row of where that footing began to print. Each On Print event loops through all the controls within its section, and passes info to a function to process, like:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim Ctrl As Control
If gFormattedXL Then
For Each Ctrl In Me.Detail.Controls
FormatXLCell xlsApp, xlsWSheet, CurrentRow, Ctrl
Next Ctrl
CurrentRow = CurrentRow + 2
End If
End Sub

Anyway, hate to babble, but the code is a bit long to post. But it does provide a set of tools to create Excel export files rather easily. Perhaps I could pull together a sample db if you're interested.

Ken

 

[RoyVidar]

You've declared all objects as object, not as excel.application/workbook... this to me indicates you're using late binding (also my preference), but then some/most of the Excel constants arent available, and you'd probably need to replace with literals.

I think for instance xlSum -> -4157

Also, SkipVougth's suggestion included another alteration of the Subtotal statement (removing the "Cells(iRows, 0)" part):

[tt]xlWS.Range("C16:Q16").Subtotal _
GroupBy:=1, _
Function:=-4157, _
TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15), _
Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True[/tt]

I can also make it work with this as the first line

[tt]xlWS.Range("C16:Q16").cells.Subtotal _[/tt]

BUT - you're neither closing Excel, nor release the object variables, so you may have an extra instance of Excel in memory:

[tt]set xlWS = nothing
' also a close
' xlWB.Close <saveas parameter?>
set xlWB= nothing
' also quit?
' xlApp.quit
set xlApp= nothing[/tt]

Roy-Vidar

 

[PHV]

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillDickenson]

Changing the function to the -4157 worked perfectly. Good catch.

And thanks for the suggestion on formating. I'll need that next.

Thanks all