Create Values snapshot of Excel Pivot Table

[MikeAuz1979]

Hey all,

I have a report which is an excel 2000 pivot table sourcing data from an MS Access 2000 which is fine until a guy with excel 97 tries to open it and the pivot table doesn't refresh.

So I'm thinking that I could email a snapshot to the 97 guy when the access database import runs which will open the excel file refresh the pivot table, copy it as values and format to a new excel workbook and then email it.

I got as far as the code below but the last line has the error 'Application defined error':

Set acapp = CreateObject("Excel.Application")
acapp.Visible = True
acapp.displayalerts = False
acapp.workbooks.Open ("G:\Bsprice\REPORTS\Network Billing.xls")
acapp.worksheets("Network Billing").Cells.copy
acapp.workbooks.Add
acapp.activesheet.range("A1").select
acapp.activesheet.PasteSpecial paste:=xlValues

Thanks for any help
Mike

 

[CautionMP]

MikeAuz1979,
[tt]xlValues[/tt]? If your running this from Access do you have a reference to the Excel type library? If not you will need to change [tt]xlValues[/tt] to an actual numeric value.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[PHV]

You may try this:
acapp.Workbooks.Add
acapp.ActiveSheet.Range("A1").PasteSpecial -4163 ' xlValues

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