按名称访问工作表，包括ThisWorkbook [英] Access Worksheet by name including ThisWorkbook

问题描述

这困扰了我一段时间.我想按名称访问工作表，而不是如何在Excel中命名工作表".我还想在行中包含 ThisWorkbook ，以消除使用同名工作表打开多个工作簿的可能性.但是我遇到一个错误:

This has been bothering me for a while. I want to access Worksheet by name instead of "how worksheet has been named in Excel". I would like also to include ThisWorkbook in the line, to eliminate possibility of having several Workbooks open with Worksheets with the same name. However I am getting an error:

我已将工作表命名为 CalculationItem1 .为什么跟随无效?

I have named my Worksheet CalculationItem1. Why following does not work?

Sub TestTest()
    ThisWorkbook.CalculationItem1.Range("A1:A2").Copy
End Sub

出现以下错误:

以下工作正常，但是是否有可能在另一个打开的工作簿中使用相同的命名工作表?然后会出现错误吗?

Following works but there is a possibility of having same named Worksheet in another opened Workbook? Then errors can appear?

Sub TestTest()
    CalculationItem1.Range("A1:A2").Copy
End Sub

---

这是名字:

---

Here is the name:

推荐答案

只要工作表与代码("ThisWorkbook")位于同一工作簿中，就可以通过其代码名称访问工作表.即使工作簿未处于活动状态，也是如此-就像您有一个具有该名称的对象变量一样.因此，使用 CalculationItem1.Range("A1:A2").Copy 将始终引用代号为 ThisWorkbook 的代号为 CalculationItem1 的工作表.

As long as the sheet is in the same Workbook as the code ("ThisWorkbook"), you can access the sheet via it's code name. That is true even if the Workbook is not active - it's like you have an object-variable with that name. So using CalculationItem1.Range("A1:A2").Copy will always refer to the sheet with the code name CalculationItem1 of ThisWorkbook.

如果要通过代号访问另一个工作簿的工作表，则必须迭代工作表并查找属性 CodeName (如FaneDuru在其答案中所示)).

If you want to access a worksheet of another workbook via code name, you have to iterate the sheets and look for the property CodeName (as FaneDuru shows in his answer).

这篇关于按名称访问工作表，包括ThisWorkbook的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！