Appending Access records to end of an Excel file

[Jules48]

I store comprehensive details of customers' "transactio ns" 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

[pietlinden]

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.

[Jules48]

pi********@hotm ail.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

[Hank]

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

[Jules48]

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.