PDA

View Full Version : [SOLVED] Back-up / Different Paths



Philcjr
08-08-2005, 11:41 AM
Need some help,

Situation: 1) I have a file that is used by 8 people in different corporate locations. 2) Majority of the users are on MACs not PCs. 3) The directory in which the file is saved in is on a network, which means that these users have different paths to go through in order to get to the file.

Problem: I want to create a copy/back-up of the file and save it to another directory. I do not want to use the built-in feature of excel? as this has been cause MANY errors

Desired Outcome: Irrespective of where the file is opened, I would like the file to create a copy/back-up into another folder. If the file path does not exist, I would like to suppress all errors and just have the file close out.

There are three different paths in total:
(PCs) Madison NJ path: G:\team\PS&L\GA Spreadsheet\
(PCs) Collegeville PA path: S:\PS&L\GA Spreadsheet\
(MAC) Collegeville PA path: FF01A39D_VOL1:team:PS&L:GA Spreadsheet

The MAC path, I am still wondering if this is correct, as I still get errors when trying to save. Any way to verify this? I used the following code to obtain the paths:


Private Sub Workbook_Open()
Dim rng As Range
Set rng = Worksheets("test").Range("A65536").End(xlUp).Offset(1, 0)
rng.Offset.Value = Application.UserName
rng.Offset(0, 1).Value = ThisWorkbook.FullName
Columns("A:C").AutoFit
End Sub

Here is my code for creating the copy/back-up:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
.SaveCopyAs ("G:\team\PS&L\GA Spreadsheet\" & Format(Date, "mm-dd-yy") & " " & .Name)
End With
End Sub

Killian
08-08-2005, 04:13 PM
Hi there,
I tidied up the formatting on your post - hope that's OK

regarding the question, are you wanting one backup location or one thats relative to the current path? If relative's OK, a combination of the App Path seperator and the FileSystemObject should sort it out. Unfortunately I don't have Office installed here right now (long story involving the death of a hard drive) but hopefully this (untested) code and a trip to MSOffice help (or the KB here) will help.


'!!!Add a reference to the MS Scripting runtime (VBE>Tools>References)!!!
Dim fso As Scripting.FileSystemObject
Dim strBackUpPath As String
Set fso = New Scripting.FileSystemObject
With ThisWorkbook
strBackUpPath = .Path & Application.PathSeparator & Format(Date, "mm-dd-yy")
If not fso.folderexists(strBackUpPath) Then
fso.CreateFolder(strBackUpPath)
End if
.SaveCopyAs (strBackUpPath & Application.PathSeparator & .Name)
End With
Set fso = Nothing

Ken Puls
08-08-2005, 04:24 PM
Heya,

Killian, just tested on Windows platform and works flawlessly! Nice job for "off the top of your head" coding! :thumb

Philcjr
08-08-2005, 04:43 PM
Killian,

Thank you for fixing the format... I had copied and pasted from word, guess that is not a good idea. :) Is there a way I can/could have fixed it myself?

For the coding, what can I say, but THANK YOU! :bow:

1) Any chance of explaining how this acutally works, for some this may be very easy... but I am a newbie and am very eager to learn.

2) With this script, will it make a copy and place it in a "Back up" folder within the folder "GA Spreadsheet"? If not, can it?

3) What if the back-up file already exists? Will it automatically overwrite the existing file? If not, can it?

excelliot
08-10-2005, 04:15 AM
Hi there,
I tidied up the formatting on your post - hope that's OK

regarding the question, are you wanting one backup location or one thats relative to the current path? If relative's OK, a combination of the App Path seperator and the FileSystemObject should sort it out. Unfortunately I don't have Office installed here right now (long story involving the death of a hard drive) but hopefully this (untested) code and a trip to MSOffice help (or the KB here) will help.


'!!!Add a reference to the MS Scripting runtime (VBE>Tools>References)!!!
Dim fso As Scripting.FileSystemObject
Dim strBackUpPath As String
Set fso = New Scripting.FileSystemObject
With ThisWorkbook
strBackUpPath = .Path & Application.PathSeparator & Format(Date, "mm-dd-yy")
If not fso.folderexists(strBackUpPath) Then
fso.CreateFolder(strBackUpPath)
End if
.SaveCopyAs (strBackUpPath & Application.PathSeparator & .Name)
End With
Set fso = Nothing

