Import Excel into Access, get column headers dynamically

Question

I am currently creating a script that will import all of the excel sheets in a specified folder into a unique table in Microsoft access. Now, the thing is that this process is supposed to be done monthly or bi-monthly and the headers on these excel sheets changes often. I have attempted to use the DoCmd.Transferspreadsheet method but the issue i am running into is that the fields do not match the destination table. Now, i cannot just make a table with the appropriate field names and then import into that because, as i said, the excel file's headers change often.

i would like a way to import an excel sheet into a new table and the table should automatically adopt the fields of the excel sheet no matter what they are. So basically every time i import, a new table should be created with the appropriate fields.

the only workaround i have of this is to create a new table every time i import and loop over the first row of the excel file to find the names of the fields, which are then used in the creation of the table.

but this is a messy workaround. I know that it is possible to import into a brand new table using the microsoft access UI. It takes a few clicks and then its all good.

I want a programmatic solution.

Function loadData()

    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:\Bdz outputs\"

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

    strFile = Dir(strPath & "*.xlsx")
    strTable = Left(strFile, 8)
    strPathFile = strPath & strFile
    'Debug.Print (createTable("hello", "asdasd"))

    Do While Len(strFile) > 0
        strPathFile = strPath & strFile
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", strPathFile, False

        ' 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

If you can't find a way of doing it with the TransferSpreadsheet, you can write code to open and read the Excel Spreadsheet. It is possible to change the header names programmatically, or another option, if the excel column value types always match access column types, you could keep the access names and just insert after the excel header, row by row. — PKatona
I may not be understanding but if you specify a non-existing table name in the table argument of TransferSpreadsheet and specify True for column headers, MS Access imports spreadsheet with exactly with Excel file's headers. — Parfait

Gord Thompson Gord Thompson · Accepted Answer · 2016-01-22T22:40:51

One possible solution might be to use an "on the fly" link to the Excel data, e.g.,

CurrentDb.Execute _
        "SELECT * INTO myNewTable " & _
        "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\Gord\Desktop\foo.xlsx].[Sheet1$]", _
        dbFailOnError

or, as Parfait suggests in a comment above, this seems to work, too ...

DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="myNewTable", _
        FileName:="C:\Users\Gord\Desktop\foo.xlsx", _
        HasFieldNames:=True

... where [myNewTable] does not already exist.

Import Excel into Access, get column headers dynamically

2 Answers