8

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.

teepee
  • 2,620
  • 2
  • 22
  • 47
  • does `NumItems = WorksheetFunction.CountA(LINES.Columns(1))` work? It should if you have it defined as `set LINES = InputBook.Sheets("Lines")` – mr.Reband Mar 07 '14 at 17:25
  • I haven't defined LINES anywhere in the code; it was only set manually, permanently as the codename for that particular worksheet. I want to avoid altogether having to define it as a function of the worksheet name. – teepee Mar 07 '14 at 17:34
  • 2
    Late answer, but I posted on this: http://yoursumbuddy.com/using-worksheet-codenames-in-other-workbooks/ – Doug Glancy Nov 07 '14 at 20:10

2 Answers2

12

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.

enderland
  • 13,825
  • 17
  • 98
  • 152
  • and what the benefit of your approach? why it's better than `Set Ws=InputBook.Sheets("Lines")`? – Dmitry Pavliv Mar 07 '14 at 17:32
  • 3
    @simoco if the user changes the sheet name (not CodeName) to be say, "my Lines" the code you wrote will break. Referencing the `CodeName` means the only time it will not work is if someone edits the `CodeName` in the VBA IDE. It's almost always advantageous to reference `CodeName` rather than the Sheet name for this reason. – enderland Mar 07 '14 at 17:33
  • +1 you're right, actually I misundersood the question when read it first time:) – Dmitry Pavliv Mar 07 '14 at 18:25
0

I could call a workbook "I LIKE TEA" but InputBook.I LIKE TEA.Columns(1) wont cut it.

Abstract the name away with a const or:

public enum InputSheets
   LINES = 2,
   GARMENTS = 4
end enum

to allow:

InputBook.Sheets(InputSheets.LINES).Columns(1)

or 

MyGetSheetFunction(InputBook, InputSheets.LINES)

You could take this further and use a wrapper class.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    Thanks for your help. If I were to use this method, then I will have to be concerned about the sheet order in the InputBook file, right? Unfortunately I can't be sure that it will remain in place. I am trying to avoid any dependence on sheet location or sheet name and am looking to link directly to the codename. Is there no support for this type of operation? – teepee Mar 07 '14 at 17:41
  • Name and position are the only two identifying factors. You could iterate all the sheets looking for some unique bit of data in a cell. – Alex K. Mar 07 '14 at 17:45
  • OK, that's fine. I hoped the codename was accessible but an iterative search will be a reasonable work-around. Thanks for all your help. – teepee Mar 07 '14 at 20:58