Pivot Table in Excel from access

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
skyrise11Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
shanesuebsahakarnConnect With a Mentor Commented:
Hmm - I haven't tried it, but you could try the OutputTo command:

DoCmd.OutputTo, acOutputForm, "MyForm",acFormatXLS, "C:\MyExcelFile.xls", True
0
 
shanesuebsahakarnCommented:
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
 
skyrise11Author Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
skyrise11Author Commented:
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
 
shanesuebsahakarnCommented:
That's odd - are the other three sheets blank? Also, are they part of the same file or a different one?
0
 
skyrise11Author Commented:
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
 
shanesuebsahakarnCommented:
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
 
skyrise11Author Commented:
Its cool, I got it to work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.