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.

 

[Uncle Gizmo]

Three possibilities immediately spring to mind

1) Detect the final position of the data in XL and use that information to calculate the start position of the next import. This option will require both knowledge of excel automation and MS Access VBA coding.

2) Append the data to an "export table" stored within MS Access, and then export this table as a whole directly into XL overwriting the previous data.

3) When you export the data From MS Access, store the start location and end location of the data block within an MS Access table and then use this information to calculate the position of the next block of data you export.

I cannot help you with option one, although that one would probably increase my knowledge the most and is the one I should do!

Option two would probably be the easiest, however if would overwrite data in your XL table.

I prefer option three, it seems the tidyest and would be relatively easy to do.

Someone may have a better solution.