VBA Saving Dialog Box is not closing

[camillaEne]

Hi,

I have a VBA code that loops through different files, making changes in them and then saving the files. When saving some of the larger files, I have a problem that the Saving dialog box will not close.
I save the following way:

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

wb.SaveAs filepath, ConflictResolution:=xlLocalSessionChanges
wb.Close SaveChanges:=False

The file is saved locally on my computer, but this saving dialog box do not close (see picture). I need to press cancel in order for the code to proceed with the next file. Can I force it to close in my code? Or how to deal with this problem?

Thank you very much for helping!!

 

[rlv01]

You say your code loops through multiple files. I find that when opening and saving/closing multiple files it is important to have a DoEvents statement somewhere to allow the Excel 'housekeeping' to keep up. You might try this as a test.

    DoEvents
    WB.SaveAs Filename:=filepath, ConflictResolution:=xlLocalSessionChanges, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    DoEvents
    WB.Close SaveChanges:=False

(note that I am guessing about the FileFormat based on what I see in the dialog that you posted)

 

[camillaEne]

You say your code loops through multiple files. I find that when opening and saving/closing multiple files it is important to have a DoEvents statement somewhere to allow the Excel 'housekeeping' to keep up. You might try this as a test.

    DoEvents
    WB.SaveAs Filename:=filepath, ConflictResolution:=xlLocalSessionChanges, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    DoEvents
    WB.Close SaveChanges:=False

(note that I am guessing about the FileFormat based on what I see in the dialog that you posted)

Thank you for this tip! The code is running every morning - and after implementing it, all files were saved and closed this morning

 

[camillaEne]

Unfortunatly this did not solve my problem. Sometimes after making changes in some of the large files, the dialog box is not closing down. Any other suggestions will be very appreciated!

 

[rlv01]

How many files is this macro looping through and saving at any one time? 10? 100, 1000?
Does the dialog refuse to close on the first large file, or does it only begin to happen after the nth file? (n = ?)

 

[camillaEne]

How many files is this macro looping through and saving at any one time? 10? 100, 1000?
Does the dialog refuse to close on the first large file, or does it only begin to happen after the nth file? (n = ?)

Sorry I did not see your reply. It loops through 12 files. It refuse to close on the first large file. I have tried to change the order of the files, but it appear to only be a problem with the large onces. Do you know a way of solving the problem? Thank you!!!

 

[Alex Blakenburg]

I know very little about this and don't have anything I can test it on but I can see you seem to be running out of things to try.
I assume you are using SaveAs because you don't want to change your original file, if that is the case does it make any difference if when you open the workbook you open it as ReadOnly ?
ie Workbooks.Open(Filename:=filepath, ReadOnly:=True)
It doesn't stop you updating the file it just stops you saving with the original name which you don't want to do anyway.

 

[rlv01]

Can you give us an idea of how large the problem file is?

As far as further suggestions, @Alex Blakenburg's idea is a good one. Here are a couple more

wb.SaveAs filepath, ConflictResolution:=xlLocalSessionChanges

I am a bit suspicious of the ConflictResolution parameter above as it implies that it needs to go off and check things elsewhere. So maybe as an experiment, delete it and see if it makes a difference.

    DoEvents
    WB.SaveAs Filename:=filepath,  FileFormat:=xlOpenXMLWorkbookMacroEnabled
    DoEvents
    WB.Close SaveChanges:=False

The other thing you could do purely as an experiment is to add a really long time delay to see if the time needed for file operations on a really large file are the problem.

Start with something ridiculously long, like 10 or 15 seconds.

    WB.SaveAs Filename:=filepath, ConflictResolution:=xlLocalSessionChanges, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.Wait(Now + TimeValue("0:00:15"))
    WB.Close SaveChanges:=False

 

[camillaEne]

I know very little about this and don't have anything I can test it on but I can see you seem to be running out of things to try.
I assume you are using SaveAs because you don't want to change your original file, if that is the case does it make any difference if when you open the workbook you open it as ReadOnly ?
ie Workbooks.Open(Filename:=filepath, ReadOnly:=True)
It doesn't stop you updating the file it just stops you saving with the original name which you don't want to do anyway.

Hi. I do just change the original file, but when I used just "wb.save ..." this did not work better. Is this the solution you suggest?

 

[camillaEne]

Can you give us an idea of how large the problem file is?

As far as further suggestions, @Alex Blakenburg's idea is a good one. Here are a couple more

wb.SaveAs filepath, ConflictResolution:=xlLocalSessionChanges

I am a bit suspicious of the ConflictResolution parameter above as it implies that it needs to go off and check things elsewhere. So maybe as an experiment, delete it and see if it makes a difference.

    DoEvents
    WB.SaveAs Filename:=filepath,  FileFormat:=xlOpenXMLWorkbookMacroEnabled
    DoEvents
    WB.Close SaveChanges:=False

The other thing you could do purely as an experiment is to add a really long time delay to see if the time needed for file operations on a really large file are the problem.

Start with something ridiculously long, like 10 or 15 seconds.

    WB.SaveAs Filename:=filepath, ConflictResolution:=xlLocalSessionChanges, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.Wait(Now + TimeValue("0:00:15"))
    WB.Close SaveChanges:=False

The large file is 8 MB, so not even that large. The smaller onces that do not cause problems are around 1-2 MB.

Thank you for your suggestions. I also started out without the ConflictResolution, but added it as a test. But I will try to delete it.

And make sense with the Application.Wait --> but I am wondering if the code will ever jump to this line if it is stucked in the "Wb.SaveAs", as the dialog box is not closing down?

Thank you for helping!