TOAD Multiple grid export into one excel workbook

Good Morning All,

I have joined the forum after looking high and low across the net and here to find a solution to my query. I have been teased a few times but its never the answer I am looking for. I will however still state my apolgies if this query has been resolved here.

I run a script which outputs 8 grids in Toad for Oracle 10.6 and I would like them all to export over to Excel on seperate sheets, so I would have 8 sheets in 1 workbook. I have managed to do this by using Excel Instance but surely there must be a faster way instead of doing it 1 by 1. Is there a way of doing this within Toad and not using other applications?

a script or option?

Kind Regards

Maz

A similar question was posted yesterday (www.toadworld.com/…/23989.aspx). I put an example of how to potentially do this in Automation Designer.

If your script is just a series of “select * from table/view”, then you can multiselect and rt-click those tables or views in the schema browser, then right-click and choose “Export Dataset”, and choose the “Excel Instance” export format. That will put each table/view on its own sheet in excel.

Thanks guys, I was looking for Toad to do this automatically however it looks like the best Toad can do is output as Excel Instance or use another application. Thanks for the quick response.

I know this thread is old, but the next beta (12.9.0.26) will have a button on the Script Output tab to export all grids to one Excel file.

This thread is now even older but it still pops up at the top of search results in Google, so thank you for updating with this information!

You’re welcome Caitlin. By the way, an option was added somewhat recently (12.10 or 12.11) that allows the “Excel file” export option to add a new sheet to existing files.

So if you wanted to do this by automation, you could:

  • make the 1st action export a query to excel file (to go on the first sheet in the excel file). Choose the “if file exists…overwrite” option.
  • make the 2rd and subsequent actions export another query (to go on the next sheet in the excel file). Choose the “if file exists…add new sheet” option.

I have it and I am still on 11.6

OK, I went back and checked. It’s been there for exporting to MS Access - MDB and ACCDB files - for ages. And also for ages, you could append to a new sheet with the “Excel Instance” export format. But for “Excel File” - the ability to add a sheet to an existing file - that’s new in 12.9.

Hi all. As @JohnDorlon mentioned, exporting to an Excel instance has been a feature for some time and I have used that to create multi-tab spreadsheets based on multiple queries. But when my company switched to Office 365, this no longer worked the way it had. It would create the spreadsheet using the results from the first query and then all subsequent query results would go into tabs in a separate default worksheet. I then have to consolidate the spreadsheets.

So today I've been experimenting with changing output from Excel Instance to the same file with the Add new sheet option. But I'm seeing odd behavior there too. I have 7 queries creating 7 tabs in the spreadsheet. Sometimes it only creates the first tab before getting an error. Sometimes 3 tabs. It's not consistent. The error I'm now getting is "Cannot create file ...". The process cannot access the file because it is being used by another process".

I am using Toad for Oracle version 16.2. Any ideas?

Thanks,
Steve

@sgivens

Hi Steve,

If you are using the "Excel File" format option in Export Dataset, and you have the file open in Excel, that is why you are getting the error. The "Excel File" format cannot write to a file that is currently open.

I have Office 365 with Excel installed locally. This is what I did:

  1. Run a Select in the Editor with F9.
  2. Rt-click and choose "Export Dataset". Export using the "Excel Instance" format. Excel was not running yet. Toad started it and export the dataset to the first sheet.
  3. Left Excel running and went back to Toad
  4. Entered a different Select in the Editor with F9
  5. Rt-click and choose "Export Dataset". Export using the "Excel Instance" format again
  6. Data was exported to a new sheet in the same workbook

Maybe I am doing something a little different than you. If you let me know your exact steps, I'll try again to reproduce the problem that you're having.

Thanks.

John

Hey John,

Does TOAD use an Excel API call for that?

Cheers,
Russ

For Export to Excel instance, yes. We use COM to communicate with Excel.

For Export To Excel File, no. In that case, we create/modify the file ourselves.

Sorry for the delay John, and thanks for looking into this.

Here are the actions I’m doing:

And a couple properties from the steps:

The error:

After the run:

I can run it again and different steps will fail. Once last week, it ran all the way through.

The full filename is T:\Library\Oracle\Audit\Reports\FNB Oracle Audit Rpt (%database% - %server%) %DATEFILE%.xlsx, which is on a network drive. I may try moving this to my C drive and see what happens.

Thanks,

Steve

Testing inclusion of screen shot after change...
Pointing to C drive made no difference.

I see you have "Launch after creation" checked.

That can cause the "cannot create file" error if you

  1. Run the export
  2. Leave Excel running
  3. Run another export to the same file.

Toad can't write to the file if it is open in Excel.

That did the trick! Thanks John.

You're welcome. For next version, I changed the generic "cannot create file" message to this:

image