I have created a file and referenced it as GlobalFile
.
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: GlobalFile
and NightMareFile
.
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.
Already tried:
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.