Batch import Excel Spreadsheets


I have a hundred or so Excel spreadsheets that are identical in structure and I'd like to batch import them into the same Access ttable with the method being: use first row as field names. Is there a method to do this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if all of your files are in one folder you could try

Sub Import()
Dim strPath As String
Dim strFileName As String
Dim strFileType

strFileType = "*.xls"
strPath = "C:\MyFiles\" ' add your path here

strFileName = Dir(strPath & strFileType)

While Not strFileName = ""
Debug.Print strFileName
DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, True
strFileName = Dir()
End Sub

Chris_GrangerAuthor Commented:
Thanks guys these examples are just what I need. On a more basic level, my skills are with ASP - VB Script, PHP, JavaScript, and so on but I understand the VB code and can work with it, I'm just not sure how to implement it. As in, does this get put into a .bat file or a macro in Access or do I need Visual Studio to compile an exe, etc? Could you explain exactly where I put this code and how to trigger it. I think I have an old dev. version of Visual Studio somewhere if that's necesary. This would be very handy for me to know when I need to implement these sorts of tasks so any help appreciated...

Many Thanks
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

It goes in a module, just go to the module tab and click new.
paste the code in, changing names a needed.

with the cursor in the code press F5 and the code will run.

If this is a "one off" the is no need to even save the code but if you want to keep it then make sure that you save the module with a differnt name to the sub or Access will spit its dummy out when you try to run it.


Chris_GrangerAuthor Commented:
Cool, I'm nearly there, except it's telling me f2 field doesn't exist in database. The spreadsheet looks pretty clean but I may still need to isolate what it is importing and to what fields. Is there a way to tell it to:

1. Import a range of cells, for example: a2 to u16?
2. Code it so that: "row a" imports into "fieldname a", "row b" imports into "fieldname b", etc?

you can add the range to the end of the Transferspreadsheet command

DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, True, "A2:U16"

As long as the field names are the same it will match them in any order. (that was the "True" argument)
If the fields are in the right order you can set that to false and set your range to exclue the first row of names

DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, False, "A2:U16"


Chris_GrangerAuthor Commented:
Thats really cool! I'm using the false command but it requires my database fields be renamed to f1, f2, f3...  Is there a way to instruct it to use certain db fieldnames are? Otherwise, I'll just name all my fields in db tables as f1,f2... and then rename them to what i require after importing - not too much hassle.

The only real clincher is that the length of the spreadsheets (U16) is variable. Is there a way to get the length of the spreadsheet so that "U16" becomes a variable: "U" & rowlength?

I dont know any way to make the length variable, but, you can use NamedRanges rather than a set range. This only helps if you can get a range in the Spreadsheets first though! I am sure that one of the XL guru's would be able to do you a code that would go through each file check the range and add a name for it though.

I thought that it should just ignore names and put them in any field if you set it to false. From Help

HasFieldNames  Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed.

Each field in the spreadsheet must be of the same data type as the corresponding field in the table.
The fields must be in the same order (unless you set the Has Field Names argument to Yes, in which case the field names in the spreadsheet must match the field names in the table).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dynamic excel named ranges ...

make a name in Excel (Insert --> Name) and use the OFFSET function to determine the size ...
my excel library is at home, I don't have an example handy ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.