Copying Data from Excel to Access

[MWE]

there must be a simple way to do this ...

I wish to copy approx 50 lines from an Excel Worksheet into an Access Table. I could import the data into a new table, merge the tables, etc., but there must be a simple way to copy and paste. A single line works, but not multiple lines.

Thanks

[XLGibbs]

The easiest way is to Import data using the wizard.

You cannot simply copy the sheet into a table using the clipboard from Excel to Access (you can do it from Access to Excel however)

[XLGibbs]

By the way, when importing using the wizard you can specify to import them into an existing table, thereby appending new data...provided structures are the same..should be easy.

[MWE]

By the way, when importing using the wizard you can specify to import them into an existing table, thereby appending new data...provided structures are the same..should be easy.thanks for the prompt reply. Using the import wizard to import excel data into an existing table has NEVER worked for me. I have even tried to import a worksheet created by exporting data from Access so I know that the fields are exactly the saeme -- does not work. And one of the most irritating aspects is that Access error messages are generally useless.

I continue to wonder how MS can market products that are so incompatible.

[XLGibbs]

Yes Access's error messages are useless.

Like when it hits 2 GB and you try a make table. "Invalid Argument" makes sense to me because the invalid argument is the "INTO [TABLE]" part of the make table.

But you would think it would just say "Dude, your database is too frickin big"


If I had to do this regularly, I would set up an excel sheet as a template where I would house the new data to come into access.

I would set up a link to THAT template, and structure and Append Query in Access which reads from it and appends it properly to the table.

I would then tie the processes involved in addition to the append (SetWarnings OFF maybe) and things to the macro design tool in Access.

Then I would probably have Excel call Access at some point an execute the macro.

I dislike Access, so I rarely use it for anything other than helping people here with it.

[stanl]

If your Excel range is named, or has a header row, you can use the Jet 4.0 Provider and SELECT INTO.. If you have Excel 2003 you can persist any given range in Excel as an XML Recordset then insert the rows into Access. There is a discussion of this I had with XL-Dennis on another forum. Stan

http://www.ozgrid.com/forum/showthread.php?t=27756

[stanl]

I dislike Access, so I rarely use it for anything other than helping people here with it.

OT: perhaps not for the same reason as you, I tend to agree. I see it primarily as a middleware storage container [viz. persisting binary streams] to be manipulated via MDAC and never directly with the Access.Application Object... .02 Stan

[OBP]

I have just copied 27 and then 56 lines of an Excel Worksheet and "PasteAppended" it in to an existing Access Table without any problems at all.

[XLGibbs]

OT: perhaps not for the same reason as you, I tend to agree. I see it primarily as a middleware storage container [viz. persisting binary streams] to be manipulated via MDAC and never directly with the Access.Application Object... .02 Stan


Well, I can see it's use as that, for sure. But SQL Server spoils me rotten compared to Access.

[stanl]

I have just copied 27 and then 56 lines of an Excel Worksheet and "PasteAppended" it in to an existing Access Table without any problems at all.

My Bad! I always look at the forest, not the trees. Does this mean solved? Stan

[Ken Puls]

Hey guys,

If not too late, you may want to review an article on my site: Export A Range Of Excel Data To A Database (http://www.excelguru.ca/node/18)

Personally, I use ADO to connect to the DB, then send/retrieve by SQL. Works like a dream, truly. One of the things I like about this approach is that it uses transaction processing, so you don't get whacked if part of your transactions don't work.

HTH,