saving backup

[AmyTaylor]

Dear all, I have the following code, which saves a backup of the file being opened. I would appreciate your help on changing it so that it saves into a directory called \backup in the same file path.

Many thanks for any help !
love Amy xx

Private Sub Workbook_Open()
Dim sStr As String

sStr = Format(Now, "yyyymmdd hh-mm")

Me.SaveAs Filename:=Me.Name & " " & sStr, _
FileFormat:=xlWorkbookNormal
End Sub

[Norman Jones]

Hi Amy,

Try:

'=============>>
Private Sub Workbook_Open()
Dim sStr As String
Dim sPath As String

sStr = Format(Now, "yyyymmdd hh-mm")
sPath = Me.Path & "\Backup\"

Me.SaveAs Filename:=sPath & Me.Name & " " & sStr, _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============


---
Regards,
Norman


"AmyTaylor" <[email protected]> wrote
in message news:[email protected]...
>
> Dear all, I have the following code, which saves a backup of the file
> being opened. I would appreciate your help on changing it so that it
> saves into a directory called \backup in the same file path.
>
> Many thanks for any help !
> love Amy xx
>
> Private Sub Workbook_Open()
> Dim sStr As String
>
> sStr = Format(Now, "yyyymmdd hh-mm")
>
> Me.SaveAs Filename:=Me.Name & " " & sStr, _
> FileFormat:=xlWorkbookNormal
> End Sub
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
> http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=500532
>

[AmyTaylor]

Thank you very much Norman, can I ask one final question ?

Is it possible to add something, whereby if the folder called \backup doesnt exist it creates it then saves the backup file to it?
Thank you!!
Amy xx

[Bob Phillips]

Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder <> "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
'do nothing
Else
MkDir sFolder
End If
End If



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you very much Norman, can I ask one final question ?
>
> Is it possible to add something, whereby if the folder called \backup
> doesnt exist it creates it then saves the backup file to it?
> Thank you!!
> Amy xx
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=500532
>

[Norman Jones]

Hi Amy,

As an alternative to Bob's suggestion, you could simply create the backup
folder and use an error handler to deal with the possiblie existence.

Try:

'=============>>
Private Sub Workbook_Open()
Dim sStr As String
Dim sPath As String

sStr = Format(Now, "yyyymmdd hh-mm")
sPath = Me.Path & "\Backup\"

On Error Resume Next
MkDir sPath
On Error GoTo 0

Me.Save Filename:=sPath & Me.Name & " " & sStr, _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============

However, if the intention is that the original file should remain open
(rather than the backup copy), then change:

>> Me.Save Filename:=sPath & Me.Name & " " & sStr, _
FileFormat:=xlWorkbookNormal

to:

Me.SaveCopyAs Filename:=sPath & Me.Name & " " & sStr

In this way, the original file remains open (and unchanged) whilst a backup
copy is saved to the Backup folder.


---
Regards,
Norman



"AmyTaylor" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you very much Norman, can I ask one final question ?
>
> Is it possible to add something, whereby if the folder called \backup
> doesnt exist it creates it then saves the backup file to it?
> Thank you!!
> Amy xx
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
> http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=500532
>

[AmyTaylor]

Hi Bob & Norman,

your help is sooooo fantastic. Can I ask another question ?
Sometimes the file is opened as read only, in which case I wouldnt want the file to be be backed up. Is it possible to add something which only creates a backup if the file is opened as read only ?!!

Thanks both,
Love Amy xx

[Norman Jones]

Hi Amy,

Your statement:

> Sometimes the file is opened as read only, in which case I wouldnt
> want the file to be be backed up.

seems at odds with:

> Is it possible to add something which only creates a backup if the
> file is opened as read only ?!!

Assuming the first interpretation, try:

'=============>>
Private Sub Workbook_Open()
Dim sStr As String
Dim sPath As String

If Me.ReadOnly Then Exit Sub '<<==== ADDITION

sStr = Format(Now, "yyyymmdd hh-mm")
sPath = Me.Path & "\Backup\"

On Error Resume Next
MkDir sPath
On Error GoTo 0

Me.Save Filename:=sPath & Me.Name & " " & sStr, _
FileFormat:=xlWorkbookNormal
End Sub
'<<=============

If, however, the second interpretation shoul prevail, change:

If Me.ReadOnly Then Exit Sub
to

If Not Me.ReadOnly Then Exit Sub


---
Regards,
Norman


"AmyTaylor" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Bob & Norman,
>
> your help is sooooo fantastic. Can I ask another question ?
> Sometimes the file is opened as read only, in which case I wouldnt want
> the file to be be backed up. Is it possible to add something which only
> creates a backup if the file is opened as read only ?!!
>
> Thanks both,
> Love Amy xx
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
> http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=500532
>

[AmyTaylor]

Fantastic, that works perfectly
My mistake re the read-only statement, I only want it to backup when the file is opened as read-write. Silly me!
Thanks
Amy xx