Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RangeName Value From a Closed File (XL97; SR2)

    I'm interested in obtaining the row of a range name in a closed file. I have replicated <A target="_blank" HREF=http://j-walk.com/ss/excel/tips/tip82.htm>John Walkenbacks' code</A> to get a value from a closed file but need to tweak it for the range name. This is where I'm having a problem and worked myself into a corner. Any advice you have would be appreciated.

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RangeName Value From a Closed File (XL97; SR2)

    Without testing (which is probably a mistake), I think that you just want to change John's test routine to something like this:
    <pre>Sub TestGetValue2()
    p = "c:XLFilesBudget"
    f = "99Budget.xls"
    s = "Sheet1"
    Application.ScreenUpdating = False
    r = 10
    With Range("myNamedRange")
    For c = 1 To .Columns
    a = .Cells(r, c).Address
    .Cells(r, c) = GetValue(p, f, s, a)
    Next c
    End With
    Application.ScreenUpdating = True
    End Sub</pre>

    This assumes that you want all of row 10 of your range. Notice I used a "With" block and changed "Cells" to ".Cells" so that the row & column in the Cells method are relative to your range. Hope this helps (and works)! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    Sammy,

    You were right. The code should have been tested. Welcome to my painted corner. My challange is with the RangeName; I can not obtain the row of the rangename from the closed file.

    John

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    I have a comment to make (or is it a question)... This post reminds me of a question I've had for a while. Can I assume that any code that looks in a closed workbook will only work if that workbook (the one being peeked at) is NOT protected? I certainly hope that's the case otherwise why bother protecting workbooks, yikes!!

    Deb <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    Deb,

    I have tested with/without protecting the sheet of the source XL file that is closed and have had successful data extractions both time.

    John

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    John... this is not what I want to hear <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/spook.gif border=0 alt=spook width=15 height=15>. You said that you tried it w/o protecting worksheets but what about the workbook itself? If worksheets and workbook are protected, can your code extract the cell contents? If true, then this totally wipes out protection doesn't it? I mean why bother protecting anything when 10 lines of code can suck it all out for all to see.

    Deb <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    If you want the value in a worksheet then I don't think you need John Walkenbach's function. Try the following formula :<pre>=ROW('C:FilesWorkbook.xls'!RangeName), </pre>

    where C:FilesWorkbook.xls is the path and filename of the closed file and RangeName is the name of the range you are interested in.

    Or you could use the function in a given cell in the closed file , =ROW(NamedRange), and use John Walkenbach's function and first code example to get the value of that cell.

    Andrew C

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    Actually the following adaption of John Walkenbach's function should work :<pre>Private Function GetRow(path, file, range)
    Dim arg As String
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
    End If
    arg = "ROW('" & path & "" & file & "'!" & range & ")"
    GetRow = ExecuteExcel4Macro(arg)
    End Function</pre>

    and to test it use :<pre>Sub TestGetRow()
    p = "C:Files"
    f = "Filename.xls"
    r = "RangeName"
    MsgBox GetRow(p, f, r)
    End Sub</pre>

    Andrew C

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RangeName Value From a Closed File (XL97; SR2)

    Deb,

    You are correct in stating that all data can be extracted. However I plan on modifying the code to extract data from various range names from within the closed file; giving the end-user data that I want them to see (nothing more).

    I have not tried to extract on a protected worksheet.

    John

Posting Permissions

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