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?
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.
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.
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?
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:
Run a Select in the Editor with F9.
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.
Left Excel running and went back to Toad
Entered a different Select in the Editor with F9
Rt-click and choose "Export Dataset". Export using the "Excel Instance" format again
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.
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.
