By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

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

P: 18
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
Dec 16 '09 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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,delete,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.
Dec 16 '09 #2

P: 18
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?
Dec 17 '09 #3

Delerna
Expert 100+
P: 1,134
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?
Dec 17 '09 #4

ADezii
Expert 5K+
P: 8,638
  1. Automation Code in conjunction with the SpecialCells() Method to determine where to Append the New Data.
  2. Work from within Excel using the CopyFromRecordset() 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.
Dec 17 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.