Souleymane has attended:
Access VBA course
Export pivot table to excel
Hello Steven
I have a pivot table with some calculated total and calculated field that I want to export to excel. unfortunately, it does only export the non calculated fields. Do you know the command that can be used in VBA to export only the values?
Thanks.
Souleymane
RE: Export pivot table to excel
Hi Souleymane
Thanks for the question.
First apologies in the delay to this response. We have had a lot of traffic on the forum recently and I am just now catching up.
I have looked into this from several angles and can suggest a few possible responses. It would however be helpful if you could advise me of the different data types that are included in the underlying table or query. i can then advise as to the best route forward.
Regards
Stephen
RE: Export pivot table to excel
Hi Steve,
Thanks for having looked at my request.
The data are only figures, double ones.
Best regards,
Soul
RE: Export pivot table to excel
Hi Soul
I've done a bit of exploration and there is very little mentioned about this in any of the literature.
The best guess would be that when you create a calculated field in your pivot table it is not actually a field in the underlying table or query and therefore cannot be passed into excel. When you export into excel it seems that the fields in the table or query are automatically mapped into the relevant pivot fields and as your calculated filed is not actually in the table or query it can't be passed.
The obvious solution is the best here. Simply add your calculated field to your query design and then construct the pivot table view. This will then export over fine. If you are working from a table, build a query taking all the fields from that table and then add the calculated field as above.
Sorry but there appears to be no other solution, although if I come across anything I will let you know.
Regards
Stephen
