File backup on open + change save location

[Luke777]

Hi all,

Currently using the following code to backup a workbook when it is opened

Dim sFileName As String
Dim sDateTime As String

    With ThisWorkbook
        sDateTime = " Backup Created (" & Format(Now, "dd-mm-yyyy hhnnss") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
        .SaveCopyAs sFileName
    End With

Currently, the backup is dropped in the same location as the main document. I've created a backup folder in the same location to which I wish to save backups - keep things a little tidier

However, I'm not sure what I should be changing with the above code. I've tried

.SaveCopyAs "[pathway]" & sFileName

but no dice. I imagine it's something simple I'm missing, that's usually the way with VBA I find.

Any advice?

Thanks!

 

[Fluff]

Try

    With ThisWorkbook
        sDateTime = " Backup Created (" & Format(Now, "dd-mm-yyyy hhnnss") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.Name, ".xlsm", sDateTime)
        .SaveCopyAs .Path & "\Backup\" & sFileName
    End With

 

[Luke777]

Try

    With ThisWorkbook
        sDateTime = " Backup Created (" & Format(Now, "dd-mm-yyyy hhnnss") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.Name, ".xlsm", sDateTime)
        .SaveCopyAs .Path & "\Backup\" & sFileName
    End With

No dice with that one I'm afraid.

It seems to be getting thoroughly confused and gives a 1004 error - the message is "sorry, we couldn't find [filepath\Backups\\\filepath(yes, repeated for some reason)\filename]. Is it possible it was moved, renamed or deleted?

I've no idea why its repeating the filepath twice like that

 

[Fluff]

Did you use the code I provided, or did you change your your code?

 

[Luke777]

Did you use the code I provided, or did you change your your code?

I tried both, and also tried putting the full path in place of "\backup\" as well

 

[Fluff]

Where is the file located?
Is it on your hard drive, a server, or the cloud?