Excel – Import Excel into Access, get column headers dynamically

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