Announcement

Collapse
No announcement yet.

Reference a Range by Name in Other Workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reference a Range by Name in Other Workbook



    This should be easy, but it's driving me crazy...

    I have a workbook open and want to have a VB macro that pulls data from another workbook by the range name.

    Give filename f and desired range name "custname", I want something like

    Cells(myRow, myCol) = ????

    and the result to be named range "custname" from other open workbook "f"

    IMPORTANT: The range name will refer to only one cell, but I guess I have to limit it somehow, right? To the top left cell of the named range? How do I do that?

    Also, I can't assume the named range will be on any particular worksheet, so I need to reference that range in the WORKBOOK.

    Any help is definitely appreciated!

  • #2
    If the other workbook is named "Book1" and the range is named "MyData" then try:

    Cells(myRow, myCol) = Range("Book1!MyData")
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Thanks for the response!

      I'm apparently partway there now, but I get a Run time error '1004' Method 'Range' of object '_Global' failed. Here's a snip of the code
      Code:
          ThisWorkbook.Activate
          Cells(11, 11) = "Testing"
          Cells(12, 12) = Range("HH_WimpyCap.xls!custname")
      Before it bombs, it does, indeed, place the phrase "Testing" in 11,11.
      custname is defined in the file HH_WimpyCap.xls.

      *Pulls out another handful of hair and moans in desperation*

      Comment


      • #4
        Hi,

        Assuming your HH_WimpyCap.xls is open, then remove the ".xls" from the second line, so that it reads:

        Cells(12, 12) = Range("HH_WimpyCap!custname")
        Best Regards,
        Tom
        ---------------------------
        Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

        Comment


        • #5
          Aha! The file was not open... just testing the methodology and forgot that verrrry important step.

          Thanks for helping me out of the quagmire!

          Okay, not that THAT small problem is fixed, and assuming that I did the following:
          myFile = HH_WimpyCap.xls
          myValue = custname

          What is the best way to go from the hard-coded
          Code:
          Cells(12, 12) = Range("HH_WimpyCap.xls!custname")
          to something that uses the variable names?

          Surely this has to be simple also!

          Comment


          • #6


            YEA! I have it! For anyone that might be wrestling with this in the future, here's my solution:

            Code:
            Function GetWorkbookValue(myFileName As String, myValue As String) As Variant
                Dim Wkb As Workbook
                Dim x As Variant
                Dim myString As String
                If Not IsWorkbookOpen(myFileName) Then
                    Set Wkb = OpenFileInPath(myFileName)
                Else: Set Wkb = Workbooks(myFileName)
                End If
                myString = myFileName & "!" & myValue
                x = Range(myString)
                GetWorkbookValue = x
            End Function
            The IsWorkbookOpen and OpenFileInPath are defined elsewhere in the code--stolen.. er researched from Wallenbach

            Then the statement
            Code:
                Cells(14, 2) = GetWorkbookValue(f, "lienpost")
            works as it should. This may be old hat to most of you, but it was an uphill battle for me. Anyway, thanks to all who helped, and if this snippet helps someone else, then I'm all smiles.

            Comment

            Working...
            X