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

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 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Monday, January 23, 2006 9:41 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Setting data into and getting data from Named Range s

John,

Here's some more examples. Feel free to optimize any way you wish..

Setting the value of a single cell. This is pretty inefficient as the
workbook is opened, a cell set and the workbook closed. The code could
easily be modified to just set the cell in an already opened workbook, by
passing in the Workbook object...

Sub Excel_SetCellValue(sFileName As String, nWkSheetNumber As Integer,
strCellAddress As String, varData As Variant, Optional strFormat As String =
"")
Dim xlFile As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim oRng As Excel.Range

        Set xlFile = Excel_OpenWorkBookHidden(sFileName)
        With xlFile
            Set xlwb = .Workbooks(GetFileName(sFileName))
            Set xlSheet = xlwb.Worksheets(nWkSheetNumber)
            .Range(strCellAddress) = varData
            Set oRng = .Range(strCellAddress)
            If strFormat & "" > "" Then oRng.NumberFormat = strFormat
            Set oRng = Nothing
            Set xlSheet = Nothing
            Set xlwb = Nothing
        End With
        Excel_CloseWorkBook xlFile, True

End Sub

Retrieving the value of a single cell. A touch more efficient the workbook
being already opened....

Function Excel_CellData(xls As Excel.Worksheet, nRow As Long, nCol As Long)
As Variant
    With xls
        Excel_CellData = Trim(Nz(.Cells(nRow, nCol), ""))
    End With
End Function


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Monday, January 23, 2006 8:12 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Setting data into and getting data from Named Ranges


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 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com