How to import multiple Excell files into Access tables ?

[ishchopra]

Hello Experts,

I have 34 files which i want to upload into Access as different tables, If i try to import one by one then it will take long time because i have to repeat this activity every month. Is there any way i can upload all of them in one go ??

If there is a solution using macro then please explain the steps i.e. where n how to create a macro in access etc.. (i am totally new to Access)

Any help on this is much appreciated.

Thanks,

[Robeen]

This is a pretty easy thing to automate as long as the names of your 34 spreadsheets is always the same and the field names in your spreadsheets never change.

I've found that the easiest way is for the first time you do it - manually Import each spreadsheet into a New Table.
This creates your 34 Tables for you in Access.

Then:
If you want to delete the previous months data from the 34 Tables, you can write Delete queries for each of the tables to empty them out each month before you import the new data.

Next:
Create a new Macro.

First Action: SetWarnings = No

Next 34 Actions:
OpenQuery - QryDeleteTable1
OpenQuery - QryDeleteTable2
etc . . .

Next 34 Actions:
ImportExportSpreadsheet - Import - . . . supply the details for each spreadsheet.

Optional Last Actioin - if you are running this on your Windows Scheduled Tasks:
QuitAccess

I hope this helps.

[rpeare]

These are the important questions:

1. Do the file names of the excel spreadsheets change from month to month or are they permanent?
2. Do the spreadsheets contain cumulative (they have all the data you're interested in) from month to month OR are they NEW data only (you want to append information to an existing set of data)
3. Are your excel files all carrying the same fields or are they all carrying different fields? Can you combine separate excel files into a more normalized structure?

If the excel files are named the same and in the same location month to month and they are CUMULATIVE (they have all the information you need) you can link the excel files directly into access. Any time someone makes an update in the excel file you will have the most updated information, no need to worry about importing data at all.

If the excel files are named the same and in the same location month to month but they are ADDITIVE (the have only NEW or UPDATED) information that's when you would want to use a full fledged import rather than a link. However, it would have to be more robust than robeen is suggesting in that you would need to check existing records for each line in your excel file to see if record already exists and update it accordingly.

if your file names/locations vary from month to month that's an entirely more complex problem so I'll wait to answer that until I see more.

[BostonBuckeye]

why not just link them - make sure always use the same name for the Excel files - I do this for 40-50 Excel files - just never change thir names