About Us

romelsb

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

whoooops, my bad. One extra close bracket.

Take out a ")" at the bold bit in the following
"(ThisWorkbook.Name)-4<b))</b"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

Scrap the above, I forgot we can't use HTML here!

Where there are "))" in this line:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4))

Take out one ")"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

we catch another pop-up
compile error:
variable not defined

high-lighted

TName =

thks again....still requesting help--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Scrap the above, I forgot we can't use HTML here!

Where there are "))" in this line:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4))

Take out one ")"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

where

Dim Confirm

change to:

Dim Confirm, TName as String

Didn't think you'd have option explicit on!

"romelsb" wrote:

we catch another pop-up
compile error:
variable not defined

high-lighted

TName =

thks again....still requesting help--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Scrap the above, I forgot we can't use HTML here!

Where there are "))" in this line:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4))

Take out one ")"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

romelsb

we got another one pop-up
compile error:
syntax error
on red text line

ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

HERE IS THE CURRENT WINDOW

VBE WORKBOOK WINDOW
*******
option explicit

***<ADDINSTALL****

Private Sub Workbook_AddinInstall()

End Sub

***<BEFORECLOSE****

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm, TName As String

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

TName = Left$(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

End Sub

*************

pls dont hang up////thanks

"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

where

Dim Confirm

change to:

Dim Confirm, TName as String

Didn't think you'd have option explicit on!

"romelsb" wrote:

we catch another pop-up
compile error:
variable not defined

high-lighted

TName =

thks again....still requesting help--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Scrap the above, I forgot we can't use HTML here!

Where there are "))" in this line:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4))

Take out one ")"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

DS

You got line breaks causing problems again:

ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

should all be on a single line.

Remove your line breaks and you should be ok.

Time for me top be homeward bound (it's 550pm here, and the missus will not
be happy if I ain't home for dinner!) - you should be ok from here, but I'm
sure one of the other helpful types will be able to sort you out if you hit
any more problems.

DS

"romelsb" wrote:

we got another one pop-up
compile error:
syntax error
on red text line

ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

HERE IS THE CURRENT WINDOW

VBE WORKBOOK WINDOW
*******
option explicit

***<ADDINSTALL****

Private Sub Workbook_AddinInstall()

End Sub

***<BEFORECLOSE****

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm, TName As String

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

TName = Left$(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

End Sub

*************

pls dont hang up////thanks

"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

where

Dim Confirm

change to:

Dim Confirm, TName as String

Didn't think you'd have option explicit on!

"romelsb" wrote:

we catch another pop-up
compile error:
variable not defined

high-lighted

TName =

thks again....still requesting help--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Scrap the above, I forgot we can't use HTML here!

Where there are "))" in this line:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4))

Take out one ")"

"romelsb" wrote:

we got a pop-up...
compile error
syntax error

on 2nd line

ThisWorkbook.Save
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\TName & Format(Date, "yyyymmdd") & ".xls"

pls. dont hang up...thanks for reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

I can't remove the line breaks from here, as this is inserted by the editor
in which I'm typing at the moment!

Remove the line break on that line on your workbook, and on the line which
shows;
ThisWorkbook.SaveAs "C:\backups\Developer_" & (Date, "yyyymmdd") & ".xls"

Replace that with:
TName = Left$(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4)) & "_"
ThisWorkbook.SaveAs "C:\backups\TName & Format(Date,"yyyymmdd") & ".xls"

