+ Reply to Thread
Results 1 to 14 of 14

Code that will always save workbook into predefined location... NO EXCEPTIONS!

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Suppose you have a macro that creates a report in a new workbook. You want this report to always be saved to the same path with the same name. If there is an existing report from last time you want the newly created report to overwrite this without any prompt. So far, so easy.

    Now to ramp it up a little.

    What if the previously saved report is currently open in Excel? How can you ensure that the previous report will be overwritten/deleted by the new report without any prompt?

    I'm not sure how much harder this will make it but what if the previously saved report is currently open in another instance of Excel?

    Doable?

    Desired Outcomes
    1. Save workbook in predefined location & filename (I can do this)
    2. If file already exists (and is closed) overwrite it without any prompt (I can do this)
    3. If file already exists and is open in current Excel instance - overwrite (or delete the old/open version then save the new report) without prompt
    4. If file already exists and is open in another Excel instance - overwrite (or delete the old/open version then save the new report) without prompt
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

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

  2. #2
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,084

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    It is not impossible to overwrite a workbook that is currently open elsewhere. But the obvious danger is that the original workbook can be later closed and rewrite itself back, and you've undone all your hard work.

    So it isn't a safe accomplishment to do what you're proposing. In my opinion.

    I would detect the workbook being open or locked for editing by another process, and abort this attempt with a message indicating the workbook is in use.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    80

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Hi,

    Just spitballing here...

    If you add in the excel a ontime event to check every minute if on the network a file (just a dummy file to trigger the auto close) is that makes the excel know there is a new file ready to be saved so the file closes itself on the old data user so your better/newer file can be safed

    doing that you need to think of how this file can know who is using the master file unless this is a different file offcourse

    its an out of the box solution i know...

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by JBeaucaire View Post
    It is not impossible to overwrite a workbook that is currently open elsewhere. But the obvious danger is that the original workbook can be later closed and rewrite itself back, and you've undone all your hard work.
    Thanks for pointing that out. I admit I had overlooked that the previously saved report - if already open - could write back to itself even if its file had been overwritten.


    Quote Originally Posted by JBeaucaire View Post
    So it isn't a safe accomplishment to do what you're proposing. In my opinion.
    Oh I agree. Generally I wouldn't do this either.

    However in the scenario I have in mind for this code, it won't matter. (I will be the only person who can access these reports. I don't intend to make any changes to the reports after the macro has generated them. But even if I did make any changes, these should be lost as the report just run should replace any previously saved report - no exceptions)


    Quote Originally Posted by JBeaucaire View Post
    I would detect the workbook being open or locked for editing by another process, and abort this attempt with a message indicating the workbook is in use.
    That would be the best approach when saving workbooks. This is different. I am looking for a solution that will always save the file to the desired location - regardless of any exceptions e.g. if the file already exists, is in use etc.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,461

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by mc84excel View Post
    I am looking for a solution that will always save the file to the desired location - regardless of any exceptions e.g. if the file already exists, is in use etc.
    Save the newly created report with a unique time stamp in its file name. You will always know which one is the most recent file by its name and no other instance will have it open because it's a unique file name.


    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in your menu for the forum editor will apply the CODE tags around your selected text.

  6. #6
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,084

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    So if the file will always have an exact name, and you are the only one who might accidentally have it open elsewhere, then it's an easy task to spot this file open on your system and close it prior to your other code continuing.

    Assuming the workbook named BOOK1.xlsx

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by AlphaFrog View Post
    Save the newly created report with a unique time stamp in its file name. You will always know which one is the most recent file by its name and no other instance will have it open because it's a unique file name.


    Please Login or Register  to view this content.
    Well that approach would cause the reports to pile up in that location - which would be messy and require manual deletion of the older versions on an ongoing basis! No thanks.

    I want to save the report in the same location & filename every single time. May help to think of it as a rolling report (the recent version will always replace the previous version. I don't need to keep any outdated report)

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by JBeaucaire View Post
    So if the file will always have an exact name, and you are the only one who might accidentally have it open elsewhere, then it's an easy task to spot this file open on your system and close it prior to your other code continuing.

    Assuming the workbook named BOOK1.xlsx

    Please Login or Register  to view this content.
    Thanks JBeaucaire, I will try that soon. Only thing that worries me is, will the code still work if the workbook is open in a separate instance of Excel? (separate to the Excel that is running the VBA)
    I will test the code to find out. I just had this idea that
    Please Login or Register  to view this content.
    would only work if the workbook was open in the same instance of Excel.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,461

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by mc84excel View Post
    Well that approach would cause the reports to pile up in that location - which would be messy and require manual deletion of the older versions on an ongoing basis! No thanks.

    I want to save the report in the same location & filename every single time. May help to think of it as a rolling report (the recent version will always replace the previous version. I don't need to keep any outdated report)
    Manual schmanual; Before saving, have the code delete all the Excel files in the folder (presumably only one or two files, Right?). Use On Error Resume Next to ignore an error if a file is in use. It will delete it the next time you save a file. No muss. No fuss. You're welcome

  10. #10
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,084

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    AlphaFrog's approach is a great idea, I love a simple brute force solution.

    But you still have the problem of a secret open version of the document in another session somewhere getting SAVED later in its session, overwriting your new one, possibly.

    If the workbook(s) in question are macro-enabled, you could install something that saves/closes the workbook after a short period of inactivity...
    https://www.excelforum.com/excel-pro...tain-time.html
    Last edited by JBeaucaire; 12-04-2019 at 03:41 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    Quote Originally Posted by JBeaucaire View Post
    AlphaFrog's approach is a great idea, I love a simple brute force solution.
    Except that I have other excel files in this folder I want to keep. Which kinda puts a damper on the idea of having code delete all other Excel files in the folder! (Maybe it's just me but I find the idea of having code blindly bulk delete files more risky than having code blindly overwrite a single file even if it is in use.)

    Besides this isn't what the thread is asking for


    Quote Originally Posted by JBeaucaire View Post
    But you still have the problem of a secret open version of the document in another session somewhere getting SAVED later in its session, overwriting your new one, possibly.
    Correct.

    BTW I tested your code.

    OUTCOME 3 = If I have the last saved version open in the same instance of Excel that the code is running in, it closes it.

    OUTCOME 4 = However if I have the last saved version open in another instance of Excel than it doesn't close it.

    I don't intend to have the report open in another instance of Excel but if I ever did, I would like the code to handle this anyway (always save in set location NO EXCEPTIONS! )


    Quote Originally Posted by JBeaucaire View Post
    If the workbook(s) in question are macro-enabled, you could install something that saves/closes the workbook after a short period of inactivity...
    https://www.excelforum.com/excel-pro...tain-time.html
    That's an interesting suggestion. Problem is that I am saving a new unsaved workbook. The code is run from a XLAM and it outputs a report as an unsaved workbook - the report doesn't contain any code. (Even if I could use this suggestion, there is the risk that the new report could be saved before the timer kicks in on the open file)

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,732

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    There is only Outcome 4 left to solve. I'm thinking the most likely solution to work would be to detect if there are any other instances of Excel running then close these without saving before saving the new workbook.

  13. #13
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,084

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    It appears you have a promising solution in your other thread dedicated to the 4th problem. Let us know if that works for you.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,636

    Re: Code that will always save workbook into predefined location... NO EXCEPTIONS!

    You could just use GetObject. It will either open the file, or grab a reference to it if it's open elsewhere. Then close, and kill.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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