Need help copying columns from Excel & pasting into Access table

Sonny Jim

Registered User.
Local time
Today, 12:59
Joined
Jan 24, 2007
Messages
98
I haven't written much vba code but I need to create an event that will select data from various defined ranges from an Excel spreadsheet and paste append that data into an Access table. Can anyone help me with this?
 
if you want to manually cut and paste this stuff, you have to make sure that the entire cell is selected in access (no cursor showing in the cell) before being able to paste. and i'm not even sure you can paste in the middle of a table in access, but you certainly can do it from the top of the table by selecting the column.

writing code for this will be difficult because it is long and complicated. unless you get it from somewhere else, I would suggest using the intellisense dropdown in excel to learn what library objects you are going to need first.
 
What about the docmd.transferspreadsheet method to import the excel data into an access table?
 
Thank you very much for your ideas and the quick replies, my sincere apologies for my delayed reply! I was thinking along the same lines regarding the use of the docmd.transferspreadsheet method.I like the way Access handles duplicates when paste appending tables. How can I include this in my code?

Specifically, Once duplicate values are recognized in any field that should hold unique values I would like to initiate an event that opens a message box that says something like,"Records that Microsoft Office Access was unable to paste have been inserted into a new table called 'Paste Errors.' and have that action performed.
 
Normally you should not care about duplicate records. I import into a temp table, then I run an append query to add the new records then I run an update query to update the data for any of the existing records with the new data [if applicable]. I would suggest that you run a duplicate records query and store the duplicate records into a table that the user can view from a form. Users should not have direct [design] access to the db objects. Turning the warnings off makes it a seamless process.
 

Users who are viewing this thread

Back
Top Bottom