Excel: Auto open, refresh pivot, save and close
Posted on 2011-10-11
I have dozens of Excel Pivot Table spreadsheets that are all linked to SQL views/tables. The SQL data is updated daily in most cases via automated SSIS packages. Once the data in SQL (SQL2008R2 by the way) has been updated, I then need to refresh my pivot table and distribute via FTP server. I know I can set the pivot table to auto-refresh on opening, but is there a relatively straight fwd way to automate the full process? I don't want to set the pivot table to auto-refresh b/c when the user opens the file I don't want it to try to refresh. I know they can just ignore the error but it's kind of a pain. Also, I don't want to have to manually open and save the Excel file.
In the past, I have written a auto-open macro in a seperate excel workbook that opens the target Excel workbook, refreshs, saves and closes both the target and the macro conatining workbook. Then, as the last step of the SQL SSIS package I simply open the macro workbook. This does work, but tends to be a bit finicky. At times I have several sessions of Excel open at the same time and then the quit command no longer works and eventually the whole process fails untill I reboot.
Got any better solutions? I'm using Excel 2007 typically, but sometimes 2003 as well for some users.