The above should show on 2 lines (as that's what it is!), starting with
"TName" and "ThisWorkbook" respectively.
This can be used in any workbook, and will name the backup according to the
workbook name followed by the date.
e.g. Workbook "NumEdit.xls" would be backed up to "NumEdit_20061102.xls"

Once the amendments above are made, and the line breaks removed, you can
just copy and paste to any workbook you have, and it will backup on close to
the folder C:\backups according to the naming convention above.

Cheers
DS

"romelsb" wrote:

your correct...line breaks...can u pls gave me this considering that I can
just copy and paste it on another workbooks....can u make it constant on
"orig. filename + textdate line" then I will just type in the back-up
folder....is it possible....I try to type in the real filename then save then
when i try to close the workbook...i cant click the fileclose button....were
almost finish i think...pls reply...
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

that's just a copy & paste issue, it's inserted a line break from here where
there shouldn't be one!

If you see in the editor:

***
Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
***

then just delete the line break between the two so all that appears as a
single line. That should fix it.

HTH
DS

"romelsb" wrote:

thanks...i had tried...see this line
i do as instructed
upon closing
syntax error pop-up
vbe opened
then the ine below is highlighted

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,

pls...continue to reply
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

romelsb

thanks alot

ThisWorkbook.SaveAs "C:\Documents and Settings\Romel\My Documents\RSB\EXCEL
BACKUPS\" & TName & Format(Date, "yyyymmdd")

is correct.

Another Problem...
Is it possible to AUTOLOCK the backup-file against changes without
necessarily placing a password.
Meaning, during the SaveAs action - the backup file generated is in a
protected mode without a password....

thanks for the first problem u had solved...great job...more power
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

OK, here we go.

Open your Excel workbook, and along the top, click the following (each will
open after the other):

Tools - Macro - Visual Basic Editor

You should see a number of panes, split across the screen. On your left
should be a pane headed "Project - VBA Project", with a list of items below
it. One of these should be your item, something like "VBA Project
(DEVELOPER.xls)". Click on the plus symbol, and you should see some more
options underneath, including "Microsoft Excel Objects". Click the plus
symbol next to this, and you'll see a long list, of every sheet in the
workbook,as well as, at the bottom "ThisWorkbook".

Double-click "ThisWorkbook" and you'll get an editor pane open up in the
main body. Copy the following and paste it into that pane. Then close the
editor, and save the workbook.

*********************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\backups\Developer_" & Format(Date, "yyyymmdd") &
".xls"

End Sub
*********************

This will save the current version in place, and a copy to the folder
"C:\Backups" (which you'll need to create before first use), and the backup
copy will be named as "Developer_20061102.xls" (example as one done today, it
will update as per the date actioned).

Done and done.
HTH
DS

"romelsb" wrote:

ooopss...so good....sorry i dont know how to place it in VBE...can u tech me
just for this sake....I need the backup filename just like u
formatted.....will it be like this...
if filename of orig. workbook is "DEVELOPER.xls"
then
does the first backup will be this "DEVELOPER 2006NOV02.xls"
THANKS A LOT....pls reply back...

--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

OK, that gives us a better idea!

If you use:

**************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Confirm

Confirm = MsgBox("Do you wish to save and create a backup?", vbYesNo,
"Confirm Backup?")
If Confirm = vbNo Then
Exit Sub
End If

ThisWorkbook.Save

ThisWorkbook.SaveAs "H:\crawsm\MyDocuments\NewName" & Format(Date,
"yyyymmdd") & ".xls"

End Sub
**************

This will save the current copy in its location, and save a backup to the
fodler / file specified with the current date in format yyyymmdd added to the
file name.

It also adds a confirmation request as I mentioned earlier to make sure that
you actually want to save and backup before it does! Just take that out if
it's not suitable for your needs.

HTH
DS

"romelsb" wrote:

tHANKS FOR THE QUICKEST REPLY....your good...let me give specifics...I NEED A
BACKUP UP OF AT LEAST 2 file of the latest update......If i use today the
orig. file, save/close then i will have a backup file on a specified
folder....After one month, if i openmake changessaveclose the same orig.
file then i will have another backup auto filename(lets say with text date)
on the same specified folder...In this stage I have a history
back-ups....Will you help me with this because I need the orig file be used
like a template and at the same time with updated development (i.e. formula,
data, new sheets)....again thanks...more power...pls reply....
--
"Bright minds are blessed to those who share them.."-rsb.

"DS" wrote:

Hi romelsb,

I take it setting the "backup" option in the SaveAs dialog box to
automatically back up your workbook isn't sufficient?

If you use the following, it'll automatically save your workbook in the
current location, and back up your workbook to the specified location on
close. This needs placing in the ThisWorkbook section, rather than in a
module or sheet.

****************
Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Save
ThisWorkbook.SaveAs "C:\BackUps\NameIt.xls", Password:="yahoo"

End Sub
****************
You'll obviously need to direct the path above to a valid folder, file name
etc!

WARNING: this will save the workbook on close. If you may want to close
WITHOUT saving, add a yes/no message box to the beginning of the code to
allow the user to exit without saving / backing up. Very useful when you're
doing some development and you make a complete hash of things and just want
to start again from your last save point... as I found out the hard way!!!

HTH
DS

"romelsb" wrote:

Can anybody of good intention, program an excel workbook to provide another
back up file...I had many experience since 1992 wherein after saving and
closing my files and re-open again,,,error will appear and then it is unable
to read....I loss bunch of time using the good functions of excel yet the
program cannot fully protect the users work...I am still hoping that good
VBE tricks may do this ...proofchecksavebackupclose....
--
"Bright minds are blessed to those who share them.."-rsb.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Backup Files in Excel 2007	Arlene	Excel Discussion (Misc queries)	1	February 4th 10 11:51 PM
Backup of excel 2007 files.	Bruce	Excel Discussion (Misc queries)	8	January 6th 09 06:40 PM
Excel 2007 Backup files	Training Spec.	Excel Discussion (Misc queries)	4	April 29th 08 04:36 PM
Excel 2003 Backup Files	IMTA1	Excel Discussion (Misc queries)	1	December 15th 05 06:13 PM
Creating excel backup files	Andrew Fekete	Excel Programming	1	October 14th 03 11:35 PM

Menu

About Us