Using a hyperlink to backup Excel files
 

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

Using a hyperlink to backup Excel files
 

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

On Tue, 1 May 2007 23:30:58 +0100, Colin Hayes wrote:

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

Using a hyperlink to backup Excel files
 

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





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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

On Tue, 1 May 2007 23:30:58 +0100, Colin Hayes
wrote:

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

Using a hyperlink to backup Excel files
 

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


DQ

Using a hyperlink to backup Excel files
 

In article .com, dq
writes
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

Using a hyperlink to backup Excel files
 

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.



In article , Colin Hayes
writes
In article .com, dq
writes
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

Using a hyperlink to backup Excel files
 

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

Using a hyperlink to backup Excel files
 

In article .com, dq
writes
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