Pls anybody can tell me how to use this code :help

Bob Phillips
08-10-2005, 07:36 AM
If relative's OK, a combination of the App Path seperator and the FileSystemObject should sort it out. Unfortunately I don't have Office installed here right now (long story involving the death of a hard drive) but hopefully this (untested) code and a trip to MSOffice help (or the KB here) will help.


'!!!Add a reference to the MS Scripting runtime (VBE>Tools>References)!!!
Dim fso As Scripting.FileSystemObject
Dim strBackUpPath As String
Set fso = New Scripting.FileSystemObject
With ThisWorkbook
strBackUpPath = .Path & Application.PathSeparator & Format(Date, "mm-dd-yy")
If not fso.folderexists(strBackUpPath) Then
fso.CreateFolder(strBackUpPath)
End if
.SaveCopyAs (strBackUpPath & Application.PathSeparator & .Name)
End With
Set fso = Nothing

I am a big fan of FileSystemObject, but you really do not need it here. And you should always format dates as yyy-mm-dd when using in directory/file names, for sorting.



Dim sBackUpPath As String
With ActiveWorkbook 'ThisWorkbook
sBackUpPath = .Path & Application.PathSeparator & _
Format(Date, "yyyy-mm-dd")
On Error Resume Next
MkDir sBackUpPath
On Error GoTo 0
.SaveCopyAs sBackUpPath & Application.PathSeparator & .Name
End With

Philcjr
08-10-2005, 08:35 AM
XLD,

Thanks for your input. I copied your code and pasted it into the "Workbook_BeforeClose" and it worked.

However,

I have a few questions:
1) can the file be saved into a pre-defined folder named "Back-up" vs. having it create a new folder each time?
2) can the file that is saved at closing, be named (YYY-MM-DD & file name)?
3) lastly, what is "PathSeparator"

Thanks again,
Phil

Bob Phillips
08-10-2005, 08:43 AM
I have a few questions:
1) can the file be saved into a pre-defined folder named "Back-up" vs. having it create a new folder each time?

If I understand correctly, yes even more simply



With ActiveWorkbook 'ThisWorkbook
sBackUpPath = .Path & Application.PathSeparator & "Backup"
.SaveCopyAs sBackUpPath & Application.PathSeparator & .Name
End With



2) can the file that is saved at closing, be named (YYY-MM-DD & file name)?

Again yes.



With ActiveWorkbook 'ThisWorkbook
sBackUpPath = .Path & Application.PathSeparator & "Backup"
.SaveCopyAs sBackUpPath & Application.PathSeparator & _
Format(Date, "yyyy-mm-dd ") & .Name
End With



3) lastly, what is "PathSeparator"

It is an application property that you can query to see what path separator is being used. It is usually "", but on some systems it will be something else. Killian was just being (properly) cautious.

.

Philcjr
08-10-2005, 11:09 AM
Thank you to everone that had posted to this thread, I have tested this code from here in NJ and had people test it from the MAC side and PC side in PA. No errors and code works! :cool:

Cant thank you all enough.... Phil

Killian
08-11-2005, 02:49 AM
Hi all

sorry for not posting back - it's been a busy few of days finishing off a web app at work

Glad to hear it's all working. Just a quick note about the FileSystemObject... the reason I like it is that simple combinations of "If file/folderexists" and "createfile/folder" make accurate & error-free file management very straight-forward and being able to iterate through sub-folders and files is damn useful


Dim fso As Scripting.FileSystemObject
Dim fldr As Scripting.Folder
Dim f As Scripting.File
Dim strMsg As String
Set fso = New Scripting.FileSystemObject
Set fldr = fso.GetFolder(ThisWorkbook.Path)
For Each f In fldr.Files
strMsg = strMsg & f.Name & vbLf
Next
MsgBox strMsg

Nice...

Bob Phillips
08-11-2005, 03:11 AM
Glad to hear it's all working. Just a quick note about the FileSystemObject... the reason I like it is that simple combinations of "If file/folderexists" and "createfile/folder" make accurate & error-free file management very straight-forward and being able to iterate through sub-folders and files is damn useful

I agree with all of that, as I said in my respose I am a big fan also.

Using FSO requires referncing another script library; some organisations block scripting; requires another object creation (resource hungry); and has the potential of not working on earlier systems (unless you use late-binding). All reasons to use judiciously, and it just wasn't necessary in this case.