Referencing sheets in another workbook by codename using VBA [duplicate]

Question

I am attempting to copy data from one workbook to my current workbook using VBA. InputBook is a workbook object referring to the file from which I would like to extract data. The main issue has to do with referencing particular worksheets in the InputBook workbook. In InputBook, I have a worksheet named "Lines" with the codename LINES. I would prefer to reference this worksheet by its codename, for example:

NumItems = WorksheetFunction.CountA(InputBook.LINES.Columns(1))

This clearly doesn't work and I know I can make it function by using either of the following:

NumItems = WorksheetFunction.CountA(InputBook.Sheets("Lines").Columns(1))
NumItems = WorksheetFunction.CountA(InputBook.Sheets(2).Columns(1))

I would, however, rather not use either of those methods as they seem to be less robust. Is there any way to reference the codename of a worksheet object in another open workbook? Thanks.

Answer 1

You can "hack" a reference to another workbook sheet code name by:

Sub UseCodeNameFromOutsideProject()
    Dim WS As Worksheet
    With Workbooks("InputBook .xls")
        Set WS = _
            .Worksheets(CStr(.VBProject.VBComponents("Lines").Properties(7)))
        debug.print WS.Name
    End With
 End Sub

Your WS object is now set to the sheet which has the codename "Lines" in this example.

---

Original inspiration is here.