Importing data from Excel to Access

[Yukipilas]

Hello,

i would like to transfer data to Access from Excel with a macro. I am quiet new to access so i have no idea of what to do. I have read some forum posts and i managed to transfer a worksheet from excel to access with a macro from Ms access (importexportspreadsheet).

I want to do this with a macro, because i have lots of workbooks, and the import wizard may be too slow for this. My questions are the following:

- What is more common or more flexible, using a macro from Access or Excel to perform the task ? any examples?
- The data is already prepared in Access, how do i program the macro to read the data correctly in order to append it in the existing table (define field, etc..)
- how to import/export multiple excel worksheets to access from a single workbook ?

I appreciate some example code so i can get started,

best regards,

yukipilas

 

[Micron]

Did you rule out simply linking the spreadsheet file as a table in Access? Each time you open the db, the 'table' records would be refreshed.
Ribbon > External Data tab, Import > Excel choose link, find and choose file & it will show you a list of sheets in it.

 

[Yukipilas]

Yes,

your suggestion only enables me to do it for 1 worksheet. Each workbook has around 12 worksheets and i have hundreds of workbooks with several worksheets in them.

Still waiting for some feedback about, any coded macro templates for transfering data into Access

Best regards,

yukipilas

Did you rule out simply linking the spreadsheet file as a table in Access? Each time you open the db, the 'table' records would be refreshed.
Ribbon > External Data tab, Import > Excel choose link, find and choose file & it will show you a list of sheets in it.

 

[Micron]

So I meant you link all sheets, one at a time.

 

[xenou]

Note: linked worksheets as tables locks the workbook the sheets are in and depending on the situation you may not want that to happen so beware.

 

[Yukipilas]

Hi,

i have tried that, it is not a bad idea when you want a non- programmed solution.

The problem comes as soon as i want to update the table, that means add more data. When i delete the old data and insert the new data in excel, because the Excel table is a mirror of the Access, it will not add the new data but rather replace it.

I do not want to have a giant Excel table with all the data, unless there is a workaround to this way of importing data,

i am still looking for programmable examples to import data from Excel to Access,

Best regards,

yukipilas

 

[xenou]

Since linking so many workbooks (you do have a lot of them here) sounds awkward, I'd agree some kind of programming using a loop structure might be best. It's hard to write an entire program for you but if you want someone to give it a shot you must provide very detailed and clear instructions - the exact structure of the workbooks, the name of the table(s), that will be the destination, the location of the files, and so on. Also be prepared for problems unless the data in Excel is very consistent - always structured the same and with and no anomalies.

There's probably good information on how to use the transferspreadsheet method by googling for that. An Ado tutorial can be found here: Excel, Access and ADO: Part 2 -- DataWright Information Services You might also consult with MrExcel consulting services or another expert to have this done for you if you can't program it yourself.