P
pv
I am in need of help with exporting queries from Access 03 to Excel. I am
familiar with the transfer spreadsheet command using
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query6",
"testquery.xls". query 6 is the query that I would like to export and the
default view of the query has been set to Pivot Table. When the export runs
the raw data in standard datasheet view is exported . I am hoping to be
able to export the query as set up in the pivot table view.
In most other applications I will drive excel to create the pivot table once
the data has been exported. In this case there are many pivot tables to be
updated and refreshing data has become a very manual process due to the
design of the pivot tables within the workbook. Many pivot tables on a
single worksheet etc. It has also become a huge load on excel to run/refresh
save work.
I have searched the forums and the web and have it seems that there has not
been a response that has gotten to a successful resolution. Many are close,
but no cigar. I have found the command acCmdPivotTableExportToExcel, but am
having trouble the implementation. Will this do what I need? Is it possible
to export a query pivot table view to excel?
Please note, crosstab queries only get me so far. I need to show subtotal
and grand totals along with details in a single view. This view will then
been used in power point presentations. If there is a way to write access
reports to power point, please show me the light. What I have been doing up
until now is writing one query to arrive at the details, writing a second
query for subtotal and totals, then combining the results in a union query.
Any suggestions are very welcome.
Thank you in advance for your help!
I hope my post is clear. It has been a long day.
familiar with the transfer spreadsheet command using
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query6",
"testquery.xls". query 6 is the query that I would like to export and the
default view of the query has been set to Pivot Table. When the export runs
the raw data in standard datasheet view is exported . I am hoping to be
able to export the query as set up in the pivot table view.
In most other applications I will drive excel to create the pivot table once
the data has been exported. In this case there are many pivot tables to be
updated and refreshing data has become a very manual process due to the
design of the pivot tables within the workbook. Many pivot tables on a
single worksheet etc. It has also become a huge load on excel to run/refresh
save work.
I have searched the forums and the web and have it seems that there has not
been a response that has gotten to a successful resolution. Many are close,
but no cigar. I have found the command acCmdPivotTableExportToExcel, but am
having trouble the implementation. Will this do what I need? Is it possible
to export a query pivot table view to excel?
Please note, crosstab queries only get me so far. I need to show subtotal
and grand totals along with details in a single view. This view will then
been used in power point presentations. If there is a way to write access
reports to power point, please show me the light. What I have been doing up
until now is writing one query to arrive at the details, writing a second
query for subtotal and totals, then combining the results in a union query.
Any suggestions are very welcome.
Thank you in advance for your help!
I hope my post is clear. It has been a long day.