Auto-Backup of Personal.xlsb

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Auto-Backup of Personal.xlsb

Post by gvanhook »

Does anyone have experience in automatically backing up their personal.slxb file to a network drive or a cloud service? My organization is looking at business continuity and I do all the programming for our finance unit (with lots of help from loungers :cheers: ) We are trying to determine the best way to have my macros available to others in the event that something happens to me.

I work on the macros frequently so don't think publishing an add-in is the right way to go.

I found this code online that will save a copy of the personal.xlsb file each time I save it, but it is failing on me with a compile error on the .SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak") line

Code: Select all

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'use Before Save event as Before Close event will not be triggered by Personal

'location to backup Personal.XLSB to (network drive, etc.)

Const sBak As String = "\\fake_network_Drive\Macro_Backup"

Application.DisplayAlerts = False

With Workbooks("Personal.xlsb")

.SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak")

.Save

End With

Application.DisplayAlerts = True

End Sub
Another alternative I found was to move the personal.xlsb to a network drive and use the mklink /j function in Windows 10 to create a directory junction to the xlstart folder. I haven't tried this option yet. If anyone has tried this before, please share your experiences.

I am open to other suggestions as well as I always value the opinions of fellow Loungers.

Greg

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-Backup of Personal.xlsb

Post by HansV »

A compiler error or a runtime error? You're missing the backslash between the path and the filename. sBak should be defined as

Code: Select all

Const sBak As String = "\\fake_network_Drive\Macro_Backup\"
Regards,
Hans

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Auto-Backup of Personal.xlsb

Post by gvanhook »

It was a compile error. I scrubbed the path for the post and just forgot the backslash. The code I am running has the backslash. The error is highlighting the word Now and saying "expected function or variable"

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-Backup of Personal.xlsb

Post by HansV »

That's weird. I ran the code (with a path on my computer) and it created a backup without error.
Do you have a module named Now? If so, give it another name.
Regards,
Hans

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Auto-Backup of Personal.xlsb

Post by gvanhook »

I don't have a module named Now.
I commented out the section of the line that is appending the date to the filename and now it is unable to find the file path. I even added an if statement to check for the final backslash and append one if it is absent. This is a runtime error. I am using C:\Users\gvanhook\Documents\ as the file path. I know the folder exists and that I have read/write access...

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-Backup of Personal.xlsb

Post by HansV »

What happens if you
- Create a new workbook.
- Copy the code into the ThisWorkbook module of the new workbook.
- Comment out the code in the old workbook.
- Close the old workbook (without saving it).
Does the code work in the new workbook?
Regards,
Hans

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Auto-Backup of Personal.xlsb

Post by gvanhook »

The code is running from the This Workbook module of personal.xlsb.

I tried your steps and closed personal.xlsb and added the code to a new workbook. I still got the same runtime 1004 error saying it could not locate the file path.

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-Backup of Personal.xlsb

Post by HansV »

I'm afraid I don't understand what causes the problem. Are you absolutely sure that the path has been spelled correctly? What happens if you copy/paste the path into the address bar of File Explorer?
Regards,
Hans

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Auto-Backup of Personal.xlsb

Post by gvanhook »

It opens the correct folder if I copy/paste to file Explorer. I have a meeting with my IT team is 30 minutes and I will see if there is some level of security they have applied that is stopping this from working.

LisaGreen
5StarLounger
Posts: 814
Joined: 08 Nov 2012, 17:54

Re: Auto-Backup of Personal.xlsb

Post by LisaGreen »

I'm interested if security is the issue....

PLease get back to us!

TIA
Lisa

gvanhook
StarLounger
Posts: 65
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Auto-Backup of Personal.xlsb

Post by gvanhook »

Okay, it was not a security issue. When I first ran this code the Now() statement was causing a compile error. I commented out the part of that line from & Format(Now ()... on to the end but that broke the filename which caused the runtime error. So back to the Format(Now() error.
One of my team tried a different approach for that line and it worked. He replaced the Now() statement with a variable that had been defined as Date

Code: Select all

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'use Before Save event as Before Close event will not be triggered by Personal

'location to backup Personal.XLSB to (network drive, etc.)
Dim strPath As String


strPath = "\\fakepath\fake\fake\backup\"
If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
End If

Application.DisplayAlerts = False

With Workbooks("Personal.xlsb")

Dim varToday As Date
varToday = Date

.SaveCopyAs strPath & Format(varToday, "yyyymmdd") & ".bak"

.Save

End With

Application.DisplayAlerts = True

End Sub
Now, everytime I same personal.xlsb it created a backup copy of the file in the designated path. All I need to do to recover the file is copy it to XLStart and rename as personal.xlsb!

Thanks for the assistance.

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-Backup of Personal.xlsb

Post by HansV »

Weird - it should have worked with Now. But I'm glad it's working now.
Regards,
Hans