Appending data to Existing Excel Data

[net]

Hello All,

I am exporting data from Access (2000) to Excel using a criteria form. I would like to append data to the Excel sheet without clearing the current data. How can I perform this function?

Here is part of my code:

<BEGIN CODE>
Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\WO_Activity_Report.xls")
myWB.Sheets ("WO_Activity_Report")
myWB.Save
myXL.Quit
Set myXL = Nothing
<END CODE>

Your assistance is greatly appreciated.

 

[DenMiller57]

Here is some code I got working to send data to the first worksheet in a workbook. The code does not work on the second worksheet and I dont know why.

Dim DB As Database
Dim rst As Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWsMel As Object
Dim xlWsERD As Object

Set DB = CurrentDb ' set reference to current database
' open a table as a recordset
Set rst = DB.OpenRecordset("tmp_ExportExpenses")

' Create the Excel Application object where the data will be sent
Set xlApp = CreateObject("Excel.Application")

Set xlWb = xlApp.Workbooks.Open(ExcelExpensePath)
Set xlWsMel = xlWb.WorkSheets(1)
' Set xlWsMel = xlWb.WorkSheets("Mel") This also works since the first worksheet name is Mel
Set xlWsERD = xlWb.WorkSheets(2)

' The next 3 lines work because they post to the first worksheet
xlWsMel.Cells(1, 3).Value = rst!Fullname
xlWsMel.Cells(1, 7).Value = rst!EABN
xlWsMel.Cells(1, 9).Value = ProcessDate

' These have also been tested and work correctly to the first worksheet
xlWsMel.Cells(1, "C").Value = rst!Fullname
xlWsMel.Cells(1, "G").Value = rst!EABN
xlWsMel.Cells(1, "I").Value = ProcessDate

' The next 3 lines do not work, because they post to the second ws
' in the workbook. If you figure out why, let me know. BTW, I tried changing the order and data with no success.
' It might be due to a limitation of DAO, I don't know.
xlWsERD.Cells(4, "K") = rst!Fund
xlWsERD.Cells(4, "O").Value = rst!AgreementNum
xlWsERD.Cells(6, "N").Value = "Training Division"
xlWb.Save
xlWb.Close

' Close objects
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
' Release Excel references
Set xlWsMel = Nothing
Set xlWsERD = Nothing
Set xlWsRCPT = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

 

[net]

Hey, thanks DenMiller!

I apprecaite your time and assistance.