Error on Saving...

[Skydyno]

Hi to you all

My first post so be gentle with me....

I'm having a problem that I can't seem to figure out. I'm new to the VBA programming side of excel and have ended up with a nice little spreadsheet to learn with.

On closing my workbook I wish to create a backup of the speadsheet with it's name and the date appended to it in a backup folder. Then save it again using the original name (overwrite) in the original folder. I'd like to control the save myself and not have the prompt for save appear.

I've managed to get the desired results (although I can not stop the save prompt from apprearing) but, on occasion I get an error when saving it..

Run-Time Error 1004 : Method 'SaveAs' of object '_Workbook' failed

​

with the offending line highlighted in debug

Sub Save_Close()

    Application.DisplayAlerts = False
        
    Dim SaveDate As String
    SaveDate = Format(Date, "DD-MM-YYYY")
    
    [COLOR=#008000]'Create the present save location[/COLOR]
    SaveLocation = ThisWorkbook.Path & "\" & ActiveWorkbook.Name
    
    [COLOR=#008000]'Check to see if a backup folder exists and create it if not[/COLOR]
    CheckBackupFolder = ThisWorkbook.Path
    
        If Dir(CheckBackupFolder & "\Backup\") = "" Then
            
            MkDir CheckBackupFolder & "\Backup\"
        
        End If
        
    [COLOR=#008000]'Create the backup path[/COLOR]
    BackupLocation = ThisWorkbook.Path & "\Backup\" & Mid(ActiveWorkbook.Name, 1, InStr(1, ActiveWorkbook.Name, "_") - 1) & "_" & SaveDate & ".xlsm"

    [COLOR=#008000]'Check to see if it's backup day and save in backup folder if true[/COLOR]
    If Weekday(Date) = ActiveWorkbook.Sheets("Input").Range("X66") Then
        ActiveWorkbook.SaveAs Filename:=BackupLocation, FileFormat:=52, CreateBackup:=False
                
    End If

    [COLOR=#008000]'Save Spreadsheet in it's default location[/COLOR]
[COLOR=#b22222]    ActiveWorkbook.SaveAs Filename:=SaveLocation, FileFormat:=52, CreateBackup:=False[/COLOR][COLOR=#0000ff]
[/COLOR]        
    Application.DisplayAlerts = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

End Sub

The thing I can't seem to get is that some times it does seem to work...

I hope I've managed to explain it clearly and hope that I'm not being dense....

Thanks in advance for your help and suggestions.

Regads to you all.

 

[ParamRay]

.
.

I've only looked briefly at your code, but could it be that your active workbook hasn't yet been saved? (i.e. Your SaveLocation variable doesn't have a file extension on the end of it.)

 

[Skydyno]

.
.

I've only looked briefly at your code, but could it be that your active workbook hasn't yet been saved? (i.e. Your SaveLocation variable doesn't have a file extension on the end of it.)

Thanks for the quick reply. I checked you suggestion. The SaveLocation variable takes it's name from combining the activeworkbook.path and the activeworkbook.name. The activeworkbook.name includes the file extension and is added to SaveLovation

Checking and testing this showed that this was the case. firstly I opened and then closed the workbook,no changes - Saved OK. I made changes to the workbook and saved - OK. I changed the backup date to today so that it saved using both saves in the code - OK. OK so far this morning.

But every now and then I get the error... I'll try to keep a note each time I save to see if I can notice any difference when the error does occur.


I'm still trying to get the workbook to close using only my save and not the automatic one with prompt from Excel... I've tried the suggestions from here and allover and still have not managed to find a definitive solution. Surely there must be a way to close excel without saving and prompting!! any ideas?

Thanks again for your time and help....

regards