Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Export pivot table to excel

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Export pivot table to excel

Resolved · High Priority · Version 2007

Reply Fri 13 Mar 2009, 11:06Delegate Souleymane said...

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

For upcoming training course dates see: Pricing & availability

Reply Wed 18 Mar 2009, 11:02Trainer Stephen said...

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

Reply Wed 18 Mar 2009, 19:55Delegate Souleymane said...

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

Reply Fri 20 Mar 2009, 09:58Trainer Stephen said...

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

Thu 26 Mar 2009: Automatically marked as resolved.

Access articles

· Save Time And Effort In Access By Creating Relationships
· Microsoft Access: Access Its Full Potential For Business Rewards
· 3 Reasons to Switch from Excel to MS Access - You'll be Glad You Did!
· User-Proofing Your Access Database: Part 1
· Find Microsoft Access Templates For Any Task

Please browse our web site to find out more about
ms-access-course and other Microsoft training courses.

Access tip:

Open A Combo Box Automatically

This is helpful if users need to enter large amounts of data. There are two ways to open ComboBoxes when they get the focus by using the tab keys.

When the ComboBox gets focus:

Press Alt + Down Arrow on the keyboard

For it to happen automatically, needs a bit of coding:

1. Add a combo box and a text box control to the form
2. Set the combo box's On Got Focus property to the following event procedure:

Private Sub ComboBoxName _GotFocus()

Me!ComboBoxName.Dropdown

End Sub

3. Open your form in Form View and use the TAB key to make sure it works.

View all Access hints and tips