Copying file to another location

[selsley]

Can anyone tell me how to copy an Excel file to another location when
open it? I'm trying to allow people to view a copy of a file each tim
I update it.

Many thanks

Stev

 

[Dave Peterson]

Wouldn't you want to copy your workbook to that location when you save it--not
when you open it?

I put this code behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now + TimeSerial(0, 0, 3), "SaveACopy"
End Sub

Tnen in a general module, I put this code:

Option Explicit
Sub SaveACopy()

Dim myPath As String

myPath = "c:\my documents\excel\test\"

With ThisWorkbook
If .Saved Then
On Error Resume Next
.SaveCopyAs Filename:=myPath & .Name
If Err.Number <> 0 Then
MsgBox "Something bad happened"
Err.Clear
Else
MsgBox "Also saved to: " & myPath & .Name
End If
On Error GoTo 0
End If
End With

End Sub

If you save your workbook, then it does the SaveCopyAs to the location of your
choice (about 3 seconds after the save).

 

[Dave Peterson]

The bad news about this is that the code will travel with the workbook. If
someone uses a copy and then saves it, it'll try to do the save again.

One way is to ask if the backup copy should be made or use some indicator that
will only work for you.

If you set your username under Tools|Options|General tab, you could rely on
that:

You could modify the workbook_beforesave event like this:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If UCase(Application.UserName) = LCase("yourusernamehere") Then
Application.OnTime Now + TimeSerial(0, 0, 3), "SaveACopy"
End If
End Sub