Problem Exportimg data from Access adp(project) to excel as a pivot table

Question

Post reply

Problem Exportimg data from Access adp(project) to excel as a pivot table

Carla Randell

SSC-Addicted

Points: 419
More actions
March 15, 2007 at 1:58 am

#194501

Hi ,
Could you please assist.
I have a Access project (adp) with SQL backend Database.
I have created a report and would like to export it to Execl as a pivot table in a macro but I get the following error:
XML ERROR in PivotTable
REASON: Missing Tag
ACTION: Aborting current XML group
FILE: C:\DOCUME~1\ZARAND~1\LOCALS~1\Temp\PivotTable83119_cachedata.xml
GROUP: ElementType
TAG: attribute

I have also tried to do a straight export pivot to excel from the Access menus but get the same error. I apprecaite your help on this.
Regards
Carla

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Perry Harts Ten Centuries Points: 1271 More actions · Answer 1

Is the report using a view or stored procedure?

Perry

Carla Randell SSC-Addicted Points: 419 More actions · Answer 2

Hi,

Thanx for coming back to me.

I am using a Query which is linking 2 tables .

I renamed all the fields in the query as originally I thought it may be the field names in the tables, as this application was originally an .mdb and the naming convention they used had spaces in the field nmaes.

In the mdb this pivottable to excel works perfectly with the same query, which is why i'm tottally flumoxed as to why it fails when linking to SQL backend.

Ciao, Carla

Perry Harts Ten Centuries Points: 1271 More actions · Answer 3

If you are using an ADP then your queries would be views on SQL. From Excel you can connect directly to the view by importing external data. That may be a cleaner approach than through reports.

Carla Randell SSC-Addicted Points: 419 More actions · Answer 4

Unfortunately the project is going out to users , who may not feel comfortable wuth connecting to the view from execl, hence why I was trying to just export the data , once they are happy with what they see in access.

Do you know why pivottableto execl seems to fail when using .adp?

Should I create something in SQL rather as a cross-tab and export that, any suggestions are most welcome.

Regards

Perry Harts Ten Centuries Points: 1271 More actions · Answer 5

For my users I set up the data connection and stored the excel file at a Sharepoint site. The only training I had to do was teach the users to enable refresh when it opens.

Carla Randell SSC-Addicted Points: 419 More actions · Answer 6

Hi,

Thanx for that advice.

Have you ever tried to create a cube definition in SQL ? I have read that you can view and customize a pivot table by using the Cube Definition Manager, which is then available after you create and process the cube definition. I am going to try and persue that a little

Regards

Perry Harts Ten Centuries Points: 1271 More actions · Answer 7

No, I have not. But sounds like a good approach. Let me know if it works.