473,320 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Appending Access records to end of an Excel file

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
4 6018
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
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
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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jsudo | last post by:
I have a Access database with a large amount of records (close to 500,000) that I would like to export to Excel. I found out that Excel has the capability of of about 65,000 rows so I know I...
9
by: Pete | last post by:
Does anyone have a simple html vbscript or other type of snippet they can share that appends a record to a access database via ADO or DAO? I would like to allow users that don't have Microsoft...
3
by: kylei | last post by:
When I try to append one table to another with the exact same field names I get an error... Microsoft Access was unable to append all the data to the table. The contents of fields in 0...
3
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown....
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
4
by: BFoxDDS | last post by:
i'm sure this is simple to do, but i can't find the answer in the search engines. i have an access db with 400,000 records. i produce queries of 5,000 records at a time exported into an excel...
5
by: Hokiecow | last post by:
I'm trying to import specific columns from an excel file (Requirements.xls) into an access table (tblRequirements). Using VBA, I'm able to import the entire excel file into table...
8
by: Irene | last post by:
Hi, I have an MS Access Database with 1 Table containing about 2 million records in Unicode (diferent languages). I would like to export the Table to a Text file (CSV, Tab, etc.) Access...
3
by: khoward | last post by:
Hi, I have an Access 2007 database that contains customer contact information. There are over 8,000 that include name, organization (as a look-up column), email, phone, address, and events that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.