Is it possible to save a file when updating in two different locations at the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

Greg,

Assuming that you're always working with the version on the local computer,
you could use code like the following in the ThisWorkbook code module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
ThisWorkbook.SaveCopyAs "\\server\share\folder\" & ThisWorkbook.Name
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

"Greg" wrote in message
...
Is it possible to save a file when updating in two different locations at
the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

You'll want to kill the existing copy of the workbook on the server, so the
code should be

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
On Error Resume Next
Kill "\\server\share\folder\" & ThisWorkbook.Name
On Error GoTo 0
ThisWorkbook.SaveCopyAs "\\server\share\folder\" & ThisWorkbook.Name
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

"Greg" wrote in message
...
Is it possible to save a file when updating in two different locations at
the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

Can be done with VBA code...........I have an "Archive" button on some of my
bigger databases which will save a DateTime-stamped copy of the file to a
specified Archive Directory and then re-save the file by it's normal name to
it's normal Directory.........

Vaya con Dios,
Chuck, CABGx3



"Greg" wrote:

Is it possible to save a file when updating in two different locations at the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

I don't think that .savecopyas will care if there is an existing file with that
same name. It'll just overwrite it--with no warning.

Chip Pearson wrote:

You'll want to kill the existing copy of the workbook on the server, so the
code should be

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = False Then
On Error Resume Next
Kill "\\server\share\folder\" & ThisWorkbook.Name
On Error GoTo 0
ThisWorkbook.SaveCopyAs "\\server\share\folder\" & ThisWorkbook.Name
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

"Greg" wrote in message
...
Is it possible to save a file when updating in two different locations at
the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

--

Dave Peterson

Greg,

You're right, it isn't a good idea to have two files with the same name. But having one or two
backups of important files is a good idea, and having a dated trail of really important files is
even better. Below is code that you can assign to a button, to save important files with "BU
date-time" added to the file name, in two backup folders - one local and one on the network (always
useful to have a local backup for when the network is down). Note that the folders are assumed to
exist, but if they are not accessible, a message will be given. Change the folder paths, and change
the number of dated backup files to keep (the value FILES2KEEP). You can also get rid of the sub
RemoveOldBackups if you prefer to manually manage your backup files.

HTH,
Bernie
MS Excel MVP


Option Explicit
Const LOCATION1 As String = "C:\Excel Backup\"
Const LOCATION2 As String = "F:\Excel Backup\"
'Change this number to change how many backups are kept
Const FILES2KEEP As Integer = 3

Sub DoubleBUandSave()
'Saves the current file to two backup folders and its own folder
Dim myFName As String
Dim myB As Workbook
Application.DisplayAlerts = False

myFName = Replace(ActiveWorkbook.Name, ".xls", "")

'Save in a backup folder on the local disk
On Error GoTo NotSavedLocal:
ActiveWorkbook.SaveCopyAs Filename:= _
LOCATION1 & myFName & " BU " & Format(Now, " yyyy-mm-dd hh-mm") & ".xls"

SaveNetwork:
'Save in a folder on the network
'This can be a mapped drive letter or a UNC style path
On Error GoTo NotSavedNetwork:
ActiveWorkbook.SaveCopyAs Filename:= _
LOCATION2 & myFName & " BU " & Format(Now, " yyyy-mm-dd hh-mm") & ".xls"

'Then do a regular save
SaveRegular:
ActiveWorkbook.Save

'Then clean up the extra dated backups
RemoveOldBackups ActiveWorkbook

Application.DisplayAlerts = True

Exit Sub

NotSavedLocal:
MsgBox "The local backup of this file failed."
Resume SaveNetwork

NotSavedNetwork:
MsgBox "The network backup of this file failed."
Resume SaveRegular

End Sub

Sub RemoveOldBackups(inBook As Workbook)
'Routine to get rid of older backup files
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = LOCATION1
.SearchSubFolders = False
.Filename = Replace(inBook.Name, ".xls", "") & _
" BU " & " ????-??-?? ??-??"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderDescending) 0 Then
If .FoundFiles.Count FILES2KEEP Then
For i = FILES2KEEP + 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
End If
End If

