PivotTableExportToExcel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This seems to work fine however does not seem to work in VBA when the macro
is converted. I also cannot find referrence to it in the object browser.
Nor does there seem to be any information in the help files. Can this
command be run from VBA or does it need to be run in Macro format?

Any ideas?
 
Redapples said:
This seems to work fine however does not seem to work in VBA when the macro
is converted. I also cannot find referrence to it in the object browser.
Nor does there seem to be any information in the help files. Can this
command be run from VBA or does it need to be run in Macro format?
Click to expand...

In VBA use DoCmd.TransferSpreadsheet method. Read the Access VBA help
article on TransferSpreadhseet for more info.
 
Thanks for replying to the post.
The TransferSpreadsheet performs a different function to the
PivotTableExportToExcel command and as such the results are different. The
TransferSpreadsheet output is not a pivot table out put but will output the
query as a data grid view. I already use this function for other queries in
the same application. Unless you know different in which case can you post
the code?


Anyone else got any ideas.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm using Access 2002 and cannot find any reference to a
PivotTableExportToExcel method. Which version of Access are you using?
Are you sure this method is a built-in method and not a user-defined
function?

If you create a cross-tab query (aka pivot table) you can export that
query to a spread sheet using the TransferSpreadsheet method. Please
read the Access VBA Help article "TransferSpreadsheet Method" for more
info.


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVMQDoechKqOuFEgEQJVSACgrssIX8QdyvhrNNki5OOgEztxtoMAoKho
LoEdTwu5UHvhkLBEWBReTTsd
=xiTC
-----END PGP SIGNATURE-----
 
I'm using 2002 also. It is a built-in method. to replicate what I am
describing open a select query click on the view menu and select PivotTable
view. Works for forms too. If you look at the toolbar in this view you will
see the command I'm refering to. This can, as I posted be acctivated from a
macro but no from VBA so far as I can work out. The export to Excel has
different fuctionality in Excel than a crosstab query in the method you
describe.

Once again thaks for posting.
 
FYI from access help files in file "Export data or database objects" under
heading Export a PivotTable view to Excel.


Use this procedure to export a PivotTable view of a datasheet or form to
Microsoft Excel as an interactive PivotTable report. The link between the
Excel PivotTable report and the source data is maintained.

For best results, either move all fields out of the detail area or hide
detail data for items before you export the PivotTable view to Excel.

On the PivotTable toolbar, click Export to Microsoft Excel to run or switch
to Excel and display the data in a PivotTable report.

Note Because PivotTable reports in Excel cannot display some PivotTable
view layouts and calculations, the PivotTable report might look different
from the PivotTable view. For information about working with Excel PivotTable
reports and adding fields to them, see Excel Help.
 
Back
Top