CODEWITHSUNDEEP
excelvba
Ronald Fitzpatrick
Ronald Fitzpatrick

Reputation: 1

Excel 2010/2013 intermittently pauses when saving workbooks via VBA

I've had an intermittent problem for the last couple years and it's finally gotten to the point where I really need to try tracking down a solution.

I'm running a VBA script that takes various csv files that are spit out over the course of the day and chops them up for use in various other processes.

Every 20 minutes it opens a workbook with a 'Workbook_Open' block that does the work. When it finishes the job it closes the Excel application. It does this all day, every day.

MOST of the time this goes off without a hitch. OCCASIONALLY, it will hang up as it's trying to save a workbook. What I see when I catch it is the "Save as" box with the file path/name and the cancel button. It just waits there until someone clicks "Cancel" or the system fails from trying to open Excel too many times.

If I catch it and click the button then it will proceed on as if nothing abnormal had happened. Otherwise it just waits there for the user to click the button, as if it were any other msgbox/inputbox.

The weird thing is that the file is saved regardless of whether or not I click "Cancel" or kill the application or whatever. There doesn't seem to be any pattern to when it happens.

I've tried searching for similar issues but all my searches inevitably come up with results relating to system/application crashes. This isn't that. Excel will continue as normal, even to the point of continuing to save files in the way it SHOULD behave, if I click cancel.

Help?

Upvotes: 0

Views: 768

Answers (1)

John Morrill
John Morrill

Reputation: 21

I solved this problem by adding calls to DoEvents before and after the .SAVEAS or .SAVE calls. It's just two days, but this seems to have taken a process that I run daily multiple times a day (that creates multiple workbooks to a shared drive and never got through a day without this bug happening) to being a perfectly hands off process.

Upvotes: 2

Related Questions