Saving Export Pivot Table from Access to Excel as a specific name

[Marisol2]

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 45 queries. I don't want run and save each query.

When I created a macro to export the queries using 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.

 

[ddcroit]

Marisol,

I would suggest to use some code to transfer the data to Excel and at that point create your Pivot Table.

This is how it works:

1- Create an object variable (Dao.Recordset) that contains the data from a particular query.

Dim DB as Dao.Database
Set DB = currentdb

Dim ThisRset as Dao.Recordset
Set ThisRset = DB.OpenRecordset("MyQuery")

2- Create an Excel app

dim xl as excel.application
set xl = Excel.application

xl.workbooks.add
xl.range("a1").copyfromRecordset ThisRset
xl.rows(1).insert

'Put the range names

xl.range("a1").value = "field1"

'Continue to put field names

3- Create Pivot Table

I suggest to use the Recorder. Please take into account to change the source data to "range("a1").currentregion.address"

4- There you go. Remember to create a Library reference to Excel from Tools / References

Please try this and let me know if this isn't clear enough or didn't work.