Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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
0
skyrise11
Asked:
skyrise11
  • 4
  • 4
1 Solution
 
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
 
shanesuebsahakarnCommented:
Hmm - I haven't tried it, but you could try the OutputTo command:

DoCmd.OutputTo, acOutputForm, "MyForm",acFormatXLS, "C:\MyExcelFile.xls", True
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Independent Software Vendors: 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!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now