+ Reply to Thread
Results 1 to 26 of 26

Automatic save as backup when opening file

  1. #1
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Automatic save as backup when opening file

    I have been searching this site for assistance from other posts and have come up with variations, but I am not coversant enough to convert to what I need.

    Can someone give me a VBA script or is it Bolean? One that auto saves a file as it opens one.
    I'd like to have the file save the original name, but add date and time if possible.

    As I have multiple users I cannot use read only, as I need up to date data.

    For this purpose file name is "Class Roll 2015" Location C:\Rolls\
    and
    the folder for save as is in the same folder, name of ABC Backups Location C:\Rolls\ABC Backups\

    Is there a VBA script out there that can do this?

    thanks
    Last edited by Christopherdj; 01-29-2015 at 08:02 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    This is untested and the format may not be what you want for the date and time:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Doesn't seem to work - I was hoping it would save the backup as it opens. I'm well out of my depth here as to how to fix.
    I have found this on the forum but I don't know enough to modify. It looks similar to what you've done.
    OMG this is doing my head in......

    Please Login or Register  to view this content.
    Last edited by Christopherdj; 01-19-2015 at 08:18 PM.

  4. #4
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Automatic save as backup when opening file

    Try this; change the file location.
    Please Login or Register  to view this content.
    Last edited by Blokeman; 01-19-2015 at 09:01 PM.
    Please click the * Add Reputation if this helps
    If solved remember to mark Thread as solved

    "I'm glad to help and this is not meant to sound smart, but either you have super-human vision to see all those controls cleared one by one with the code I posted, or your computer is really slow."

  5. #5
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Blokeman - still not working.
    Wondering if this should be a .bat file attached to the file? It should execute the "Save As" as it opens the file, creating a file in the backup directory, but opening the original.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    Thanks for the rep!

    Does the ABC directory exist? Should we create that folder? I'll look again tomorrow

  7. #7
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Yes, it does exist. So I'm not sure what the problem is. I"ll upload a sample when I get back to work. Thanks for the help.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    I made a Rolls Folder a Class Roll 2015 workbook and an ABC Backups folder in the Rolls folder and ran my macro without a hitch! So, what else is going on??

  9. #9
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Automatic save as backup when opening file

    Try this then:-
    Please Login or Register  to view this content.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  10. #10
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    OK, well I'm obviously doing something wrong.
    File attached, I added the script, saved as Macro enabled, then when I opened the file - nothing saved into the directory, nothing shows in the macro area?
    Confusion reigns !!!
    Thanks for trying ppls, what am I doing wrong?

    ps How does a macro do an auto save upon opening of the file? I thought macro's had to be activated once the file was opened (manually by selecting and running it?)
    Attached Files Attached Files

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    This works:

    Please Login or Register  to view this content.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    You may not want to do what you have planned because you'd end up with a lot of files, but there are event procedures: Open, BeforeClose etc.

    I'm cogitating, maybe I'll come up with a more viable solution??

    Try this - it's a before close event in the ThisWorkbook module: Roll Master 2015.xlsm

  13. #13
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Your solution will do the job I need.

    Question: instead of stipulating the file path from C:\ is it possible to use the current directory?
    I assume we can change the file path to the active directory?
    That way when I distribute the file, all they need to do is setup the sub folder. Or can we script to create the folder if it does not exist?

    Questions:
    Does it have to auto save without a prompt?
    Why..... if somone goes into the file, has a bit of a play and screws with the data, they may think exiting without selecting save will restore the file back to original (on open).
    Last edited by Christopherdj; 01-21-2015 at 05:23 PM.

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    The backup happens at the close and any drive is ok as long as the file folder is set up. But this code will set up the backup folder for you in any drive:

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Almost there - and thanks so much, I really owe you for this.....again!!!

    1. Can we change date format from mm-dd-yy to dd-mm-yy (I tried to edit the script, but it defaults back to the original format).

    2. What is the correct way to insert the script above? Is it via Macro's or via Visual Basic?
    (When I try to copy the code into my file, it worked on one, not on the other, yet I did the same process, created a macro, then pasted the code over it.)

    3. Is it possible to prompt the "real" save, but create the backup anyway on close?
    Why..... if somone goes into the file, has a bit of a play and screws with the data, they may think exiting without selecting save will restore the file back to original (on open).
    Last edited by Christopherdj; 01-22-2015 at 05:35 PM.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    It seems to work?

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Hi - sorry but for some reason I can't get the script to run/hold so it saves as per your last feedback.

    Can you explain how it is placed into the file. I've gove in via using Developer, Macro - recored/create one, then paste over the top with your script. Doesn't remain/work.
    Then via Developer, Visual Basic, pasting your script into the file, save, and again...... it doesn't work.

    What is it I'm doing wrong?
    Last edited by Christopherdj; 01-27-2015 at 01:24 AM.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    You haven't put the new code in the ThisWorkbook module. Roll Master 2015 EXCELFORUM creates backups MkDir.xlsm

  19. #19
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    That's because I am unsure of how to do it. Can you direct me - I thought I was doing it right, but as it won't work I need to confirm what I'm doing. Sorry.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Hi Chris,

    Did you open the file I sent? - When you do check out the ThisWorkbook module - the code is there.

  21. #21
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    OMG - I'm feeling like a fool. I had to double click for it to show.....!!! Now I understand. Thanks so much - and apologies for hounding you.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    That's OK Chris, and you're welcome!

  23. #23
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    Just one more question - the code autosaves the actual file as well as creating the backup, it does not prompt for a save.
    I'd like to have the prompt to "Save or Don't Save", so that if changes made to the file are not required, it will close without saving changes.
    The reason I ask is, if someone goes into the file, has a bit of a play and screws with the data, they may think exiting without selecting save will restore the file back to the original (as it was on opening).

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    Replace the code with this:

    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    320

    Re: Automatic save as backup when opening file

    That will do the trick just nicely, thanks again.

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Automatic save as backup when opening file

    You're welcome and - thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Auto save a backup copy in a seperate location OR save file with a pop up
    By kdsanderson30 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2014, 12:38 PM
  2. [SOLVED] Automatic Backup of Excel File on a daily basis
    By Mr.India in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2014, 02:06 AM
  3. save:Automatic Backup
    By Zan03 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2013, 12:41 PM
  4. EXCEL file automatic regular (schedulled) backup in your email
    By Sobirjon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 01:41 PM
  5. Replies: 1
    Last Post: 01-07-2006, 09:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1