hello,
I have an excel spreadsheet with data that I have to append to an access table. This is going to happen every month so I would like to automate as much as possible. Could somebody recommend the best way to accomplish this?
There are many approaches possible. Do you want to control the process from Access or from Excel? Do you create the spreadsheet yourself or do you get it from somewhere else? Do you have control over the format? What does the data look like - how many fields are you talking about?
Its a simple excel sheet which I create. It has 2 columns, not exactly sure how many rows (I would guess 1500 at most).
It has no formatting - just a straight data dump.
Not too much data - just a pain to have to do manually.
Have you considered what your database design should be? Is one of your 2 columns a unique identifier, or do you have another plan for a primary key in your table? Is there other information you should be storing in addition to the 2 columns?
If you don't consider things like that before you start collecting data you might have problems retrieving what you want later.
Is it possible to do this from within the excel code?
I am using an excel vba macro to create the spreadsheet with the data to be added to the access table. Is there a way to add more code to this excel sheet to open the access table and automatically append the data?
I am planning on using an autonumber field as the primary key in the table. It is a list of cellular phone numbers with the charge for each number. Each month, I will append the new months charges for each number. I will be using this to show trending for each number.
In vba help, look at 'openrecordset' and the 'edit method example' for details of how to use DAO to manipulate Access tables.
(I know ADO is newer, but I started with DAO and it still works)
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.