473,385 Members | 1,587 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Specifying Directory and File Name on Pivot Table Export from Access query to Excel

I have some queries in Access 2003 db that I have setup to display as pivots. I can go into design pivot table view and click on and then click on Export to Office MicroSoft Excel. The problem is I have about 6 queries that produce 48 reports (based on parameters). I don't want to manually save each pivot query results.

The first macro I created to export the queries I used TransferSpreadsheet but the export results are regular xls spreadsheets-with just the data and not in the pivot table views I designed.

So what I did next was created a macro that opens each query and then did a run command PivotTableExportToExcel. This is all fine and dandy, but I still have to save each Excel file it pops up manually.

I've been unsuccessful at getting Access to behave the same way that TransferSpreadsheet does in where I can specify the file name for each query. Any ideas are welcome. Thank you.
Oct 29 '08 #1
2 4013
Stewart Ross
2,545 Expert Mod 2GB
Hi. We would appreciate it if you do not double-post your questions; you have already asked about this in a thread originated by another user.

As far as I can see there is no way to save the result of performing

DoCmd.RunCmd acCmdPivotTableExportToExcel

which is really just a means to automate a menu selection. I reckon if you need to save the sheets automatically you will need to investigate the use of Excel as an automation server, running appropriate code to save the results. As I have not investigated this in the context of exporting a pivottable I cannot give further guidance on it at this stage.

-Stewart
Oct 29 '08 #2
Thank you. I think. My intentions were innocent as the other question was very similiar (not exact) to what I needed. Plus it was already resolved. I guess I don't understand the harm in asking similiar questions that are in the same vein, but nonetheless I won't do it again. Much apologies!
Oct 29 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: M O J O | last post by:
Hi, (I'm new to XML) I can export data from Access as an XML file (with schema integrated in the XML file). I can read it into a DotNet DataSet, but the schema is not correct that is - I do...
0
by: richi | last post by:
Hello I have a funny one that I'm hoping somebody can help me out with. I have populated my OWC 11 Pivot Table hosted in a browser with XML data and set up the pivot table to disable details...
2
by: LittlePhil via AccessMonster.com | last post by:
Someone please help before i start to cry. I'm trying to export from Access to Excel, then create a new excel sheet with a pivot table to display the data held in columns A:P. I get the error...
1
by: Adu | last post by:
Pivot Tables -------------------------------------------------------------------------------- Hi guys, I have two questions: First Question: I have created a Pivot Table on my Access database....
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
1
by: cut123 | last post by:
I have a query that I would like to export to different Excel documents based on the product. For each type of product I want all pertinent data to export to a different Excel document. There will...
0
by: Clare CAVS | last post by:
I have a table with a lookup column referring to another table . tblRooms has two fields, (Autonumber), and . The column I want to display is the RoomName column. If I have Bound Column = 1,...
2
by: Amin Bardai | last post by:
How about this: I want to export results of an MS-Access query into an existing XLS template using TransferSpreadsheet method? I can create a new XLS file but I just want to append records from...
0
by: sarah2855 | last post by:
Hello, I would like to insert a pivot table in an access report.I was able to create a pivot table chart in Query -> pivot table view but I don't know how to incorporate that in reports. Is this...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.