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

Appending Access records to end of an Excel file

P: n/a
I store comprehensive details of customers' "transactions" in Access
(2000). At the moment, I (or my staff) duplicate entry of the
information in an Excel spreadsheet which we use to extract stats and
charts and to number crunch etc.
Obviously, duplicating the data entry is onerous and open to error.
What I'd like to do is enter the data into the Access database then use
a query to compile a recordset of the latest set of records and then
append them to the end of the Excel file using VB.
I've found VBA code in various forums to create a new Excel workbook
and populate it with records from Access, but nothing on appending new
records to the bottom of an existing spreadsheet.
Any pointers would be greatly appreciated. If required, I could post
cut-down versions of the database and spreadsheet but it would need
some work because of customer (actually, patient) confidentiality.

Thanks in advance.

Jules

Jul 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
if you link to the table, it exports just the way you want. Just link
to the Excel table, and create an append query to add the new data.

Jul 16 '06 #2

P: n/a
pi********@hotmail.com wrote:
if you link to the table, it exports just the way you want. Just link
to the Excel table, and create an append query to add the new data.
Thanks for that - I'll work on it.

The Access data is stored in a number of related tables. So, first
link to the Excel spreadsheet, then use an append query to pull
together from the separate tables the fields to be appended to the
spreadsheet, run the append query and it should add the records to the
linked spreadsheet - just thinking "out loud" - I'll go and "have a
play" with it.

Thanks again!

Jules

Jul 16 '06 #3

P: n/a
Jules,

Link to your spreadsheet in the same manner you link to an
Access Table. Just pick the File Type of Excel and browse to your
Excel file. One nice thing is that you can select any one of the
Worksheets in your Excel file to link to. I often have two links to
two different Worksheet pages.

Then you can treat it just like a table and do an INSERT to
append your data. I've never do anything but append to the bottom of
the Worksheet. There may be a way to be cell specific.

Hank Reed

Jul 16 '06 #4

P: n/a

Hank wrote:
Jules,

Link to your spreadsheet in the same manner you link to an
Access Table. Just pick the File Type of Excel and browse to your
Excel file. One nice thing is that you can select any one of the
Worksheets in your Excel file to link to. I often have two links to
two different Worksheet pages.

Then you can treat it just like a table and do an INSERT to
append your data. I've never do anything but append to the bottom of
the Worksheet. There may be a way to be cell specific.

Hank Reed
Thanks Hank, Gonna give that a go as soon as I can (after doing a
backup of my DB, of course!)

Thanks a lot Guys.

Jul 16 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.