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

John,
The most effective way I've found to load data from Excel spreadsheets is to
create a hidden sheet linked to the data you wish to read, in essence
creating a flatfile. Then you can "load" the flatfile into Access with
something like this:

Set rstbase = dbs.OpenRecordset("tblOutPut_Data", dbOpenTable)
On Error Resume Next
.Range("A2").Select 'select first cell of first record to read
Dim x As Integer

Do While Not IsEmpty(ActiveCell)
    rstbase.AddNew 'create records in output table
    For x = 0 To 16
    If x < 4 Then rstbase.Fields(x) = .ActiveCell.Offset(0, x)
        'change sign on Jan-Dec revenue
        If x > 3 Then
        If rstbase.Fields("rptline") = 4100 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 4130 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 15000 Then
           rstbase.Fields(x) = Round(.ActiveCell.Offset(0, x), 3)
        Else
             rstbase.Fields(x) = Round(.ActiveCell.Offset(0, x), 3)
        End If
    End If
        
    Next x
    If Not rstbase.Fields("rptline") = 0 Then rstbase.Update
    
'This error occurs if duplicate Business Unit are loaded into the table.
'Business Unit is a primary key so duplicates are not allowed
        If Err.Number = 3022 Then
            MsgBox "Error #: " & Err.Number & " This record is already in
the Database!", 16, "Error"
            Err.Clear   ' Clear Err object fields
        End If
            
    .ActiveCell.Offset(1, 0).Select 'find first cell of next record
Loop

If your data is a table that is already in record format (ie with the fields
you want to populate in the Access table) you can make the upper left corner
of the range the active cell then use  offset to load the data from the
proper cell  as in the ex above. HTH

Jim Hale

-----Original Message-----
From: John Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Monday, January 23, 2006 2:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Setting data into and getting data from Named
Range s


Jim,

I did, and thanks for the offer.  I think that understanding that it is the
range object that actually does the work kind of removed my mental
roadblocks.  I was thinking that the name object should have abilities that
it doesn't have.  I sent an email re "success" to the group.  

Again, thanks for the offer.  If I run into any more roadblocks I will keep
you in mind.  Kind of the last piece for right now is reading data back out
of a named range.  I have code for doing that, though it is kind of rough
ATM.

This is for a client who is documenting construction of a hospital.
Apparently federal law requires that they collect documents for each and
every one of certain kinds of systems, paper and electronic, and store them.
My client did not understand the size of the job when he got involved, nor
when he got me involved.  

I am of course building a database to track all of the documents, request
them (electronically) from each contractor (via this Request For Document
spreadsheet, attached to emails) for each document, then as the electronic
documents are returned via attachments to the email I send, strip the
attachments off and file them in an intricate directory structure out on the
hard disk.  There will be literally thousands of documents, perhaps tens of
thousands.  6 contractors, 91 systems (though some are "copies", i.e. 7 "Air
handling units"), and ~150 documents, although all docs are not required for
all systems.  My db has to track what has been requested, store "proof" that
we did request them (each Excel RFD is saved in the same directory
structure), track which docs are received back, report which docs are "owed"
by which contractors for which systems, and report which system's documents
have been completely received.

We are trying to convince all the contractors to work with us in naming the
documents to a standard so that as the attachments come in I can parse the
doc name to see what system/document it is and "count it as received".
There are just so many documents that to do this stuff manually would invite
chaos.


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.