Thank you so much for your help earlier. I decided in the meantime to try and do an export template whereby the data was exported from my table into excel and then on Sheet2 I built a pivot table based off of the data.
In automation I tried to utilize the export of the same table to the same .xlsx filename and just had it go to Excel and point to the data worksheet, begin at column a(1) and clear the prior worksheet data (rows will often change depending on the day).
The export completes successfully and the correct worksheet is updated with new data, however on Sheet 2 the pivot table is reduced to simple text and is no longer functioning as a pivot. Has anyone else seen this occur? I would have expected the pivot table to have just updated with the new source information since it was pointed to the same sheet and range.
There seems to be some refresh options for both linked pivots and regular pivots in the Excel help file. I found this but have not check it out.
Debbie
Automatically refresh data when a workbook is opened
You can refresh an external data range automatically when you open the workbook, and optionally save the workbook without saving the external data, so that the workbook file size is reduced.
Click a cell in the external data range.
On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.
[cid:image001.gif@01CAB155.7D7DBF90]
Click the Usage tab.
Select the Refresh data on file open check box.
If you want to save the workbook with the query definition but without the external data, select the Remove external data from query table before saving worksheet check box.
Note To refresh data when the workbook is opened for a PivotTable report, you can also use the Refresh data when opening the file check box under the PivotTable Data section on the Data tab of the PivotTable Options dialog box. For more information, see PivotTable options.
Thanks for your help just got back in the office and tried it out and made one small change the the export template as I changed the file name from .xlsx to the old .xls and it worked.
Might there be a bug in the implementation of support for .xlsx in the beta? When the export of the data is done in a .xls the pivot is fine when I refresh, however for .xlsx the data is new but the pivot ceases to exist and only individual cells are left with the prior pivot data.
There are some existing limitations with 2007 and the third party tool we use for this. I know it does not update Charts but didn’t know about Pivots. There is a CR on the Excel 2007 Chart. I will make sure it includes getting the Pivot to work.
Thank you so much. I know I have had a lot of things to ask today.
Also noticed one other limitation for exportation to .xlsx and that is with hidden worksheets. Often times I like to hide the source data just for clarity by hiding the worksheet and under the .xls format the data export works correctly and everything updates to the hidden worksheet, however when exporting under .xlsx an error messages advises that the sheet must be visible.
I am having related issues in dealing with pivot charts for export. I have been recently upgraded to winows 2007 and I have been using the 3.1 version of Toad for Data Analysts. For the last 1 I have had a automation script that exported to Very Hidden tabs in a XLSM spreadsheet and the workbook contaned 6 pages of Pivot tables that have auto refreshed. The pivotable have had mixed calculation and sumations of data for example sum of one item count of another and min of another and a calculation of yet another. The issue I am having is now the pivot tables when auto update are all chaning to one type of summation which is sometimes count and sometimes sum.
Can some one let me know of a fix or is this a future upgrade if so please hurry.
