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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

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

docmd.transferspreadsheet acimport,, "tableName", "c:\folder\xl.xls",true, "nameofsheet!"
0
dqmqCommented:
Have you tried:

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


0

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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
Adobe Acrobat

From novice to tech pro — start learning today.