Exporting Pivot Table query to excel with specific file name and directory location

[08211987]

Hi,
I have created a query with a Pivot table and am trying to export to excel with a specific file name to a specific directory. I coded this to export the file without a specific name or location and it works great:

DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit
DoCmd.RunCommand acCmdPivotTableExportToExcel, report_dir & FileName & timeStamp & ".xlsx", False, ""
DoCmd.Close acQuery, "qry_Private_Brands_Pivot"

But I couldn't get the RunCommand to allow a specific file name and location. So I replaced the runcommand with this TransferSpreadsheet statement and it gave me the error "A value you entered for the Transfer Type argument is invalid". (the report_dir and timestamp are already defined).

FileName = "PDF_ASSETS_Private_Brands_Pivot_"
DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel, 10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, ""

Is there a different way to accomplish this?
Thanks!
C

 

[08211987]

Should I be using a Pivot Table form instead of a query?

 

[PHV]

what about this ?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Thanks for responding PV,
I tried what you suggested and it just processed the statement very quickly and nothing was produced.

So far these are all the statements I have tried, I have commented many of them and only the first and last one work.

DoCmd.OpenQuery "qry_Private_Brands_Pivot", acViewPivotTable, acEdit

'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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", True

'DoCmd.TransferSpreadsheet acExport, 10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, ""

DoCmd.TransferSpreadsheet acOutputQuery, "qry_Private_Brands_Pivot", acFormatXLS, report_dir & FileName & timeStamp & ".xlsx", False, ""

DoCmd.Close acQuery, "qry_Private_Brands_Pivot"

I can see the Access Pivot table in access when I close the code window.

This DB has exported other regular queries before so I think it must just the pivot export.

Thanks,
C

 

[rekrabnai]

Could you post the EXACT values you have for the 'report_dir' and 'timestamp'

Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!

 

[08211987]

report_dir = "\\msfs05.lowes.com\DATA1\SHARE\merchnet\PCM Reporting\PDF_Digital_Asset_Metric_Report\PDF_Collection_Stats\"
FileName = "PDF_ASSETS_Private_Brands_Detail_"
timeStamp = 9-11-2012

Just to confirm the statements I put in previous post:
1st - creates the pivot table and it is displayed in access
2nd - (if I remove everything after the 1st comma) it opens excel and contains pivot table in 1st sheet but I have to manually save
2nd - if I leave as is it gives compile error "wrong number or arguments or property assignments"
None of the transferspreadsheets do anything, no errors and no processing
I also added this statement and it just exports the pivot detail data to excel:
DoCmd.TransferSpreadsheet acOutputQuery, 10, "qry_Private_Brands_Pivot", report_dir & FileName & timeStamp & ".xlsx", False, ""

Thanks,
C

 

[08211987]

I've decided to code VBA that would export the detail data to excel into a predefined template and manipulate it from there such as have a sheet with the pivot table that is refreshed and a formatted sheet that the detailed unformatted data is copied into.
Thanks to those who tried to help.
C