How to append data to an existing file using DoCmd.OutputTo

[Busbait]

Hi,

I have the following code which works fine in MS Access 2007. It export current access form records to an excel sheet:

DoCmd.OutputTo acOutputQuery, "RunQuery", acFormatXLSX, , True

Is it possible to append data from this form to an existing excel sheet instead of creating new one or replacing the existing one?

Your help is highly appreciated

[Delerna]

I don't know if this will help you or not but.

Did you know that you can link a worksheet from an existing excel file
into the tables tab of access.
From there on you can treat the link as though it was a table.
You can uses queries/vba code in access to read,append,del ete,update the data in the spreadsheet through that link. In fact, you can do anything to it that you can do with an ordinary table.

[Busbait]

Thanks Delerna for your reply , but unfortunately the link to a worksheet from an existing Excel file doesn’t help in my case

Is there any way that I can export MS Access records and append at the same time to Excel file?

[Delerna]

But thats what I am saying. A link to a worksheet is one way that you can export and at the same time append MS access records to an excel spreadsheet.

I don't know of any way to do that with DoCmd?

Another way might be to create an excel object in VBA and manipulate it that way.

A link is much simpler though.
using Access
Use a query to delete all data from the linked spreadsheet
Use a query to insert new data to the linked spreadsheet
Use a query to append more data to the linked spreadsheet
Use a query to update data on the spreadsheet
close access

using excel
open the spreadsheet containing the worksheet that is linked to access and there all your exported/appended data is.

I don't know your full situation of course so if you say that doesn't work for you then it doesn't work for you.

Does anyone else know of another way?

[ADezii]

1. Automation Code in conjunction with the SpecialCells() Method to determine where to Append the New Data.
2. Work from within Excel using the CopyFromRecords et() Method which copies the contents of an ADO or DAO Recordset Object onto a Worksheet, beginning at the upper-left corner of the specified Range.
3. If you provide me with your Table/Query Name along with their Field Names and Data types, with some sample Data I'll see what I can come up with in my spare time.