I am running two queries that automatically load to their specified worksheets with no issues. On the first tab int he worksheet, I have a pivot table / dashboard type setup that are set to refresh on open. When the data updates and emails out, the worksheet is saved on the last tab touched, which is not the pivot table. Is there any way I can get Toad Data Point to leave the last tab saved before the automation runs as the one shown when the work book is open?
If you are familiar with Excel macros you can use a Run Program step in your automation after exporting to the spreadsheet to launch Excel (specify path and \Excel.exe) and on the argument line the path and file name of your macro (path\FileName.xlsm). This will launch Excel and open the macro spreadsheet. If you name the macro Auto_Open it will run when the spreadsheet opens. You can make it refresh the pivot tables (even create the pivot tables if you want), select the tab you want and save the file. Don’t forget to put Application.Quit at the end of your macro so it will return control to your automation job (otherwise your job will sit there waiting until it times out). Personally I like to keep my macro file separate from my xlsx file and have the macro spreadsheet open xlsx sheet and perform the actions on it, save it, close it and then quit.