Create a row in excel from an access record

[mkatz13]

I need to fill the next row in an excel workbook after a certain event occurs in access using the data from the access record on the corresponding page of the excel workbook.

What I am doing is that the excel is an account receivable ledger and an entry needs to be created when certain events occur on the access form.

I would assume the use of DDE, but I'm not quite sure how to proceed and if that is correct.

I currently do this update by hand, but automation would be great.


Thanks

Mike Katz

 

[GHolden]

Hi,

this may point you in the right direction...

This code opens a recordset and writes to the end of an Excel workbook.
You will probably have to mess about a bit, but hopefully you get the general idea.

Dim db As Database
Dim rstDate As DAO.Recordset
Dim ExcelApp As New Excel.Application
Dim intColIndex As Integer
Dim intRowIndex As Integer
Dim intCounter As Integer

Set db = CurrentDb
Set rstDate = db.OpenRecordset("SELECT * FROM tblDate", dbOpenDynaset)

With ExcelApp
'Open Excel File
.Workbooks.Open "c:\test\test.xls"
'Don't show Excel
.Visible = False

'Find first blank row
'by looping until first empty
'cell in column A
intRowIndex = 1
Do While LenB(.Cells(intRowIndex, "A&quot) > 0
intRowIndex = intRowIndex + 1
Loop

rstDate.MoveFirst
'Loop through recordset
'adding DateID to column A
'and fldDate to column B
Do While Not rstDate.EOF
.Cells(intRowIndex, "A&quot = rstDate!DateID
.Cells(intRowIndex, "B&quot = rstDate!fldDate
rstDate.MoveNext
intRowIndex = intRowIndex + 1
Loop
'Close and save workbook
.ActiveWorkbook.Close True
'Quit excel
.Quit
Set ExcelApp = Nothing

End With

Any questions, etc, just let me know...



There are two ways to write error-free programs; only the third one works.