Excel named range lookup

[gordon smith]

I'm having trouble accessing named ranges in Excel. I can create and
use named ranges during a run of a ruby script, but I cannot use named
ranges from a preexisting workbook.

WIN32OLE::VERSION = Win32OLE: 0.5.9
RUBY_VERSION = 1.8.2
RUBY_RELEASE_DATE = 2004-12-25
Microsoft Excel 2002 in Windows 2000 Pro

I can add and access a named range if both are done from Ruby in the
same script:

# Add a named range.
worksheet.names.Add( { 'Name' => 'myRange', 'RefersTo' =>
worksheet.Range( 'A2' ) } )

# Assign to a named range.
worksheet.range( worksheet.names( 'myRange' ).Value ).value = 2

However, I cannot access named ranges that already exist in a
worksheet.

puts "names.count=#{worksheet.names.Count}"
worksheet.range( worksheet.names( 'foo' ).Value ).value = "bar"

results in:

names.count=0
bluesheet-gui.rb:181:in `method_missing': names (WIN32OLERuntimeError)
OLE error code:800A03EC in <Unknown>
<No Description>
HRESULT error code:0x80020009
Exception occurred. from bluesheet-gui.rb:181:in
`createBluesheet'
from bluesheet-gui.rb:456
from bluesheet-gui.rb:454:in `call'
from bluesheet-gui.rb:61:in `runDuring'
from bluesheet-gui.rb:454

This is a worksheet with ~100 named ranges

Has anyone accessed named ranges in an existing worksheet?

[dave...@gmail.com]

> Has anyone accessed named ranges in
> an existing worksheet?

It works for me, using Excel 2000:
irb(main):014:0> excel.workbooks.open "MENSA_test.xls"
irb(main):019:0> excel.worksheets(1).names.count
=> 2
irb(main):024:0> excel.worksheets(1).names.each {|name| puts name.name
}
'MENSA QUESTIONAIRE'!_FilterDatabase
'MENSA QUESTIONAIRE'!Print_Area
=> nil
irb(main):026:0> excel.worksheets(1).names("Print_Area").value
=> "='MENSA QUESTIONAIRE'!$A$1:$BW$43"
irb(main):027:0> excel.worksheets(1).names("_FilterDatabase").value
=> "='MENSA QUESTIONAIRE'!$B$6:$E$40"

I'm trying to think of things to do that don't normally work today;
bugs seem to be hiding from me this morning.

Cheers,
Dave

[gordon smith]

Clarification: has anyone accessed a USER DEFINED named range in an
existing worksheet?

I can also access "Print Area", when I'm looking at the first
worksheet...

Thanks

[graham]

gordon smith wrote:
> Clarification: has anyone accessed a USER DEFINED named range in an
> existing worksheet?

from my Excel (not Ruby) experience - ISTR that named ranges can occur
at either a worksheet or a workbook level. Worksheet level names are
those (unsuprisingly) which include a worksheet name in the range
definition. This might be part of your problem
Graham

[gordon smith]

The gentleman is correct!

Also check workbook.Names for named ranges. Something like the
following works:

rangeString = workbook.names( 'Sheet1!myRange' ).Value
# Remove "=" prefix (e.g. "=Sheet1!$A$2:$A$4")
rangeString = rangeString.slice( 1, rangeString.length - 1 ) if (
rangeString =~ /^=/ )
worksheet.range( rangeString ).value = 'testing...'

BEWARE that worksheet matches the range sheet. Better would be to regex
the sheet name from the range value and do the lookup...