title: Import Excel into Access, get column headers dynamically
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>I want a programmatic solution. </p>
  
  <pre><code>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 &amp; "*.xlsx")
      strTable = Left(strFile, 8)
      strPathFile = strPath &amp; strFile
      'Debug.Print (createTable("hello", "asdasd"))
  
      Do While Len(strFile) &gt; 0
          strPathFile = strPath &amp; 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
  </code></pre>