Excel Selection.Subtotal in Access Macro 1

[NMiller007]

Hello,

Thanks to this forum, I've learned a lot about controlling Excel documents from within an Access macro. Most of the time, I can record the Excel macro and with a little tweaking (put a . in front of the line) get it to work in Access.

I am now converting an old Excel macro and I'm not sure how to use the Selection.Subtotal command (I've never even used in Excel).

Here is the original code:

Range("A1").Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6), Replace:=False, PageBreaks:=False, SummaryBelowData:=True

When I put a period in front of those lines, Access give me a weird message box that just says 400 (I haven't added in any error checking yet). How can I get these to work in my macro? Here's more of my Access macro:

Set objXL = CreateObject("Excel.Application")
With objXL
    .Visible = True
    Set objWB = .Workbooks.Open("CMBS.xls")
    .Columns("F:F").NumberFormat = "$#,##0.00"
    .Columns("A:O").EntireColumn.AutoFit
    .Range("A1").Select
    .Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    .Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    .Range("A1").Select
    .DisplayAlerts = False
    .Save
End With

Thanks!

 

[PHV]

Perhaps this ?
.Selection.Subtotal GroupBy:=1, Function:=[!]-4112[/!], TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=True ' xlcount
.Selection.Subtotal GroupBy:=1, Function:=[!]-4157[/!], TotalList:=Array(6), Replace:=False, PageBreaks:=False, SummaryBelowData:=True ' xlSum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NMiller007]

Thanks!! That worked (of course).

I believe you have helped me on more than one occasion. I really appreciate it.

Is there a place where all of these Excel constants are listed?

Thanks again!

 

[PHV]

a place where all of these Excel constants are listed
When in the Excel VBE press the F2 key.
The F1 key is useful too.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NMiller007]

I was going to mention that I didn't think they were in the help file (I searched for constants and xlCount).

I never knew about the object browser. Very interesting.

Thanks!