Re: Writing Excel cells within Access

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/14/05 

Date: Mon, 14 Mar 2005 12:03:21 -0500

Some sample code that may get you started (you can add loops to this code to
loop through each work*** in a book if you'd like). This code opens an
EXCEL workbook and reads data from it and writes the data into a recordset.
You can modify this as needed to do things directly on the spread***, etc.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
    rst.AddNew
        For lngColumn = 0 To rst.Fields.Count - 1
            rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
        Next lngColumn
    rst.Update
    Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing 

-- 
        Ken Snell
<MS ACCESS MVP>
"Bob Bonta" <bob.bonta@jte.osd.mil> wrote in message 
news:85f301c528a3$6e5176a0$a401280a@phx.gbl...
> Thank you Chris.  What you suggested is exactly my
> intention (perhaps I wasn't clear enough in my original
> post.).
>
> Do you have a sample of how to write to Excel cells from
> within Access?
>
> Bob
>>-----Original Message-----
>>
>>"Bob Bonta" <bob.bonta@jte.osd.mil> wrote in message
>>news:859d01c5289d$7430aa10$a401280a@phx.gbl...
>>> Hey folks - I'm looking for a sample code snippet on
>>> writing to an Excel spread*** one cell at a time from
> a
>>> query built within MS Access.
>>>
>>> Any assistance is greatly appreciated.
>>>
>>> Thanx!
>>>
>>> Bob
>>
>>Bob,
>>
>>A Query (QueryDef) in MS Access cannot send information
> to MS Excel.
>>
>>You will need to write VBA code, instantiate a recordset
> (you can name
>>an existing QueryDef when you do this), and then loop
> through the
>>recordset, writing out to the MS Excel "cells" based on
> whatever
>>conditions are appropriate.
>>
>>
>>Sincerely,
>>
>>Chris O.
>>
>>
>>.
>>