?
Solved

Pivot Table in Excel from access

Posted on 2004-09-20
8
Medium Priority
?
303 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 2000 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
Technology Partners: 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!

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 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