I have created a file and referenced it as
Then I saved it as "Glo" and then as "Ume". Then I reopen the saved files to have two distinct workbooks open on two different names in two different Workbook objects:
After opening, Excel windows has the correct headers as "Glo.xls" and "Ume.xls" respectively, but testing
NightMareFile.Name results in "Glo.xlsx" !!!
How is this possible at all?
I'm on Win 10 64 bit, Excel 365 16 bit.
What makes me pull my hair: Changing the order of the two open blocks fixes the name conflict: if "Ume" is opened first it has the correct name, as does "Glo".
Originally I had more named file versions saved and reopened, but only this one was always faulty, hence the new name: NightMareFile. No matter how I changed the order of files to open, this one always inherited the name of the file opened before him into another object variable.
Option Explicit Sub main_control_routine() Dim GlobalFile As Workbook Dim NightMareFile As Workbook Set GlobalFile = Workbooks.Add Debug.Print "GlobalFile.Name: " & GlobalFile.Name Application.DisplayAlerts = False GlobalFile.SaveAs Filename:="Glo" Debug.Print "GLOBAL File ready!" 'GlobalFile save as Ume GlobalFile.SaveAs Filename:="Ume" Debug.Print "GlobalFile.Name: As Ume " & GlobalFile.Name Application.DisplayAlerts = True 'GLOBAL reopened to GlobalFile Set GlobalFile = Workbooks.Open("Glo", False) Debug.Print "GlobalFile.Name: " & GlobalFile.Name 'Ume reopened to NightMareFile Set NightMareFile = Workbooks.Open("Ume", False) Debug.Print "NightMareFile.Name: " & NightMareFile.Name End Sub
EDIT: as of Sept 2023 (Excel 365, Version 2307, and maybe before that) it looks like this issue has been resolved. You may still want to check, if you're running an older version.
If a workbook with the same name as the one you're trying to open is already open, and you're trying to assign a workbook object variable to the return value of the
Open() method, then the end result can be unpredictable.
For example - if I run this with both workbooks "Glo" and "Ume" already open:
Sub main_control_routine() Dim wb As Workbook Set wb = Workbooks.Open("Glo.xlsx", False) Debug.Print wb.Name Set wb = Workbooks.Open("Ume.xlsx", False) Debug.Print wb.Name Set wb = Workbooks.Open("Glo.xlsx", False) Debug.Print wb.Name Set wb = Workbooks.Open("Ume.xlsx", False) Debug.Print wb.Name End Sub
...this is the output:
Ume.xlsx Ume.xlsx Ume.xlsx Ume.xlsx
Not what you'd expect.
In my testing it looks like instead of getting the intended workbook it returns a reference to the last-opened workbook.
The fix would be to always check if the workbook is already open before using
Workbooks.Open() to get a reference to it.