Import multiple excel files to MS Access XP

[ivancycheng]

I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm very new in using VB). Many Thanks!

[NeoPa]

With the little information you include in the question, I can only think of telling you about using Import & Link Tables.

Importing can import data from Excel into an Access table. It can create the table as part or the process or it can append the data to an existing table.

If you link to an Excel worksheet then that sheet appears in your database as a table.

If you need more help you will need to make your question clearer.

Welcome to Bytes!

[ADezii]

If you just want to bring Worksheets into a new Table, the easiest way, without even writing a single line of code, is with the Windows Clipboard. Access is sufficiently intelligent to recognize Excel data pasted into the Clipboard, and Access automatically creates a new Table based on that data when you do a Cut-and-Paste operation. If you do this, it is very helpful to include Field Names in the top Row of the selected data of the Excel Worksheet. Access automatically assigns those Worksheet Column Headings as Field Names in the new Table. A few simple steps and you are on your way:

1. Open the Excel Worksheet and select the data that you want to use to create the Table in Access. If possible, include Column Headings in the selection that can be used as Field Names.
2. Choose Edit|Copy, Right Click|Copy, etc...
3. Switch to Access and, with the Tables portion of the Database Window active, choose Edit|Paste, Right Click|Paste, etc...
4. Access will ask whether the first Row contains Column Headings, answer appropriately.
5. Good luck, and let us know how you make out.

[ivancycheng]

Thanks for all your reply! For more information : I have create a template with 10 sheets to estminate the next year budget. Totally I have around 400 files now and I want to consolidate 1-2 sheets in the template to a access database. Any one can advice me how to import these 400 templates to the access (I think VB is nuch useful in these case but I'm fresh on this). Many Thanks!

[NeoPa]

Again, with the information you provide, it's unclear what you are dealing with.

Presumably these "templates" are really just workbook files where you have data.

As there are so many of them I expect you would like the importing to be done within your code, yet you tell us nothing about where these files are stored and under which names. Without this, there is no way to see if this can be done algorithmically .

So, in relative darkness, I can only suggest that you create a loop in your code that goes through each workbook file.
For each file import the data into a previously prepared table (the data should append to any existing data). When the loop processing has completed all your data should be available in this table.

[ivancycheng]

Thanks for your advise. All files store in same folder but the file name is difference (e.g. aa123, aa234......) they have same sheet name (e.g. rev09, rpk09.....) can anyone provide the VB code to me?

[NeoPa]

No. Someone cannot simply provide you with code. That would be against the rules of the site.

All we can do is point you in the right direction. Even this will be difficult as it is still not clear what you need. We have asked for clarification, but it doesn't seem to be much clearer now than before.

You don't provide the folder name. You don't clarify what you mean by template. You don't explain what we can expect as far as filenames or worksheet names are concerned (although you provide a couple of examples for each).

I could not code from this, and I have already pointed you in the right direction.

Please don't ask for more help until you've at least tried to produce something, and then you can explain clearly and precisely what you are having difficulty with.

We are here to help you learn, but generally don't appreciate being treated like a free coding service.

[cassiomoreno]

Actually VB is very useful, but more than doing a loop is how to do it. Because operations inside access are easy, but operations with windows are a little bit more complicated.


If I were you, I would put on a table the route to the directory where the files are in a column and the names of the files in another.

You may think is too muck work to copy all of them... maybe.
I'll write you a code below where you can get the names of the tables, than you treat them in excel with text extraction process... like the funtions mid, left, right, etc...

so:
1. open notepad
2. write the following code:
c: (or another prompt, depending on which you use)
cd " direction of the container folder"
dir /s > filename.txt

3.This will generate a text file with the name you chose. Open it, then you can copy all of the file's names, paste in excel adn get the names to paste on the access table i told you before.

4. Create a very simple loop in VBA:
a. Create a loop
b. inside the loop, create an importing process
There you have, everything imported.

To put them all together you can create a union query on SQL. Which is very simple and you can find millions of instructions in thousands of languages on google.

If you have a little ability with Access VBA, from creating the the batch file to have it all imported, you can have it all done in less than an hour.

Seems complicated, but it's the most simple I've ever found for a start process, or paliative process.
For a recurrent process, then you have to write the complete code: from reading the directory to adding the imported files throughout the loop...

Hope to have helped.