Solved

Pivot Table in Excel from access

Posted on 2004-09-20
8
297 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now