为什么新打开的Workbook.Name与Excel窗口中的Workbook标题不同? [英] Why is freshly opened Workbook.Name different from Workbook title in Excel window?

问题描述

我已经创建了一个文件，并将其引用为 GlobalFile .
然后，我将其另存为"Glo"，然后另存为"Ume".然后，我重新打开保存的文件，以两个不同的工作簿对象中的两个不同的名称打开两个不同的工作簿: GlobalFile 和 NightMareFile .

打开后，Excel窗口分别具有正确的标题"Glo.xls"和"Ume.xls"，但是测试 NightMareFile.Name 会导致"Glo.xlsx"！>

这怎么可能?

---

我使用的是Win 10 64位，Excel 365 16位.

已经尝试过:

• 打开之前或之后的DoEvents
• 打开后刷新全部
• Excel重新启动没有带来任何变化.

是什么让我动容:更改两个打开的块的顺序可以解决名称冲突:如果首先打开"Ume"，则其名称正确，而"Glo"也是如此.

最初，我保存并重新打开了多个命名文件版本，但是只有此版本总是有问题的，因此使用了新名称:NightMareFile.无论我如何更改打开文件的顺序，该文件总是将在他之前打开的文件的名称继承到另一个对象变量中.

 选项显式子main_control_routine()昏暗的GlobalFile作为工作簿昏暗的NightMareFile作为工作簿设置GlobalFile = Workbooks.AddDebug.Print"GlobalFile.Name:"&GlobalFile.NameApplication.DisplayAlerts = FalseGlobalFile.SaveAs文件名:="Glo"Debug.Print全局文件准备好了！"'GlobalFile另存为UmeGlobalFile.SaveAs文件名:="Ume"Debug.Print"GlobalFile.Name:作为Ume"&GlobalFile.NameApplication.DisplayAlerts = True'GLOBAL重新开放给GlobalFile设置GlobalFile = Workbooks.Open("Glo"，False)Debug.Print"GlobalFile.Name:"&GlobalFile.Name梅花重新开放给NightMareFile设置NightMareFile = Workbooks.Open("Ume"，False)Debug.Print"NightMareFile.Name:"&NightMareFile.Name结束子 

解决方案

如果与您要打开的名称相同的工作簿已经打开，并且您正在尝试将工作簿对象变量分配给返回 Open()方法的值，则最终结果可能无法预测.

例如-如果我在两个工作簿"Glo"和"Ume"都已经打开的情况下运行它:

  Sub main_control_routine()昏暗的wb作为工作簿设置wb = Workbooks.Open("Glo.xlsx"，False)Debug.Print wb.Name设置wb = Workbooks.Open("Ume.xlsx"，False)Debug.Print wb.Name设置wb = Workbooks.Open("Glo.xlsx"，False)Debug.Print wb.Name设置wb = Workbooks.Open("Ume.xlsx"，False)Debug.Print wb.Name结束子 

...这是输出:

  Ume.xlsxUme.xlsxUme.xlsxUme.xlsx 

不是您所期望的.

在我的测试中，它看起来像没有获得预期的工作簿，而是返回了对上次打开的工作簿的引用.

解决方法是始终检查工作簿是否已经打开，然后再使用 Workbooks.Open()对其进行引用.

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:

• DoEvents before or after Open
• RefreshAll after open
• Excel restart brought no change.

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

解决方案

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.

这篇关于为什么新打开的Workbook.Name与Excel窗口中的Workbook标题不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！