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

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.

Originally Posted by JBeaucaire

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.

Originally Posted by JBeaucaire

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)

Originally Posted by JBeaucaire

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.

Originally Posted by mc84excel

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.

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.

Originally Posted by AlphaFrog

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)

Originally Posted by JBeaucaire

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.

Originally Posted by mc84excel

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

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

Originally Posted by JBeaucaire

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

Originally Posted by JBeaucaire

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! )

Originally Posted by JBeaucaire

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)

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.

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

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.

Originally Posted by JBeaucaire

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

Finally got time to test it. It didn't work. (Closes if same application. Doesn't if not)

Originally Posted by rorya

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.

I'm not familiar with GetObject. From a quick peruse of the MS help, I think this approach sounds promising.

Do I use the GetObject to set the workbook to an object type variable? How do I close it from this variable? Same as Workbook.Close?

Just pass the full path to the workbook to it and it will return a Workbook object reference, which you can then close. It is slight overkill in as much as it will open the workbook if it isn't already open, only for you to close it, but it's simpler than the API calls to iterate through all Excel instances.

Thanks rorya. I will try this out within a week and let you know how it goes.

Re overkill - No problem. I will use a function to test if file is already open before running the GetObject code.

Originally Posted by rorya

Just pass the full path to the workbook to it and it will return a Workbook object reference, which you can then close. It is slight overkill in as much as it will open the workbook if it isn't already open, only for you to close it, but it's simpler than the API calls to iterate through all Excel instances.

Perfect. Thanks Rory. Just what I was looking for.

For anybody with the same problem, below is code as proof of concept / to test that this code works:

Please Login or Register  to view this content.