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

I have it WORKING!!!

Yea!!!

What a learning experience this has been.

In order to understand the code snippet you have to know that I open a
workbook and set a class global Workbook and Worksheet object called
respectively mXLWB and mXLWS.  Thus this function simply passes in a name
range and a recordset typcast as an object.  It then translates that named
range into a range object, which then calls the CopyFromRecordset method
passing in the RS object.  The CopyFromRecordset is perfectly happy getting
an object, and apparently can handle either a DAO or an ADO recordset passed
in.  I am currently passing in a DAO recordset but will switch to ADO.

What I have not accomplished is CopyTORecordset (read the data back out of
the range INTO a recordset object).  I think I can accomplish that back in
Access however without calling the Excel objects.

Function mXLWBNameDataCopyFromRS(strName As String, RS As Object)
On Error GoTo Err_mXLWBNameDataCopyFromRS
Dim lRange As Range
    Set lRange = mXLWS.Range(strName)
    lRange.CopyFromRecordset RS
Exit_mXLWBNameDataCopyFromRS:
On Error Resume Next
    If Not (lRange Is Nothing) Then lRange.Close: Set lRange = Nothing
Exit Function
Err_mXLWBNameDataCopyFromRS:
        MsgBox Err.Description, , "Error in Function
clsExcel.mXLWBNameDataCopyFromRS"
        Resume Exit_mXLWBNameDataCopyFromRS
    Resume 0    '.FOR TROUBLESHOOTING
End Function 

I now have an entire (largish) Excel class which handles opening / saving /
closing the Excel Application, workbooks, setting references to worksheets,
passing back pointers to OPEN workbooks and worksheets, getting and setting
data from named ranges, and copying data from a recordset into a named
range.  Also little things like setting visibility, running macros and so
forth.

My application now opens Excel, opens a "template" workbook, immediately
saves it back to a different location, updates a handful of single cell
ranges, and two multi-cell ranges, then closes the spreadsheet, attaches it
to an email, and sends it off to a recipient.

Sometime when I get a few minutes I will create a demo to put up on my web
site.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, January 23, 2006 12:58 PM
To: accessd at databaseadvisors.com
Subject: Re: [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