We help IT Professionals succeed at work.

Can I apply PivotTable.XMLData output from Access to an Excel Pivot Table?

558 Views
Last Modified: 2012-05-05
I have saved the structure of various pivot tables as XML in a table so that users can save their favorite pivot views or can call up entries and recreate frequently used structures instantly.

To learn about using/extracting the PivotTable.XML data, refer to the EXCELLENT solution at:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21259888.html
for more details.  I had certainly never heard of this capability before I found it on EE.

My question is, does anyone know how to apply this XMLData specification to a silimar recordset in Excel?  My user wants to be able to manipulate the output (as a pivot table) once in excel.

If this is not possible, is there any other way I can port the pivot table and data in it's current format (subform) to excel?

Thanks for any guidance.
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
gethryn,

1. What's confusing is that since this Q is in both the Excel and Access TA's, it is hard to tell where you are doing what, and where you want it to go.
Access-->Excel?
or
Excel-->Access?

2. <is there any other way I can port the pivot table and data in it's current format (subform) to excel?>
Can you explain?
A pivot table will be the same in Access or Excel. If your pivot table exists in Access as a subform, it will be a pivot table in Excel.
(I don't think you can bring an Access Main/subform into Excel.)

JeffCoachman

Author

Commented:
Jeff,
Sorry I wasn't clear... to your questions:

1. I want to export from Access to Excel.

2. I have a Form/Subform in Access.  The main form allows the user to pick the user-saved pivot view from the stored list and applies it to the Subform (which is in Pivot Table view) using PivotTable.XMLData.  If I write code to export that subform to Excel, I get the datasheet in Excel, and not the pivot table as it appears in the subform.  I would like to see the same pivot table in Excel when I export it.  I do not need the main form content to be exported to excel.

Thanks for your help!  Hope this clears things up some more.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
gethryn,

Oh "That" subform! (the one I helped you with in a previous post)
:)

Pivot Tables in Excel and Access are not exactly the same.

I don't think you can "easily" port one to another, let alone import these "saved settings"

What are they doing in Excel that they can't do in Access?

I mean, if you've got this entire slick system in Access and it works.
Why not educate users on how to manipulate the Pivot table in Access?

JeffCoachman

Author

Commented:
Hehehe -- it WAS you!  Many thanks for that  :)

I figured this may be the answer...

What they want to do is use the output from the pivot table as input into an existing spreadhseet; add some arbitrary values and comments here and there; and spit out results with some pre-existing formulas.

This sounds possible in theory... and I toyed with the idea of doing this in access, but it is a little too inflexible, and not to mention difficult for a 'novice user' to comprehend why they can't just 'change that value to 23' for example.  In excel, they can do this to their heart's content with little need for training classes.

If you have any thoughts on how I might mock up multiple pivots in a report, and port that to excel for endless hours of joyous user manipulation, i'd bump it to 500 points and send you a case of beer ;)  -- perhaps we can work on why the subform does not export as a pivot?  Could we create a temporary query and make it a pivot table, perhaps?

Thanks for the help again,

Geth
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
gethryn,

Let me see if I understand your intentions:
1. You have this great pivot table system in Access, you can do what ever you want.
Users can save Pivot Table configurations and re-use/load them.

Yopu now want to move this entire system to Excel maintaining the functionality that you have in Access.

Is this correct so far?

JeffCoachman

Author

Commented:
Nearly.

All they want in Excel is the Pivot table.   The ability to re-use and load, etc., can all stay in Access.

The question is, since I have the definitions of the pivot in Access, can I just "apply" it to data in Excel for the purpose of creating the same pivot in XLS format.

Failing that, is there any other way to make a pivot table from a form export correctly to MS Excel?

Thanks again,

Geth
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.