.NewSearch
.LookIn = LOCATION2
.SearchSubFolders = False
.Filename = Replace(inBook.Name, ".xls", "") & _
" BU " & " ????-??-?? ??-??"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderDescending) 0 Then
If .FoundFiles.Count FILES2KEEP Then
For i = FILES2KEEP + 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
End If
End If
End With

End Sub




"Greg" wrote in message
...
Is it possible to save a file when updating in two different locations at the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg

In addition Bernie's code, you might be interested in my "Save Copy And Zip"
add-in. It adds a item to your Tools menu that will pop up a dialog allowing
you to save a copy of any open workbook (that has been saved at disk -- in
must have a non-empty Path) as a Zip file. The main function
SaveCopyAndZipFile is Public, so it can be fully automated from other code.

The add-in requires the command-line version of either PKZip or WinZip. The
command line version of PKZip is included with the standard PKZip package,
and the command line version of WinZip is a free download to registered
users.

See http://www.cpearson.com/excel/SaveCopyAndZip.htm for details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Greg,

You're right, it isn't a good idea to have two files with the same name.
But having one or two backups of important files is a good idea, and
having a dated trail of really important files is even better. Below is
code that you can assign to a button, to save important files with "BU
date-time" added to the file name, in two backup folders - one local and
one on the network (always useful to have a local backup for when the
network is down). Note that the folders are assumed to exist, but if they
are not accessible, a message will be given. Change the folder paths, and
change the number of dated backup files to keep (the value FILES2KEEP).
You can also get rid of the sub RemoveOldBackups if you prefer to manually
manage your backup files.

HTH,
Bernie
MS Excel MVP


Option Explicit
Const LOCATION1 As String = "C:\Excel Backup\"
Const LOCATION2 As String = "F:\Excel Backup\"
'Change this number to change how many backups are kept
Const FILES2KEEP As Integer = 3

Sub DoubleBUandSave()
'Saves the current file to two backup folders and its own folder
Dim myFName As String
Dim myB As Workbook
Application.DisplayAlerts = False

myFName = Replace(ActiveWorkbook.Name, ".xls", "")

'Save in a backup folder on the local disk
On Error GoTo NotSavedLocal:
ActiveWorkbook.SaveCopyAs Filename:= _
LOCATION1 & myFName & " BU " & Format(Now, " yyyy-mm-dd hh-mm") & ".xls"

SaveNetwork:
'Save in a folder on the network
'This can be a mapped drive letter or a UNC style path
On Error GoTo NotSavedNetwork:
ActiveWorkbook.SaveCopyAs Filename:= _
LOCATION2 & myFName & " BU " & Format(Now, " yyyy-mm-dd hh-mm") & ".xls"

'Then do a regular save
SaveRegular:
ActiveWorkbook.Save

'Then clean up the extra dated backups
RemoveOldBackups ActiveWorkbook

Application.DisplayAlerts = True

Exit Sub

NotSavedLocal:
MsgBox "The local backup of this file failed."
Resume SaveNetwork

NotSavedNetwork:
MsgBox "The network backup of this file failed."
Resume SaveRegular

End Sub

Sub RemoveOldBackups(inBook As Workbook)
'Routine to get rid of older backup files
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = LOCATION1
.SearchSubFolders = False
.Filename = Replace(inBook.Name, ".xls", "") & _
" BU " & " ????-??-?? ??-??"
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderDescending) 0 Then
If .FoundFiles.Count FILES2KEEP Then
For i = FILES2KEEP + 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
End If
End If

.NewSearch
.LookIn = LOCATION2
.SearchSubFolders = False
.Filename = Replace(inBook.Name, ".xls", "") & _
" BU " & " ????-??-?? ??-??"
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderDescending) 0 Then
If .FoundFiles.Count FILES2KEEP Then
For i = FILES2KEEP + 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
End If
End If
End With

End Sub




"Greg" wrote in message
...
Is it possible to save a file when updating in two different locations at
the
same time (i.e. on the computer and on the server). Don't think it a good
idea for obvious reasons, but I was asked to find out.
--
Thanks for your help,
Greg