Solved

Pivot Table in Excel from access

Posted on 2004-09-20
8
302 Views
Last Modified: 2008-01-09
Hi,

I'm used to opening a recordset in pivot table view of a form in access 2000. But, how do I automatically open that pivot table in Excel using VBA, so the users wouldn't have to go to "PivotTable" Menu and click on "Export to Microsoft Excel". And also, how do you automatically add fields to the row or the column fields of the pivot table while you're in Excel, again using vba. Any links will be wonderful.

If this is not an Access topic, let me know.

Thanks
0
Comment
Question by:skyrise11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12107298
Have a look here - this link shows you how to export a query to Excel as well as demonstrating Excel automation from within Access:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210288&Product=acc
0
 

Author Comment

by:skyrise11
ID: 12113357
But how do you actually view the records in a pivot table view in excel automatically.

Its a great link for transfering a record set to excel, but once you're there, how do you automatically (using vba) go from the normal table view into pivot table view?

Thanks
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12113430
Hmm - I haven't tried it, but you could try the OutputTo command:

DoCmd.OutputTo, acOutputForm, "MyForm",acFormatXLS, "C:\MyExcelFile.xls", True
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:skyrise11
ID: 12116242
When doing the DoCMd.outputTo with the code from the link above, the program opens excel with 3 sheets in one window, and then another sheet in another window where the sheet is named "MyForm".

How would you just bring up the first of the 3 sheets, which is the only one that contains data?

It’s easy to delete any one of those 3 sheets and have them not open up, but one of them contains data you need. So, I’m not sure how to not have the additional sheet open up in the separate window, which I think is caused by the arguments to DoCmd.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12116899
That's odd - are the other three sheets blank? Also, are they part of the same file or a different one?
0
 

Author Comment

by:skyrise11
ID: 12117895
Yeah,

Out of the first 3 only one of them has data. They are in a separate file.

And the final one where the sheet is named "MyForm" is also empty and this is in another file.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12117949
Weird - the OutputTo should only be creating one sheet, although I've never tried it on PivotTable view. How about this:

DoCmd.OutputTo, acOutputForm, "MyForm",acFormatXLS, "C:\MyExcelFile.xls", False
FollowHyperlink "C:\MyExcelFile.xls"
0
 

Author Comment

by:skyrise11
ID: 12118713
Its cool, I got it to work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question