Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Is it possible to let Excel save the .xls file to .bak when opening it?
Preferably with a macro. (I am using Excel 2003). Cheers, Avi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Hi,
You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
If you really want an archived record of the workbook, then you should
consider just saving the file to an archive directory with a datecode appended to the filename, and like Shane says, resaving to your regular directory without the datecode........if you only save it as a .bak, it will be overwritten each time you open the file anew....even if it has errors in it. Vaya con Dios, Chuck, CABGx3 "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Thank you. That worked. I just have two followup questions:
1. Is this possible to implement this for all .xls files (i.e. use it in persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an actual filename in the macro - it should use something like current_workbook (??). 2. Is it also possible to automatically answer the questions about overwriting existing files with 'yes'? Thank you again, Avi. "Shane Devenshire" wrote: Hi, You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
You can use an application level event.
This goes in the ThisWorkbook module of your persnlk.xls workbook. (persnlk.xls is the same as the USA version personal.xls???) Option Explicit Public WithEvents xlApp As Application Private Sub Workbook_BeforeClose(Cancel As Boolean) Set xlApp = Nothing End Sub Private Sub Workbook_Open() Set xlApp = Excel.Application End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) Dim NewFileName As String NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak" Wb.SaveCopyAs Filename:=NewFileName End Sub (Instrrev was added in xl2k. If you're using xl97, you'll have to parse the filename differently.) And an alternative... Another option would be to dump autosave and use Jan Karel Pieterse's addin (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) avi wrote: Thank you. That worked. I just have two followup questions: 1. Is this possible to implement this for all .xls files (i.e. use it in persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an actual filename in the macro - it should use something like current_workbook (??). 2. Is it also possible to automatically answer the questions about overwriting existing files with 'yes'? Thank you again, Avi. "Shane Devenshire" wrote: Hi, You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Thank you very much. This looks very promising. I'm off to home now, so first
thing tomorrow I'll give this a go. Arnold Vink "Dave Peterson" wrote: You can use an application level event. This goes in the ThisWorkbook module of your persnlk.xls workbook. (persnlk.xls is the same as the USA version personal.xls???) Option Explicit Public WithEvents xlApp As Application Private Sub Workbook_BeforeClose(Cancel As Boolean) Set xlApp = Nothing End Sub Private Sub Workbook_Open() Set xlApp = Excel.Application End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) Dim NewFileName As String NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak" Wb.SaveCopyAs Filename:=NewFileName End Sub (Instrrev was added in xl2k. If you're using xl97, you'll have to parse the filename differently.) And an alternative... Another option would be to dump autosave and use Jan Karel Pieterse's addin (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) avi wrote: Thank you. That worked. I just have two followup questions: 1. Is this possible to implement this for all .xls files (i.e. use it in persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an actual filename in the macro - it should use something like current_workbook (??). 2. Is it also possible to automatically answer the questions about overwriting existing files with 'yes'? Thank you again, Avi. "Shane Devenshire" wrote: Hi, You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Thank you very much. This looks very promising. I'm off to home now, so first
tomorrow I'll give this a go. Arnold Vink "Dave Peterson" wrote: You can use an application level event. This goes in the ThisWorkbook module of your persnlk.xls workbook. (persnlk.xls is the same as the USA version personal.xls???) Option Explicit Public WithEvents xlApp As Application Private Sub Workbook_BeforeClose(Cancel As Boolean) Set xlApp = Nothing End Sub Private Sub Workbook_Open() Set xlApp = Excel.Application End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) Dim NewFileName As String NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak" Wb.SaveCopyAs Filename:=NewFileName End Sub (Instrrev was added in xl2k. If you're using xl97, you'll have to parse the filename differently.) And an alternative... Another option would be to dump autosave and use Jan Karel Pieterse's addin (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) avi wrote: Thank you. That worked. I just have two followup questions: 1. Is this possible to implement this for all .xls files (i.e. use it in persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an actual filename in the macro - it should use something like current_workbook (??). 2. Is it also possible to automatically answer the questions about overwriting existing files with 'yes'? Thank you again, Avi. "Shane Devenshire" wrote: Hi, You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
"Dave Peterson" wrote: You can use an application level event. This goes in the ThisWorkbook module of your persnlk.xls workbook. (persnlk.xls is the same as the USA version personal.xls???) Option Explicit Public WithEvents xlApp As Application Private Sub Workbook_BeforeClose(Cancel As Boolean) Set xlApp = Nothing End Sub Private Sub Workbook_Open() Set xlApp = Excel.Application End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) Dim NewFileName As String NewFileName = Left(Wb.FullName, InStrRev(Wb.FullName, ".")) & "bak" Wb.SaveCopyAs Filename:=NewFileName End Sub (Instrrev was added in xl2k. If you're using xl97, you'll have to parse the filename differently.) And an alternative... Another option would be to dump autosave and use Jan Karel Pieterse's addin (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) avi wrote: Thank you. That worked. I just have two followup questions: 1. Is this possible to implement this for all .xls files (i.e. use it in persnlk.xls) and, if so, what phrasing should I use in the macro to avoid an actual filename in the macro - it should use something like current_workbook (??). 2. Is it also possible to automatically answer the questions about overwriting existing files with 'yes'? Thank you again, Avi. "Shane Devenshire" wrote: Hi, You could add a save as command to the Open_Workbook event. If you only want this to happen when you open the workbook you will probably need to run two consecutive saves - one to do the bak and another to resave the file to where you opened it from. You should be able to record the steps and then add them to the following: Private Sub Workbook_Open() 'your code End Sub This code goes into the thisWorkbook object in the VBE. Press Alt+F11, double-click the thisWorkbook object, for your workbook, in the Project explorer near the top left of the screen. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "avi" wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as .bak when opening .xls
Just a note.............
Excel 2003 does not recognize *.bak files. Backup files are *.xlk Gord Dibben MS Excel MVP On Wed, 18 Mar 2009 07:43:01 -0700, avi wrote: Is it possible to let Excel save the .xls file to .bak when opening it? Preferably with a macro. (I am using Excel 2003). Cheers, Avi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening excel spreadsheet but it didnt save my last work | Excel Discussion (Misc queries) | |||
Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
no updates prompt at opening and save changes at closing | Excel Discussion (Misc queries) | |||
how to get disk icon on save button of save as dialog like 2000 | Excel Discussion (Misc queries) | |||
REF errors when opening excel in xp. works fine when opening wor. | Excel Discussion (Misc queries) |