Routine to import

Hi All,

I would like to carry out a routine which imports a number of Excel Files which are held in my Folder (C:\lists) into my Access 2003 programme. All the Excel Files would be appended into the same Table (tbl_all) within my A03 programme.

Grateful if someone would provide me with the vba code to carry out this task at a touch of a button.

Please let me know if my request requires clarification.
PipMicAsked:
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.

[ fanpages ]IT Services ConsultantCommented:
The name of the worksheets in each MS-Excel workbook, & their individual layout, plus the definition/order of the columns within the [tbl_all] table would be useful.

A sample MS-Excel workbook (if they are all the same, or noting differences, if applicable), & an MS-Access database containing a blank table definition, may also help "Experts" engage with your question.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can import your files using TransferText, or TransferSpreadsheet:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "YourTableName", "FilePath"

TransferSpreadsheet article: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

To loop through files in a directory:

Dim sFile As String
sFile = Dir("Path to the folder", "*.xls")

Do While Len(Dir) > 0
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "YourTableName", "Path To The Folder\" & sFile
  sFile = Dir
Loop

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In addition to what Scott has said, there is also OLE automation.  That gives you the most control over the process.

What you do with that is start an instance of Excel, then control Excel from Access to get the data.

This allows you to move row by row, cell by cell, etc.

You might need to do this when the worksheet is not in a clean, consistent format.  

Try the TransferSpreadsheet first and just keep in mind that you can go further if you need to.

Jim.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That should be:

sFile = Dir("Path to the folder\*.xls")
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.