CODEWITHSUNDEEP
excelvba
Nick van H.
Nick van H.

Reputation: 388

How to cope with "saving" dialog remaining open?

I have code to save a workbook;

wb.SaveAs sTmp, FileFormat:=51

where sTmp is the complete valid file path (including .xlsx at the end) and fileformat 51 is .xlsx.
The new workbook is stored in a network location. (This might be part of the problem but I can't find anything related.)

This code runs smoothly 9 out of 10 times. Sometimes the "saving" dialog box remains open, even though the file has been saved, causing the code to hang on the SaveAs line.
If I click the cancel button the code continues and I can open the file.

Is there any time-out function if a line of code takes too long to execute? (Googling for time out returns user inactivity questions or SQL related problems)

Update;

Generating the string is not the problem, sTmp is a validated string. If the string (or any other input for that matter) would be wrong or the specified path/file name would be wrong, doesn't exist, has to be replaced or contains illegal characters VBA will throw an error.

To clarify; when executing this line the workbook is actually saved. Excel pops up a dialog box "saving workbook to (path)" with a progress bar. This pop up won't go away and the code hangs on above line, as if it is waiting for a hand shake. When the progress bar is killed (i.e. pressing the cancel button) the code resumes as normal.

Upvotes: 1

Views: 1982

Answers (1)

Jason
Jason

Reputation: 11

I had this error show up after a small code modification. It was the use of the Kill function.

After substituting the following code, the issue no longer occurred.

With New FileSystemObject
    If .FileExists(yourFilePath) Then
        .DeleteFile yourFilepath
    End If
End With

Upvotes: 1

Related Questions