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

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" wrote
in message ...

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

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

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" wrote in
message ...

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

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" wrote in
message ...

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

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

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" wrote
in message ...

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

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

--
AmyTaylo
-----------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097
View this thread: http://www.excelforum.com/showthread.php?threadid=50053