none
Workbooks.Open returns wrong object RRS feed

  • Question

  • I have an old VB6 program that has been working for many years, and users who have upgraded to Excel 2016 are starting to report problems. On investigation it appears that the Workbooks.Open method is opening the workbook correctly, but is returning a reference to the wrong workbook object. 

    Is this a known bug in Excel 2016?

    I expect I can workaround the problem, by using the filename to find the correct object in the Workbooks collection, but if there is an update or patch available for Excel, I can recommend this to our users rather than having to produce a new version for everyone.

    Phil

    Tuesday, September 18, 2018 4:50 PM

All replies

  • Hi Phil,

    What error did you get? I assumed that you get an error of "Run-Time error '9' ".

    You could try to close all workbooks and relaunch the Excel first.

    If the problem persists, you could try the steps in the following link to troubleshoot your Workbooks Collection:

    Troubleshooting the Workbooks Collection

    For more information, please review the following link:

    Excel VBA: Workbooks.Open returns the wrong object

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, September 19, 2018 6:12 AM
    Moderator
  • Hi Phil,

    Thanks for your asking. Did you resolve your issue ? If the post helps you, you can mark it as answer and close the post.

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

    I wish you a happy life!

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, September 21, 2018 9:14 AM
    Moderator
  • Hello thanks for your reply. Sorry for not coming back sooner, but for some reason I never got a notification email that anyone had responded.

    I have seen that stack overflow question, but the 'answer' given there is not a practical solution.

    I do have a workaround for the problem, but this definitely looks like a fault in Excel 2016 to me, and therefore is likely to be potentially affecting many people's applications and macros. It would therefore be very useful if Microsoft can acknowledge that this is a fault in this version, and issue a patch to fix it, or at least document the fault.

    The people commenting on the Stack Overflow question appear to be getting the problem intermittently and it goes away when they close and re-open Excel. Our program has multiple workbooks open, and the user may also have other workbooks open, so this isn't a viable option. The program has been running for many years with many users using various versions of Excel and we've not seen this problem before as far as I recall. We now have users starting to upgrade to Excel 2016 and reporting this problem. It seems to happen consistently with this application.

    My workaround is to locate the workbook in the workbooks collection after opening the new workbook and get the object reference from there, as the Open function no longer appears to return the correct reference in this version of Excel. Instead it appears to be returning a reference to one of the installed .xla addins.

    Phil.


    Monday, September 24, 2018 2:07 PM
  • I forgot to add. Yes, I do get Error 9, but that is because the program goes on to reference a specific Sheet in the opened workbook. As the reference is pointing to the wrong workbook, the sheet does not exist which causes error 9. If my program wasn't explicitly referencing this sheet by name the result could have been much worse. It could have potentially updated some other unrelated workbook.
    Monday, September 24, 2018 2:20 PM
  • Hi Phil,

    We will test it and feedback to the related Team.

    Also, please add your voice to this UserVoice suggestion:  

    Excel for Windows (Desktop Application)

    Thanks for your understanding.

    Best Regards,

    Yuki



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Wednesday, September 26, 2018 1:37 AM
    Moderator
  • Uservoice should be for enhancements, not bug fixes.

    The problem is still there.

    I'll re-post.

    Wednesday, April 10, 2019 11:17 AM
  • Workbooks.Open didn't work for me either while trying to run a couple different VB routines.  After comparing my PC to another user, I discovered the ADD-INs were Inactive.  After Inactivating all Add-Ins, my VB routines worked.

    Even more confusing, out of curiousity, I turned the ADD-INs back on and the VB routines are continuing to work.

    Try Inactivating all the Add-Ins that are Active in Excel, then run your VB code.  After that, it's up to you which Add-Ins you can or want to reactivate.

    I had to share after spending hours sleuthing this issue out.  Hope I helped!

    Jeff K.


    • Edited by JifK Tuesday, December 10, 2019 1:17 PM
    • Proposed as answer by JifK Tuesday, December 10, 2019 1:18 PM
    Tuesday, December 10, 2019 1:17 PM
  • To: JifK
    re:  workbooks

    Have you all tried:  Application.Workbooks.Open ?
    Tuesday, December 10, 2019 5:28 PM