[AccessD] Writing to an excel file

I think the simplest way for you to handle this is to define a Named Range
inside the Excel file which includes the column names and the rows to which
you will want to write data.

That done you can then go through the menu choices File/Get External
Date/Link Tables and select the Excel file as your source. Then in the link
table dialog check the 'Show Named Ranged' option and choose the range you
defined. On the next screen hit the 'First Row Contains Column Headings'
check box, and you are done. The range now shows up as a linked table and
you can write to it using VBA. 

It seems you cannot just use append queries as that would require adding
rows to the worksheet. So you get an error stating that the 'Spreadsheet is
full'.

However VBA works just fine. Here is some code I just threw together that
does the trick...

Sub WriteExcel()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsout As DAO.Recordset
Dim n As Long
    Set db = CurrentDb
    Set rs = db.OpenRecordset("testQry", dbOpenDynaset)
	' testQry is just a simple select query that returns the correct
	' number of columns for this test. i.e. the name number as the named
range has.
    Set rsout = db.OpenRecordset("Excel_Sheet_Area", dbOpenDynaset)
	' Excel_Sheet_Area is a linked 'table' that uses a named range in
the spreadsheet
	' to ensure the data is written to the correct starting row. 
	' The range covers X columns and all the rows from the start row to
the end
	' of the worksheet.

    rsout.MoveFirst
    rs.MoveFirst
    With rs
        While Not .EOF
            rsout.Edit
		' I don't care about the field names as the query has the
right number
		' of columns. So I just write out the data accessing it via
the field number.
            For n = 0 To rsout.Fields.Count - 1
                rsout.Fields(n) = .Fields(n)
            Next n
            rsout.Update
            rsout.MoveNext
            .MoveNext
        Wend
    End With
    rsout.Close
    rs.Close
    Set rs = Nothing
    Set rsout = Nothing
    Set db = Nothing
End Sub

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie Johnson
Sent: Tuesday, August 02, 2005 8:35 AM
To: AccessD solving'
Subject: [AccessD] Writing to an excel file


I have a recordset that I would like to write as rows in an excel
spreadsheet. 
 
I want to start on a certain line. 
 
The columns are already predefined and match my recordset. 
 
This will run each month so it will need to overwrite the prior month's
data.
 
What is the best way to go about this?



May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us





 





		
---------------------------------
 Start your day with Yahoo! - make it your home page 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com