Excel Iterating Thru Named Ranges 1

[rhinoman]

I am attempting to read all named ranges in Excel SS in all Sheets and return the RangeName and the Value in the range but code only works for the selected sheet.

This code works but only in the active sheet:

Set nms = ActiveWorkbook.Names
For r = 1 To nms.Count
sFieldName = nms(r).Name
MyVal = ActiveSheet.Range(nms(r).Name).Value
Next

The intention is to permit Excel to inteface to Access using the rangename as the field lookup and the value to be inserted into the Access field.

Thanks - it must be staring me right in the face?

Rhinoman
x-)

 

[logius]

Dunno if this'll help, but you could try this:

   Dim NumSheets As Integer
   Set nms = ActiveWorkbook.Names

   NumSheeets = ActiveWorkbook.Sheets.Count

   For i = 1 to NumSheets
       For r = 1 To nms.Count
          sFieldName = nms(r).Name
          MyVal = ActiveWorkbook.Sheets(i) _
                     .Range(nms(r).Name).Value
      Next r
   Next i

I haven't tested all of this, but the new for loop should hop through all the sheets in the workbook.

 

[logius]

Sorry, I put in a typo. The variable shouldn't be "NumSheeets" it should be "NumSheeeeeeeeeeeeeeeeeeeeeeeeets" =)

You know what I mean.

 

[rhinoman]

Hey,

thanks to Logius, I was able to figure it out, simple really. Here is the code:
Set nms = ActiveWorkbook.Names

For r = 1 To nms.Count
Worksheets(Mid(ActiveWorkbook.Names(r).RefersTo, 2, InStr(ActiveWorkbook.Names(r).RefersTo, "!&quot - 2)).Activate
sFieldName = nms(r).Name
MyVal = ActiveSheet.Range(sFieldName).Value
Debug.Print sFieldName; "*"; MyVal
Next r

Thanks all.

Rhinoman