Disable Excel warning “’Filename’ is already open. Reopening will cause..."

[newnewbie]

Hi,
How do I disable the warning Excel gives me when I am trying to open a document with the same name? I do not care is the changes are discarded, just need to switch the warning off and let the users reopen the file (equivalent of “Yes” on the warning box).

Please guide me in the right direction.

Thanks!
Lena

[NeoPa]

Are you doing this via code Lena, or is this a query about setting options in Excel generally?

[newnewbie]

It's a generic/default Excel warning and I guess I have a number of ways to go about it, I just do not know which way is better.

I have number of financial spreadsheets all bundled up in a reporting suite in OutlookSoft (SAP BPC 7.0):
Menu with options/links to (e.g.) Fin. Scorecard, P&L, Actual vs Budget.xls files. When users click on one of these options, e.g. Scorecard, the Scorecard opens. There is a button there that "takes" the user back to the Menu file by opening another Menu (so it is not a link to the Menu file, it's a formula that opens a new Menu file). However, if the Menu file is already open, this warning pops up, naturally. Since each of the original files are actually templates, I do not care if the user opens another file, the users do not need to save changes anyway.

So I my options are:

1. Put the code in each of the files(Scorecard, Act vs Budget, etc) that opens the new Menu file each time ignoring the open Menu and switches the warnings off.
2. Disable the warning in all Excel altogether.

I think I can do the #1, but I have never dealt with built in warnings.

[NeoPa]

Strange.

When I tried, I was unable to duplicate the behaviour (Excel 2003). Even if I made changes, opening the same file would simply select the version already open and amended.

Nor was I able to see any options that would allow one to disable this behaviour.

Later on, I tried by opening the file explicitly (before I'd used the recently opened file list from the file menu), when I was able to see the error.

If you are doing this in code then there are various methods to fool the app into not popping up the warning. I'm fairly confident this cannot be done generically though (nor would I recommend it if it could to be honest).

[newnewbie]

I found some code here http://support.microsoft.com/kb/209189 that looks at the file to see if it is open or not. I modified it a bit to see if the file is open, if not, open a new one, if yes, switch to the open version. Will test it on the reporting suite on Monday.

I am in Excel 2007 complicated by the CubeAnalysis add in and SAP BPC 7.0 (former OutlookSoft). The formula that "takes back" the user to the Menu is outlook soft formula that references the application, outlooksoft navigation menu and displayed message 'Back to the Menu". It would be hard to recreate the same behavior. Also, it is only recently that we switched to Excel 07. When we had Excel 03 we did not have the problem. I do not know how to compare the two versions (I have two machines with the old Excel 03 and Sap BPC 05 and new Excel 07 and BPC 07 so I could compare if I knew how). I looked at general Excel options and they look the same. I looked behind the scenes - whatever little VBA we have there is the same in both versions. There is an add in Project there Ex4excel.xla (if I remember correctly now) that's password protected. Not sure what it does or how to get in.

I think my little solution will work on Monday. I will replace the outlook formula in all templates with the macro to see if the Menu is already open, if not, open a new one, if yes, refer to the open one.

As always, your questions help me think in the right direction. Thank you!

[NeoPa]

@newnewbie
No worries.

It seems like NewNewbie isn't such a newb at all from reading what you did to resolve your problem. A pleasure working with you.

[newnewbie]

Thank you, that's so nice to hear. I am still so green though. I am not good at writing my own code at all. What I am getting fairly good at is plagiarizing and tweaking :) And sometimes I am stumped by a simplest thing. I never had any formal VBA, Access or Excel training. I am hoping my company will finally send me to a basic VBA course to patch so many holes in my skills.

[NeoPa]

Plagiarizing and tweaking are good Lena :)

Sometimes courses can be helpful that's true. They can answer questions you've had outstanding for ages, and even some questions you didn't know you needed to ask. Remember though, that none of the time you're struggling is wasted. It's all learning and you're progressing as you struggle.

[FishVal]

Just a thought.

If excel workbook is set to be shared (via MainMenu -> Tools -> Share Workbook ...), then it could be opened by multiple users without any notifications.