Excel VBA: Workbooks.Open returns the wrong object [closed]

Question

In Excel 2016 (don't remember it happening in earlier versions), I'm beginning to encounter issues where the Workbooks.Open command does not return the correct Workbook object. For example:

Dim wbkHelper as Workbook
Set wbkHelper = Workbooks.Open(filePath, ReadOnly:=true)

The Open command functions correctly - it opens the "filePath" file like it's supposed to - but wbkHelper ends up pointing to the completely wrong file, typically the ThisWorkbook object. The wbkHelper object is never reassigned to after that point in the code, so it's not something the code is doing. This only happens sometimes and can be difficult to reproduce, but it breaks the entire macro when it does happen.

I can think of numerous ways to work around this, but it shouldn't be necessary? This is a core VBA function that's used all the time, and it's something you expect to just work.

EDIT: This is easily reproducible, please see my comment @Marc.Meketon that follows @Phil Preen solution below.

Answer 1

I am seeing this with an old application written in VB6 that has been working for years, and users who are upgrading to Excel 2016 are starting to report problems. In my case it seems to be happening pretty consistently (seems to return a reference to one of the .xla addins). As the program has multiple Excel files open, and the user may also have other files open, closing and re-opening Excel every time it opens a new workbook is not a practical solution.

Until Microsoft acknowledge this as a fault in Excel, or someone can figure out what conditions cause the problem to occur, I would definitely recommend that you ignore the return value from the Workbooks.Open method, and instead use the filename to get the Workbook object reference from the Workbooks collection afterwards.

Dim wbkHelper as Workbook
Set wbkHelper = Workbooks.Open(filePath, ReadOnly:=true)
Set wbkHelper = Workbooks(filename) ' note filename needs to be filepath without the path