Excel code in Access

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

Curry said:

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

Click to expand...

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"

Thank you. I had tried that without any success.

Curry said:

Thank you. I had tried that without any success.

Click to expand...

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

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

Curry said:

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

Click to expand...

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

Now can you see Excel specific items?

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

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

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

IC