skyrise11
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
That's odd - are the other three sheets blank? Also, are they part of the same file or a different one?
ASKER
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.
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.
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"
DoCmd.OutputTo, acOutputForm, "MyForm",acFormatXLS, "C:\MyExcelFile.xls", False
FollowHyperlink "C:\MyExcelFile.xls"
ASKER
Its cool, I got it to work.
http://support.microsoft.com/default.aspx?scid=kb;en-us;210288&Product=acc