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 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. 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
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |