How to Export Access Pivot Table to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Macro that runs each AM on a query on my database and generates a
pivot table view of my data. I need to find a way to export this data to
excel, maintaining the pivot table view.
All I have been able to achieve is exporting to Excel and the dataview goes
back to the spreadsheet/datasheet look. How to I get it to maintain the pivot
table?

Thanks
 
Frank -
By "Pivot table view" I mean that when you go into Excel, the pivot table is
there... as opposed to just the regular spreadsheet where you would need to
use the pivot table wizard and recreate the pivot table I had just created in
Access.
This can be done when you are in Access and you click the button that says
"export to Access", but if you try to program it into a Macro, it goes to
Excel as a datasheet, not a pivot table.
Have I helped or confused you more?
Thanks
 
hi,
no, i'm not confused any more.
Excel "creates" the Pivot Table view with a wizard from
data in excel.
When excel creates the pivot table, the wizard adds all
the formating,lins and colors ect.
Access is just downloading the pivot table data. no fancy
stuff. it did not go through the excel pivot table wizard.
If this view is important to you, then you may have to
download the raw data from access, then use the excel
pivot table wizard.
sorry
Frank
 
Here is the part I don't understand though....
If you are in a Pivot table in Access, there is a button that appears on the
menu bar that allows you to "export to Excel". This button doesn't appear if
you are in Datasheet view, only when you are in Pivot Table view. If you
click that button, it sends your data to excel and you get one sheet of the
data and one sheet of the pivot table of that data. Sooo... if there is a
button that can do this, there must be a corresponding command that can be
used to do the same thing in a macro.
I wish I could paste in a copy of the button... it's the Excel symbol with a
little pencil under it... again, only when you are in an Access Pivot table.
I am using Access 2003 if that makes a difference.
Thanks
 
hi again,
yes 2003 makes a big difference. i'm still stuck on 2k
where i work. you are talking about things i don't know
about. nice things. wish i had them.
you might be able to "create" a pivot table view with a
macro. it would be doing the formatting to create the
view. but the original assesment still holds. all Access
is doing is downloading data. no fancy.
sorry.
Regards
Frank
 
Back
Top