Saving progress pop-up won't close.

petertenthije

Active Member
Joined
Sep 25, 2012
Messages
300
I have a workbook that is used to calculate and save hourly productivity.
In this file there is a macro that saves the output using <save as>.

This macro has no problem saving the workbook. However, the pop-up box that shows the saving progress is not always closed. Therefor, the next time the report is run there are error messages saying the source file is already blocked. Unfortunately, this seems to be happening randomly. Sometimes the pop-up is closed, sometimes it is not.

I have disabled pop-up messages, but this one still comes through.

I build in a check to make sure that the file is not trying to overwrite a file that is already open on another computer.
* If the macro identifies the file does not exist, then it will save with the specified name.
* If the macro identifies the file already exists, and the file is in use, then it will save with a different name.
* If the macro identifies the file already exists, and the file is not in use, then it will remove the old version before saving.

The file being saved is not excessively large (never more then 100kb).

VBA Code:
Sub Save_morningshift()

    Dim Datum As String
    Datum = Format(Date, "dd/mm/yyyy")
    Tijd = Format(Time, "hhmm")
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Shiftname= "Ochtend"


' Save file
    Savefile_Filename1 = "C:\Foldername\"
    Savefile_Filename2 = "Productiviteit " & Datum & "_" & Shiftname& ".xlsx"
    Savefile_Filename3 = "Productiviteit " & Datum & "_" & Tijd & "_" & Shiftname& ".xlsx"

    If Dir(Savefile_Filename1 & Savefile_Filename2) = "" Then
        ' File does not yet exist, it is possible to make a new file.
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Savefile_Filename1 & Savefile_Filename2
        Workbooks(Savefile_Filename2).Close SaveChanges:=False

    Else
        ' File exists, first check that the file is not blocked
        Set Readonly_check = Workbooks.Open(Savefile_Filename1 & Savefile_Filename2)
        If Readonly_check.ReadOnly Then
            ' File is blocked, save as a new file (Filename3) that includes the time.
                Readonly_check.Close
                Application.DisplayAlerts = False
                ActiveWorkbook.SaveAs Savefile_Filename1 & Savefile_Filename3
                Workbooks(Savefile_Filename3).Close SaveChanges:=False
        Else
            ' File is not blocked, remove existing file and save a new one
                Readonly_check.Close
                Kill Savefile_Filename1 & Savefile_Filename2
                Application.DisplayAlerts = False
                ActiveWorkbook.SaveAs Savefile_Filename1 & Savefile_Filename2
                Workbooks(Savefile_Filename2).Close SaveChanges:=False
        End If
    End If
End sub


I use Excel 2016 on Windows Server 2019
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,408
Messages
5,831,441
Members
430,070
Latest member
Renske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top