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?
Bat17Connect With a Mentor Commented:
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).

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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
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?

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 ...
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.

All Courses

From novice to tech pro — start learning today.