I used the video here to try to run a query, paste those results into an excel template. The excel template has 2 tabs (worksheets) only, the first one is the summary tab with 4 pivot tables and two graphs and the second tab (worksheet) with all the data from the query.
From the video, I have been able to get the results of the query to paste onto the second tab and then have the data reload/refresh when the user opens the file.
One glitch I am running into though is with the formatting. My template was set to update upon opening the file and each pivot table was set to NOT autofit column widths on update as I had set them at a specific width. This setting is being overwritten the data is copied over with the new data.
Is there a way to keep these settings in tact? If not, my graphs get all screwy and all the columns change size as well.
I'm not sure if you are talking about the column size in pivot grid on first tab or the data on second tab. If it is on the first one, it is updated by your Excel template and you need to refine the macro code there. If you are talking about the data grid which was exported by TDP, then you can create an export query export template and deselect auto fit column box and use it in your automation as export wizard activity.
Are the pivot tables Excel pivot tables or Toad pivot grids? The way that I deal with pivot table column sizing is to create a macro enabled workbook and then add VBA/macros to resize the columns. If you create a macro, you can go to the VBA window, double click This Workbook, and then paste your macro into the Workbook_Open sub. The macro will run when the workbook is opened. You could also include code here to refresh the pivot table. As another step to fully automating your report, you could create an Excel data connection to your data source and either paste the query into the Command Text box on the Definition tab of Connection Properties, or you could create a stored procedure out of your query and execute this when opening the file. Here is a link to an article: https://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/
Let me know if you have any other questions about this.
