I would like to know (if its possible) how to export multiple query results into multiple excel tabs of the same excel workbook. I understand how to do this with a single query and how to name the excel worksheet (tab). However, I need to be able to create a spreadsheet that contains different worksheets (tabs) that contain results from different queries. This is a report that will be generated each month so the report would have the same name each month with the date appended to the end. Could someone please tell me if TDP can do this and how please?
Hi BigSky,
It is possible. I have multiple projects that do this & run daily.
What I did was I created an empty workbook with the different tab names which I refer to as a "template". I store in folder called Templates & access in the the TDP project.
In my TDP project, the first thing I do is use the Copy File Activity (I named it CopyTemplate) & copy the template to my runtime results file(FilenameTemplate.xlsx to FilenameResults.xlsx) located in the same folder as the TDP projects (*.tas).
I then export all my queries (Execute Scripts Activity) that are executed/exported to the appropriate tabs/row/columns in my runtime file (FilenameResults.xlsx) .
In the 2nd to the last activity of the projectI use another Copy File Activity I called DateStampFinal using the same name but appending the copy file suffix with the Datetime function. I store it in a History folder using the Copy to folder activity input within that activity for my historical runs/audit history.
In the last activity of the project, I use the Send Email Activity where it emails to the appropriate party of the DateStampFinal version of the .xlsx along with the log file.
I use the automation feature to schedule the execution of the project on the Windows Task Scheduler.
Hope this helps!
Eric
1 Like
Great example, Eric!
When you want to write into an existing excel spreadsheet, make sure you uncheck the "Overwrite" option on your export options. If you have the 'Overwrite' checked then this action will always overwrite the whole file, but if you uncheck that option then you can choose to append the data to an existing tab (provided a copy of the file exists when you are setting up the automation so that Toad Data Point can open the file and read the names of the existing tabs) or you can choose to "Append worksheet" to whatever is in that Excel file and you can give the new Worksheet a name here. Hopefully the screen shot below helps. If you are still having trouble, you can DM me for help.
How can this be done in Toad Workbook? I have 2 separate workflows, and want Workbook1 to output to Sheet1, and Workbook2 to output to Sheet2 in the same Excel file. However Toad Workbook throws the error 'There is already a report called 'xxxxxx' in this Workflow. Please use a different name.'