Access2010


I have a folder of ~20,000 excel files .xlsx extension that all have a common header and are roughly the same size.  I would like to import all of these files into an access database.   I attempted to write a macro/module in access and have searched the web for example code, all of the example code seems to be for 2007 version of office which has macro commands that aren’t supported in 2010 version.

I was able to get my VBA code to the point of opening the file and looping through the folder however finding a command to place it into access is problematic since the only one I found is a 2007 command….

If anybody has any ideas or skills in Access 2010 please let me know.

gorobbaAsked:
Who is Participating?
 
dqmqCommented:
Have you tried:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ImportSpecification", "FileName", True


0
 
Rey Obrero (Capricorn1)Commented:
it will be the same command line

docmd.transferspreadsheet acimport,, "tableName", "c:\folder\xl.xls",true, "nameofsheet!"
0
 
Rey Obrero (Capricorn1)Commented:

for .xlsx file use 10

it will be the same command line

docmd.transferspreadsheet acimport,10, "tableName", "c:\folder\xl.xlsx",true, "nameofsheet!"
0
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.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
"all of the example code seems to be for 2007 version of office which has macro commands that aren’t supported in 2010 version. "

I don't know of any commands that were depracated between Access 2007 and 2010, so any commands you find that work in 2007 will work in 2010.

Cap has shown you how to import a spreadsheet into a table, however, so you should be okay using that code (and it certainly works in 2010).

Note too that the maximum number of objects in an Access database is 32,768. This includes index tables and other items, so it's very possible that you'll over this limit. You might consider importing these into multiple databases, and determining which of these you actually need to work with, or importing all of them to the same table (which caps code would do, if you don't change the "tablename" paramenter).
0
 
gorobbaAuthor Commented:
What do you mean by "maximum  objects?  ( is a file considered an object or could you clarify?) thanks
0
 
Rey Obrero (Capricorn1)Commented:
Access database objects are
tables
forms
queries
reports
macros
modules

You can find all of the database and project specifications by clicking Microsoft Access Help on the Help menu in Microsoft Access, typing specifications in the Office Assistant, and then clicking Search to view the "Access Specifications" topic.
0
 
dqmqCommented:
The unspoken question:  does each spreadsheet import into a separate table (i.e. object) or do they get merged into one table?  In any case, with that many imports, you are likely flirting with the limitations of Access in more ways than one.

How much data are you talking about in all the spreadsheets combined?    
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, a single Access table will often contain several indexes, and Access will consider those to be objects as well - so you'll very quickly get to the limit if you import each of those 20k spreadsheets as a separate table.
0
 
Rey Obrero (Capricorn1)Commented:
gorobba,

are you sure the one you accepted as answer does what you want to do?????????????
0
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.