Using a hyperlink to backup Excel files

[Colin Hayes]

HI all

I want to put a hyperlink or a button on my Excel sheet to backup all
the files in one named folder to another named folder. I know Excel has
auto backup , but it would be more accessible to have a link on the
sheet itself , maybe with an 'are you sure' prompt too.

If this is possible , can someone help with some code? It really would
be all the files in one folder copied over to another for backup
purposes.

Any advice gratefully received.

Thanks

 

[Gord Dibben]

Colin

You could create a small batch file to copy the contents of one folder to
another.

Start Notepad and enter a line such as this.

copy "c:\program files\microsoft office\exceldata\*.*" "c:\excelbackups"

Save as "all files" mybackup.bat in C drive

Run the batch file from a macro assigned to a button in Excel

Sub backitall()
Dim taskID As Variant
On Error Resume Next
Shell ("C:\mybackup.bat"), vbNormalFocus
End Sub


Gord Dibben MS Excel MVP

 

[Colin Hayes]

HI Gord

OK thanks for that - works a treat.

BTW could I insert a popup saying - 'Backup all files?' in the macro
somewhere and then a yes/no prompt before it actually copies them?

Best Wishes


Colin

 

[dq]

Add the following line in your code
If (MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes) Then
Exit Sub


DQ

 

[Colin Hayes]

Add the following line in your code
If (MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes) Then
Exit Sub


DQ

HI

OK thanks very much for that. Works very well.

Can I also use a line of VBA in the coding to create the backup folder
itself? It would be in the same folder and just called 'backup'. I'm
finding it's not working if the backup folder is not already there , and
it would be helpful if it could check and create if need be.

Very grateful for your help with this.



Best Wishes

 

[Colin Hayes]

HI

If someone could help with this , I'd be grateful.

I need a line of VBA to create a folder called 'backup' on the c drive
so I can save my files into it. This would be part of the routine to
backup my files using VBA code.

At the moment , I'm getting 'path not found' errors because the files
I'm trying to save are being sent to a non-existent folder. A line to
create the folder would solve it , if someone could oblige.

Thanks.

 

[dq]

VB has a MkDir command that, yes!, creates a new folder
e.g.
On Error Resume Next
Call MkDir("C:\Backup")

The 'on error...' will make VB ignore the error you get if the folder
already exists so you don't need to check for it.

DQ

 

[Colin Hayes]

VB has a MkDir command that, yes!, creates a new folder
e.g.
On Error Resume Next
Call MkDir("C:\Backup")

The 'on error...' will make VB ignore the error you get if the folder
already exists so you don't need to check for it.

DQ

HI

OK that got it. Perfect - thanks for helping with that.



Best Wishes