[AccessD] Setting data into and getting data from Named Ranges

Hi John

I prefer to setup queries outside Excel to pull data from. If so it is very easy with DAO to pull data into a range:

  Set dbs = DBEngine(0).OpenDatabase(<databasefilename>, , True)
  Set qdf = dbs.QueryDefs(<queryname>)
  qdf.Parameters(0) = lngID
  Set rst = qdf.OpenRecordset
  lngRows = rng.CopyFromRecordset(rst)
  rst.Close
  qdf.Close

To copy data values from one range to another:

  rngDestination = rngSource.Values

To set value of a single cell range:

  rng.Value = varValue

To set the value of a single cell in a multicell range (row and column is relative to the range):

  rng.Cells(lngRows, lngColumns).Value = varValue

You may go beyond that. Study Range.Offset.

Reading is along the same lines:

  varValue = rng.Cells(lngRows, lngColumns).Value

/gustav

>>> jwcolby at ColbyConsulting.com 23-01-2006 14:12:09 >>>
I am moving right along with my Excel stuff but now I need to watch as data
is poked into named ranges.  But first I have to discover how to poke stuff
into named ranges.  I am finding a lot of code for setting up the named
ranges but not a lot about actually setting a single value into the (single
cell) range, setting a table of data (from Access) into a named range
(multiple cells), and then of course, reading a value from a single cell
named range, or getting data back out of a named range (multiple cells) into
a table.

Does anyone have example code for this?

John W. Colby
www.ColbyConsulting.com