Workbook.Open & Error Handling Best Practice

Hi again... I know how to check to see if the workbook exists and I found a routine to check to see if the workbook is currently open but instead of exiting the sub early, I would like to allow the user to either have the opportunity to close the open workbook prior to proceeding or even better by making use of the open workbook if it is indeed open.
I currently use the set wb1 & wb2 to open specific workbooks if they exist and are not open... can I use error handling to set an already open workbook and continue with my code?
Thus using:

Set wb2 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName2) 'If the workbook is not open

Set wb2 = the open workbook 'If workbook is already open... not sure how that would be coded!

I have been bad at using error handling but decided that I should start... I always try to code to cover all situations but I now realize that if other users are using, they will find new ways of creating errors. You can't cover everything and if you try... you will have a lot of extra un-necessary code (which is what I have now!)

Thanks
Craig

Please Login or Register  to view this content.

I needed to add the Set statement in for this to work but work it does! Thanks
To also add a check to see if the file exists would I be better to add another error handler or use a - if Dir("c:\excelforum.xls")

Please Login or Register  to view this content.

Awesome thanks....

So with 8 or 9 lines of code, I checked if the file exist, if the file is open, set workbooks.open or Set the open file.
This will save many lines of code, I wonder why I see other functions and ways to check these items using many many more lines of code. This way is so simple!

Is there any situation that doing this could fail?

Please Login or Register  to view this content.

Sorry, in testing I used my own version of the code with my own workbooks...
I see what you mean, based on this code sample I made adjustments using my own references.

Please Login or Register  to view this content.

So in this case if the workbook was open it would be set else if the workbook is closed it would be opened and set else after the error reset and wb is still equal to nothing it would exit the sub.

Craig

After testing both above methods a little more I found that neither sample code was able to detect if the path file was open in another instance of excel, only if it was open in the same instance.


This version opened a Read Only version of the file that was already open in another instance.

Please Login or Register  to view this content.

This version reported that the file wasn't open but existed when the file was open in another instance of excel...

Please Login or Register  to view this content.

Is there a way to detect if the file was open in another instance of excel, using the above methods?

Craig

Yes, in my tests... I used your code as sent, I am not directly referencing the path. This code will detect the open workbook only if it is open with the same instance of excel, the same as the code from JasperD. If another instance of excel is open... the return generated states that the file isn't open but is on disk.

Please Login or Register  to view this content.