Import different excel files into MS Access 2010 tables automatically

Question

I'd like to import all Excel files (with different data and columns) from some directory into MS Access 2010 database, creating new table for each file. I've found the code to import files into one table:

Option Compare Database
Option Explicit

Function DoImport() 

Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

 ' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = True

 ' Replace C:\Documents\ with the real path to the folder that
 ' contains the EXCEL files
 strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"

 ' Replace tablename with the real name of the table into which
 ' the data are to be imported
 strTable = "tablename"

 strFile = Dir(strPath & "*.xls")
 Do While Len(strFile) > 0
       strPathFile = strPath & strFile
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             strTable, strPathFile, blnHasFieldNames

 ' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile

       strFile = Dir()
 Loop

End Function

But I need to create new table each time. Is it possible in VBA?

re: "I would be so thankful if someone can provide step by step example on creating buttons, modules and running." - Wow. If that isn't "too broad" I don't know what is. — Gord Thompson
I've been surfing web for long enough, but have not found examples on how to create button and link it with module. I believe it won't take long to give a quick guide. Anyways, my main problem is about creating new tables in loop. — Gyuzal R

HansUp HansUp · Accepted Answer · 2014-02-06T04:19:45

I think all you need to do is change the destination table name (the value of strTable) each time before you do DoCmd.TransferSpreadsheet.

In a comment you said you want the table name to be derived from the workbook file name. And, each time through your loop, another variable (strFile) contains the file name. So I think you could strip the file extension from that file name and use it as the Access table name.

Here is an Immediate window example which demonstrate how that can be done ...

strFile = "foo.xls"
strTable = Left(strFile, Len(strFile) - 4)
? strTable
foo

If that approach is suitable, revise the loop in your VBA code like this (untested) code snippet ...

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    strTable = Left(strFile, Len(strFile) - 4)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strTable, strPathFile, blnHasFieldNames
    strFile = Dir()
Loop

Import different excel files into MS Access 2010 tables automatically

3 Answers