automation of a query script to multible tabs in excel

Toad Data Point
1

is it possible if i can return the results of a query as part of an automation script into one same excel file but each time into a seperate tab or worksheet of file

please advise thank you
juman

2

Yes. If you use the Select To File activity and use a script that has several sql, each sql result will be sent to a new worksheet.

Debbie

3

Thank you very much it worked
juman

4

Hey Debbie,

and further more on this, it did work but i need one more thing is to name relevant tabs in Excel before i send email of this attached file
Is it possible?
please advise thank you

5

Mnnnn…that’s different. I just added yesterday the ability to name a new worksheet but that won’t help in your case as you have multiple sheets. You could do one of two things.

SOLUTION A:

  1. First create a template. Name the blank worksheet tabs what you want.
  2. Add a Copy file activity to the top of your script that makes a copy of your template to use for this export. (This preserves your template)
  3. In the Select to File actition under options point to a copy of the template. When you do this it will list the named worksheets. Select the named worksheet you want to export to. (See screenshot)
  4. The problem with this solution is that BOTH SQL will go into the same named workseet. So go back and separate out your SQL into two scripts and add a separate Select to File to enter the SQL into the second tab.

SOLTUION B:

Do steps 1 - 4 above. But instead of putting the SQL into a separate tab change the cell and row location of where the result set goes. This way you can put the SQL at the top on the same sheet or at the bottom of the result set (assuming the number of rows does not chage much.

Debbie
NamedWorsheet.png

6

Thank you very you have been of a great help i tried first approach with some hickps but it is working
:slight_smile: juman

7

Sounds good. Because of your posting this morning I added the ability to use a bind variable in the worksheet name. Now you will be able to dynamically name the worksheet if you put the Select To File in a while loop. This will be in next week’s Beta if you use Beta builds.

Anyway, glad we could help each other.

Debbie

P.S. If you want to explain the hiccups I would be happy to look.
P.P.S. Not sure when the export of SQL statement will be done as another developer does this.