[AccessD] Setting data into and getting data from Named Range s

Hi John

Note that Areas in Excel is a property of Range.

A range defaults to have one area.
However, if you use Union to set one range from several ranges, such a range will have as many Areas as it was assembled from. Each area will have its own row and column count. If you just refer to row and column of the range it will be the row and column relative to area 1!

This can turn into great fun.

/gustav

>>> jwcolby at ColbyConsulting.com 23-01-2006 16:34:22 >>>
Lambert,

But I am trying to work with NAMED RANGES, not cell references.

Function Excel_NamedRangeDataGet(xlwb As Excel.WorkWorkbook, strRangeName)
As Variant
Function Excel_NamedRangeDataSet(xlwb As Excel.WorkWorkbook, strRangeName,
varData)

As opposed to

Function Excel_CellData(xls As Excel.Worksheet, nRow As Long, nCol As Long)
As Variant 

Everyone is waxing poetic over how cool Named Ranges are, and I am too - for
working IN a spreadsheet/book directly.  OTOH, no one is offering code for
using them from VBA running out in Access.  That is what I am trying to
figure out.  I have (finally) figured out how to get/set a single cell named
range.  I can even write the same (single) value to a "table" named range
(to initialize to blank for example).  Now I need to do the same with
reading / writing "tables" from/to named ranges that refer to cell ranges
(square areas of a spreadsheet).

I have a worksheet that I need to fill in with data.  The worksheet has
single cell things such as Contractor name, system name, RFD Number etc.  It
then has AREAS that represent multi-column tables in Access.  I need to
write a 7 column table, up to 200 records, into a named range for that area
in the spreadsheet.  I then need to read back that "table" from the
spreadsheet when it comes back from the contractor.  There is another little
two column / 20 record table I need to write out, then read back when the
(same) spreadsheet comes back.

It is just frustrating, the total lack of information out there for using
named ranges from VBA.  I have found code galore for CREATING the named
ranges via VBA, but a deafening silence on USING the named ranges.  Why in
the world would I want to create them if I can't read/write to them?

John W. Colby
www.ColbyConsulting.com