By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

runcommand accmdpivottableexport and provide file name with directory

P: 3
Hi,
I have a Pivot table query in access 2007 and I want to export it to excel and provide a directory location and file name.

I've tried all of these statements below and the only ones that work are the ones that are not commented and I am not getting the spreadsheet saved as a file on a directory with the file name I want to use:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit
  2.  
  3. DoCmd.RunCommand acCmdPivotTableExportToExcel
  4.  
  5. 'DoCmd.RunCommand acCmdPivotTableExportToExcel, report_dir & FileName & timeStamp & ".xlsx", False, ""
  6.  
  7. 'DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel ', "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, ""
  8.  
  9. 'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", True
  10.  
  11. DoCmd.Close acQuery, "qry_Private_Brands_Pivot"
Any help would be appreciated.
Thanks,
Surfside
Sep 6 '12 #1
Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,045
surfside1,

Try this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputQuery, "qry_Private_Brands_Pivot", acFormatXLSX, report_dir & FileName & timeStamp & ".xlsx", False
  2.  
This will at least get the file saved for you....
Sep 7 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,931
I believe that the argument would be acFormatXLSX, as I have used that in some of my databases.
Sep 7 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,045
Seth,

BRILLIANT! So simple...... Thanks--now I can download to my current format!
Sep 7 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
I am not getting the spreadsheet saved as a file on a directory with the file name I want to use:
This is fairly vague...
What are you not getting: the pivot table, the file etc...

If you're not getting the pivot table, then in what way? Are you wanting the static results or were you wanting the dynamic aspect of the pivot table on the data in the excel workbook?

If it's the file name... make sure the name is valid and that you have "write/modify" privileges to the directory.
Personally, I don't build the string for the filepath in the command structure... makes it very difficult to verify. IMO: Build the string first then use that variable in the right locations.

What, if any errors are you getting (number and desciption please) I'll betcha that "acSpreadsheetTypeExcel10" is causeing you a headache... that's not a defined constant... replace that with just the number 10 and try that transfersheet method. I have a thread where that is discussed in detail.http://bytes.com/topic/access/answer...to-rename-file

-z
Sep 7 '12 #5

P: 3
Thanks for replying, sorry this is vague, I'll try to explain.

Expand|Select|Wrap|Line Numbers
  1. The DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit 
- creates the Pivot table and it displays it in Access.

The
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdPivotTableExportToExcel 
- Opens excel and exports the pivot table to the spreadsheet but I have to save it manually to a directory.

The
Expand|Select|Wrap|Line Numbers
  1. 'DoCmd.RunCommand acCmdPivotTableExportToExcel, report_dir & FileName & timeStamp & ".xlsx", False, "" 
- doesn't even compile - gives an error of wrong number or arguments or property assignments so I guess this is not appropriate for the RunCommand.

The 2 transfer spreadsheets do nothing at all:
Expand|Select|Wrap|Line Numbers
  1. 'DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel ', "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, "" 
  2.  
  3. 'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", True 
And the suggested acOutputQuery by Twinnyfo exports the detail data of the pivot table but not in the form of the pivot table.


I hope this helps explain and is not too wordy that I run you all off.

Thanks,
Surfside
Sep 11 '12 #6

P: 3
Sorry I didn't realize that, thanks.
Sep 11 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
:T.A. harshness on:

First code block... does as designed.
Second code block... does as designed... same as if you used the menu via mouse/keypad
Third code block... syntax error. Same as second code block
Fourth code block...
Line 1: syntax error. "acCmdPivotTableExportToExcel" is not a valid parameter for this method.
Line 3: syntax error. "acSpreadsheetTypeExcel10" is not a enumerated constant. Your string for the file path may also be invalid.

:T.A. harshness off:

Read post 4 in this thread: http://bytes.com/topic/access/answer...te-access-info and follow the link to the method.

I just created three different crosstab queries and exported them using the method without fail; HOWEVER this method does not create a dynamic worksheet, just the results.

If what you are after is a dynamic pivot table within the excel workbook then you have only two options... link to the database from the excel workbook, or export the entire recordset from the database into an excel workbook and either thru automation, vba in that workbook, or by hand create the pivot within the workbook.

-z
Sep 11 '12 #8

Post your reply

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