Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Batch import Excel Spreadsheets

Posted on 2004-10-28
Medium Priority
Last Modified: 2008-02-01

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?

Question by:Chris_Granger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Expert Comment

ID: 12441648

Expert Comment

ID: 12441667
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


Author Comment

ID: 12441715
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 12441782
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.



Author Comment

ID: 12442487
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?


Expert Comment

ID: 12442557
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"



Author Comment

ID: 12442709
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?


Accepted Solution

Bat17 earned 1500 total points
ID: 12442814
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).

LVL 39

Expert Comment

ID: 12447095
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 ...

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question