2

I have two Excel Workbooks:

  1. Source.xlsx
  2. Tool.xlsm

Source.xlsx contains a Worksheet with the VBA Object Name shtTests:

Screenshot of VBA Properties for shtTests

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

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)

Question: What does GetShtTestsFromWkbSources have to look like?

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.

Hauke P.
  • 2,695
  • 1
  • 20
  • 43
  • You'd write a loop that goes through each sheet in that workbook and compares its codename with the one you are interested in. Or set a reference to the VBA project of the other workbook and refer to it directly as a member of that project. – Rory Jun 27 '17 at 09:51
  • Possible duplicate of [Excel tab sheet names vs. Visual Basic sheet names](https://stackoverflow.com/questions/2649844/excel-tab-sheet-names-vs-visual-basic-sheet-names) – Luuklag Jun 27 '17 at 09:51

2 Answers2

3

Is this what you are trying?

Sub Sample()
    Dim wbThis As Workbook, wbThat As Workbook
    Dim wsThat As Worksheet
    Dim wsCodeName As String

    Set wbThis = ThisWorkbook
    Set wbThat = Workbooks("Book4") '<~~ Change this to relevant workbook

    wsCodeName = "ShtSheets"

    Set wsThat = wbThat.Worksheets(CStr(wbThat.VBProject.VBComponents(wsCodeName).Properties(7)))

    Debug.Print wsThat.Name
End Sub

Note: For this to work, you need to enable access to Visual Basic Projects

On the File menu Excel, Click Options|Trust Center|Trust Center Settings|Macro Settings, Check the box "Trust Access to the VBA Project Object Model"

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    Note that you have to have trusted access to the VBA project for that to work of course. – Rory Jun 27 '17 at 09:52
1

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
CLR
  • 11,284
  • 1
  • 11
  • 29
  • While I like both solutions, they both use the `Worksheets` collection for looking up the respective sheet. So I prefer this solution because it requires less pre-requisites. Thanks a lot both to CLR and to Siddharth Rout. – Hauke P. Jun 27 '17 at 11:13