Excel code in Access

[Curry]

Hi All,

I am running some Excel code within Access after exporting to excel using the code below. All is ok except at the line...

xlApp.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=False

I am not sure what other changes need to be made to this Excel code besides the addition of xlApp. at the start. This usually works fine as per below however it will not work with this line. If I remove this line the code below works without a problem.

-----------------------------------------------------------------------
Dim xlApp As Object

xlApp.Cells.Select

xlApp.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=False

xlApp.ActiveSheet.Outline.ShowLevels RowLevels:=2
xlApp.Range("F1").Select
xlApp.Visible = True

------------------------------------------------------------------------

Thanks

 

[darbid]

xlApp.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), Replace:=True, PageBreaks:=False, SummaryBelowData:=False

I am not advocating I know the answer to your problem, but my guess is that there is something specific in there that Access does not understand. And my first guess will be "xlSum" try "xlApp.xlSum"

 

[Curry]

Thank you. I had tried that without any success.

 

[darbid]

Thank you. I had tried that without any success.

Then the next thing is to get the cell as an object instead of working with Selection.

 

[Curry]

Thanks again, however I do not know how to do this? Could you provide an example?

 

[darbid]

Thanks again, however I do not know how to do this? Could you provide an example?

My suggestion was just an example of what to look for. I didn't think xlsum was the only one. For example look at this

Dim xlsRange As Excel.Range

Set xlsRange = Excel.Range("I27: J36")

'OPTION !
xlsRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'OPTION 2        
Call xlsRange.Subtotal(1, xlSum, Array(2), True, False, xlSummaryBelow)

Now can you see Excel specific items?

 

[Curry]

Hi There,

I tried both of your options without success, although I am not confident I did all that was required. It did not seem to like

Dim xlsRange As Excel.Range

So in this case I cut the code down to

xlApp.Selection.Subtotal GroupBy:=1, Replace:=True, PageBreaks:=False, SummaryBelowData:=False

and luckily the rest was defaulted as I needed them and this worked fine.

In my second issue, yes the same problem however the default is not "Paste Value" so I need to set this with the code and this is where the problem is.

xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Thanks for your assistance

IC

 

[darbid]

Please show your full code as this below works fine for me.

Dim xlApp As Excel.Application
Dim xlWksht As Excel.Worksheet
Dim xlWkb As Excel.Workbook
Dim xlsRange As Excel.Range

Set xlApp = GetObject(, "Excel.Application")

Set xlWkb = xlApp.Workbooks.Open("C:\Book1.xls")

Set xlWksht = xlWkb.Worksheets("Sheet1")

Set xlsRange = xlWksht.Range("E12: F23")

Call xlsRange.Subtotal(1, xlSum, Array(2), True, False, xlSummaryBelow)

Set xlWkb = Nothing
Set xlWkb = Nothing
Set xlApp = Nothing

 

[Curry]

Got it this time.
Thanks for all your assistance with this.

IC