VBA to transfer Excel data to an Access table

Hi there,

I input data into an excel spreadsheets every day and would like to automate a way to transfer the data to Access from Excel. (Since I'm working with different excel spreadsheets I'm wanting to avoid importing from Access)
I have a worksheet that gathers the information into the same format as the Access database, but am lost on how to write a code to get it to export to Access. I've been researching this for awhile and feel like I may be missing something. I was trying to use this code http://www.exceltip.com/st/Export_da...Excel/425.html
but was having trouble just connecting to the database.
If it makes it easier, the data I need to transfer is always in the same cells.

Thanks in advance for help!

The excel document is several worksheets that track the progress of a Lot# at a factory. There can be up to 4-5 different documents per day, but they are all going to the same table. (I wish we could do the whole process electronically, but that's a no go) Would linking 4-5 documents a day be feasible? And could that be turned into an automated process too?

I wanted to do it from the excel side because I wanted to enter the data into my excel spreadsheet template, send the formatted data to access via a VBA and then save the excel workbook as a different file. (Each workbook is named "Lot #, Production Traceability" so there will be hundreds of these soon.)

I'll be appending the data to an existing Table, which I can then use to create reports in conjunction with other tables in the same database. The data from each excel workbook is one line in an access table, that is slowly building on itself so that I can have a relational view of the data. (Which I can't have with hundreds of excel worksheets)

You can import directly from Excel, but be careful as it gives your users access to your DB

something like:

Please Login or Register  to view this content.

Here is the code I use for the exact same issue, You can place the code in a macro or before close or on open . You will need to reference DAO

Please Login or Register  to view this content.

End Sub