[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