如何通过另一个工作簿中的 VBA 对象名称引用 Excel 工作表? [英] How to refer to a Excel Worksheet by its VBA Object Name in another Workbook?
问题描述
我有两个 Excel 工作簿:
I have two Excel Workbooks:
Source.xlsx
Tool.xlsm
Source.xlsx
包含一个带有 VBA 对象名称 shtTests
的工作表:
Source.xlsx
contains a Worksheet with the VBA Object Name shtTests
:
假设在 Tool.xlsm
中有一个变量,其中包含对存储在 Source.xlsx
中的工作簿的引用:
Let's assume that in Tool.xlsm
I have a variable that contains a reference to the Workbook stored in Source.xlsx
:
Dim wkbSource as Workbook
Set wkbSource = GetSourceWorkbook() ' Some function that gives a reference to the workbook
核心问题:如何使用 shtTests
' 在 Tool.xlsm
中引用 shtTests
VBA 名称?
Core Question: How can I reference shtTests
within Tool.xlsm
by using shtTests
' VBA Name?
或者将问题表述为代码...假设您有以下代码片段:
Or to formulate the question as code... assume you have this code snippet:
Dim wkbSourceShtTests as Worksheet
Set wkbSourceShtTests = GetShtTestsFromWkbSources(wkbSources)
问题:GetShtTestsFromWkbSources
应该是什么样的?
注意:我不想像使用 wkbSources.Worksheets("Test Cloning")
因为人们可能有一天会更改其 Excel 名称.
Note: I do not want to reference it by its Excel Name like you would do using wkbSources.Worksheets("Test Cloning")
because people might change its Excel Name some day.
推荐答案
如果你想要一个函数而不是设置可信访问,那么这可能会奏效:
If you wanted a function instead of setting up the trusted access then this would probably work:
Sub example()
Dim wkbSource As Workbook
Set wkbSource = GetSourceWorkbook()
Dim wkbSourceShtTests As Worksheet
Set wkbSourceShtTests = GetShtTestsFromWkbSources(wkbSource, "shtTests")
End Sub
Function GetShtTestsFromWkbSources(wkbk As Workbook, codename As String) As Worksheet
For Each sht In wkbk.Sheets
If sht.codename = codename Then Set GetShtTestsFromWkbSources = sht
Next
End Function
这篇关于如何通过另一个工作簿中的 VBA 对象名称引用 Excel 工作表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!