Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Reference a Range by Name in Other Workbook

  1. #1
    Join Date
    12th October 2004
    Posts
    36

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  3. #3
    Join Date
    12th October 2004
    Posts
    36
    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
    VB:
    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*

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  5. #5
    Join Date
    12th October 2004
    Posts
    36
    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
    VB:
    Cells(12, 12) = Range("HH_WimpyCap.xls!custname") 
    
    
    to something that uses the variable names?

    Surely this has to be simple also!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th October 2004
    Posts
    36
    YEA! I have it! For anyone that might be wrestling with this in the future, here's my solution:

    VB:
    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
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy Range To New Workbook & Close Existing Workbook
    By Spitfire999 in forum EXCEL HELP
    Replies: 16
    Last Post: July 2nd, 2008, 06:17
  2. Named Range Reference To Another Workbook
    By Unreal in forum EXCEL HELP
    Replies: 1
    Last Post: October 16th, 2007, 12:42
  3. Reference A Workbook With Vba
    By w424637 in forum EXCEL HELP
    Replies: 1
    Last Post: April 11th, 2007, 18:30
  4. Replies: 4
    Last Post: October 5th, 2006, 06:52
  5. Reference A Range / Inactive Workbook
    By bradley_Austin in forum EXCEL HELP
    Replies: 2
    Last Post: February 10th, 2005, 08:13

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno