returning the address of a named range in VBA

**protonLeah** · 09-19-2008, 09:59 PM

How about:

dim x as string
x=ActiveWorkbook.Names("names")

,
or

x = Range("bluenames").Address

**zinny** · 09-20-2008, 02:39 AM

that code works great if i run it on the current worksheet but i'm running it from a different excel file. What i am trying to accomplish is to copy a range of cells from a closed file into the current worksheet. All my variables are correct because i've debug.printed them. its only the line:
first_range = Workbooks("FileNamesList(i)").ActivateRange(TOTAL_SUPPLIER_CHARGE).Address

that gives me a run-time error 9. Subscripts out of range.
TOTAL_SUPPLIER_CHARGE refers to cell C34 and TOTAL_PROFIT refers to cell E34.
these cell values may change if the user inserts a new line therefore the name lists have to be used.

the variable FileNamesList(i) prints the correct file name ie: test1.xls
first_range and second_range are strings

For i = 1 To UBound(FileNamesList) 
        first_range = Workbooks("FileNamesList(i)").ActivateRange(TOTAL_SUPPLIER_CHARGE & ":" & TOTAL_PROFIT).Address
second_range = Workbooks("FileNamesList(i)").ActivateRange(TOTAL_PROFIT).address        
GetValuesFromAClosedWorkbook UserDirectory, FileNamesList(i), "Main", first_range & ":" & second_range, i
        Cells(i + 4, 2).Formula = FileNamesList(i)
Next i

Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As Variant, sName, cellRange As String, counter As Long)
Dim rownumber As Long

    Debug.Print fPath, fName, sName, cellRange, counter
    rownumber = 4 + counter
 
    
        With ActiveSheet.Range("C" & rownumber & ":E" & rownumber)
          .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                     & sName & "'!" & cellRange _
          .Value = .Value
        End With
  
End Sub

**Leith Ross** · 09-20-2008, 02:47 AM

Hello Zinny,

To make your posts easier to read and preserve your formatting, please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
On the Message window Toolbar you will see the # icon. This will automatically wrap the text you selected with the proper HTML tags to create a Code Window in your post. You can do this manually by placing the tag [code] at the start of the line, and the tag [/code] at the end.

As a new member please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...

Forum Rules

Sincerely,
Leith Ross

**protonLeah** · 09-20-2008, 08:05 PM

Originally Posted by zinny

that code works great if i run it on the current worksheet but i'm running it from a different excel file. What i am trying to accomplish is to copy a range of cells from a closed file...

That's significantly different from the OP. Here's some links regarding closed files/workbooks:

Originally Posted by davesexcel

http://www.exceltip.com/exceltips.ph...tips&tipID=410

http://www.j-walk.com/ss/excel/tips/tip82.htm

here's a few sites on pulling values from closed workbooks that could give you some ideas

http://www.erlandsendata.no/english/...dacimportwbado

**zinny** · 09-21-2008, 12:33 AM

thanks. i'll have a look into those examples.
i guess more specifically my program works if cellRange is a string "c34:e34"
however i need to refer to a named lists "SUPPLIER_TOTAL_CHARGE" and "TOTAL_PROFIT" which is in the closed worksheet and refers to cell c34 and E34 respectively.
Is there any way to set the value of cellRange so that it refers to a named list instead of entering the range manually?
something like cellRange = Range("TOTAL_SUPPLIER_CHARGE").Address & ":" & Range("TOTAL_PROFIT").Address

Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As Variant, sName, cellRange As String, counter As Long)
Dim rownumber As Long

    Debug.Print fPath, fName, sName, cellRange, counter
    rownumber = 4 + counter
 
    
        With ActiveSheet.Range("C" & rownumber & ":E" & rownumber)
          .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                     & sName & "'!" & cellRange _
          .Value = .Value
        End With
  
End Sub