runcommand accmdpivottableexport and provide file name with directory

[surfside1]

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:

DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit
    
DoCmd.RunCommand acCmdPivotTableExportToExcel

'DoCmd.RunCommand acCmdPivotTableExportToExcel, report_dir & FileName & timeStamp & ".xlsx", False, ""
    
'DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel ', "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, ""
    
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", True
    
DoCmd.Close acQuery, "qry_Private_Brands_Pivot"

Any help would be appreciated.
Thanks,
Surfside

[twinnyfo]

surfside1,

Try this:

DoCmd.OutputTo acOutputQuery, "qry_Private_Brands_Pivot", acFormatXLSX, report_dir & FileName & timeStamp & ".xlsx", False

This will at least get the file saved for you....

[Seth Schrock]

I believe that the argument would be acFormatXLSX, as I have used that in some of my databases.

[twinnyfo]

Seth,

BRILLIANT! So simple...... Thanks--now I can download to my current format!

[zmbd]

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 "acSpreadsheetT ypeExcel10" 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

[surfside1]

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

The DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit

- creates the Pivot table and it displays it in Access.

The

DoCmd.RunCommand acCmdPivotTableExportToExcel

- Opens excel and exports the pivot table to the spreadsheet but I have to save it manually to a directory.

The

'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:

'DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel ', "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, "" 
  
'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

[surfside1]

Sorry I didn't realize that, thanks.

[zmbd]

: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. "acCmdPivotTabl eExportToExcel" is not a valid parameter for this method.
Line 3: syntax error. "acSpreadsheetT ypeExcel10" 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