PDA

View Full Version : Open File for Output hangs



mgs73
02-07-2017, 04:32 PM
I have a spreadsheet that runs as a scheduled task every five minutes (Windows 7 Excel 2010). For the most part, it works really well, but after a few days it stops working for some reason. I have error handling in the code, but no error is evident in the logs - it simply hangs.

I have isolated where it hangs to the following line of code:


Open FilePath for Output as #FNum

FilePath is a valid name on the local hard drive (C:\Temp\Extract.bat), and #FNum has been set to Freefile

After upgrading to Windows 7 a few years ago, I remember getting a problem similar to this except it would crash with an error message rather than just hang. I added a loop to the code to try again, and that would usually fix it. Now, I am at a loss what to do!

A related issue is that after Excel hangs, the scheduled task is terminated after a two minute timeout (the task normally takes around 20 seconds to run). When the task is terminated, however, Excel remains open for some reason, preventing the file from being opened again when the task is next scheduled. This means that until I kill the Excel process in Task Manager, the process won't run.

Anyone have some advice on how to fix or workaround this?

Kenneth Hobs
02-08-2017, 07:35 AM
Welcome to the forum!

You may want to use a Shell and Wait sort of routine if you used Shell() to execute the BAT file. http://www.cpearson.com/excel/ShellAndWait.aspx

Creating many text files can be an issue sometimes too. Use something like CCleaner.exe every so often to clean up temp files. The path c:\temp is likely not your Environ("temp") path so you may have do your own cleanup.