Toad Data Point Export to Excel Error - The exported data cannot be saved when the file is in use

We have Office 365 and Toad v5.6. When my Toad automation job runs, whether it's in the "Administrator" console as a scheduled job, or if I am running it within the designer, when the data is exporting to Excel, I receive multiple popup message boxes that say "The exported data cannot be saved when the file is in use. Please, close all programs which are using the document." with a "Retry" and "Abort" button. If I click the "Retry" button, the job continues on to completion and other times, I am presented with that same dialog box multiple times. Sometimes the jobs run fine without this error and other times, I get that dialog box. It does not trigger any errors or such, only that the job is sitting in "limbo" waiting for you to click the "Retry" button. We just started noticing this happening since we moved away from a desktop version of the Office product (Office 2016) to a cloud based "Office 365" version.

I have also noticed this if I am exporting data using a Loop routine where it is exporting data to multiple sheets within the same Excel file. For instance, if my loop routine is to create 12 sheets, it may present that dialog box when it gets to one sheet that has 0 rows of data, or not. But other times, it will run through perfectly fine, without that dialog box popping up.

I my mind, I am thinking either the Toad process is somehow "saving" the Excel file behind the scenes internally, and this "save" is not finishing in time when it gets to the next loop to start Exporting to the next sheet of data to the same Excel file, so it thinks "the file is in use". Also, these Excel files are being created on our Network drive, which sometimes there is a lag in the time it takes to save the file to a Network drive versus directly to our PC (i.e.: C:\ drive folder).

In thinking this, I have put a "Pause" step of 5 seconds in between each loop, thinking maybe this will give it time to complete the "Save", however, that hasn't proven to work 100% of the time either.

If this hasn't gone to Quest Support, then I'm with you, in that my best guess would be that latency is causing this issue, and possibly the cause for your other issue that you describe in another post, especially since you mention that it's all been happening since you moved to Office365.

I'm guessing that network responsiveness between your org (your workstation) and Office 365 servers is not nearly as consistent as when Office was installed on your workstation locally.

I would open up a Support ticket any way with Quest, to see if any of the Support engineers have seen other customers with this experience. Even if not, Quest can document this issue/condition and the dev teams can do some diligence to see if there is a solution.

Something else to consider here is, I even created a simple automation task that only has a looping routine in it with 12 steps in it where each step through the loop would export 5 rows of data onto the same sheet, but at a different Row/Column. So the export would be really fast and that too would throw the error. Though not everytime, as sometimes it would actually complete the 12 loops and create the file just fine, and other times through the loop it would throw that error. This is where I am thinking that after each Loop, Toad is somehow "saving" the file behind the scenes and thus maybe the file hasn't fully completed before it moves onto the next step in the Loop?

I don't have the ability to open a support ticket on behalf of our company, but I have contacted our internal support department but that is not producing any results as this has been going on for some time, and now this error message is happening more frequently to where I get that dialog box sometimes 8 to 10 times while it's exporting data on the same Excel sheet. How do I get Quest to document this, like you mentioned?

Another thing I have noticed is, if when my process is looping through dumping the data onto each of the Excel worksheets, if there are NO records to export during that loop, I find that I get that message alot of times. Again, this goes back to thinking that behind the scenes, Toad is saving the file each time through the loop and since there are 0 records to export, it's not giving Excel enough time to save the file when it gets to the next loop so it thinks the "file is in use".

Getting this popup message more frequently, it is becoming a major problem with the automated jobs. In this particular job, it can have 50,000 rows of data being exported, and somewhere along the way of exporting that data, it throws that message box, then if you click the "Retry" button, it continues exporting and then it will throw the message again and again. This message box can appear 3-4 times all while exporting those 50,000 rows of data before it finally moves on to the next part of the automation process where it again will throw that message when it's exporting data onto another sheet.

This is becoming a major pain, because now these jobs have to be "babysat" in case that message pops up so someone can click the "Retry" button. These are automated jobs that run at 5 and 6am, and now for someone to have to watch these jobs manually, is a big problem. Again, this NEVER happened prior to us moving to Office 365, from Office 2016.