title: DoCmd.TransferSpreadsheet acImport error "External table is not in the expected format"
body: <p>Hi I am getting this error  </p>
  
  <blockquote>
    <p>"External table is not in the expected format"</p>
  </blockquote>
  
  <p>...when i try to transfer excel data to a access Table. I have data in text file that has a description field. In order to avoid truncation at 250 character limit, I convert the text file to EXCEL and then transfer excel to access database. But I get this error.... I have both excel 2010 and 2016 on my machine installed. thanks all in advance.</p>
  
  <pre><code>Sub ImportPPDE_v2()
  
  Dim fDialog As Office.FileDialog
  Dim strNewPath As String
  
  On Error GoTo GameOver
  
  Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  
  'Set up the fDialog variable
  'Set the Title, add a filter for text files, and set the initial filepath we want to look at, we are defaulting to the Clarity Extract folder
  fDialog.Filters.Clear
  fDialog.Title = "Select Latest Project Portfolio Data Extract File"
  fDialog.Filters.Add "*.txt", "*.txt"
  fDialog.InitialFileName = "G:\Clarity EPPM Extracts\"
  fDialog.AllowMultiSelect = False
  fDialog.Show
  
  'turn off warnings, we don't need to see this
  DoCmd.SetWarnings False
  
  'Check to make sure a file has been selected, and if so that the Project Portfolio Data Extract file has been selected
  If fDialog.SelectedItems.Count = 0 Then
      MsgBox "No File has been selected. Load actions have been cancelled.", , "No File Selected"
      GoTo GameOver
  ElseIf InStr(1, fDialog.SelectedItems(1), "Project Portfolio Data extract_") = 0 Then
      MsgBox "The file selected appears to be incorrect. It should be the   Data Extract file.  Load actions have been cancelled.", , "ERROR OCCURRED IN DATA LOAD"
      GoTo GameOver
  End If
  
  'First delete everything currently in the table
  DoCmd.RunSQL "Delete * from tbl_Project_Portfolio_Data_Load"
  
  
  'DoCmd.TransferText , "Spec_PPDE", "tbl_Project_Portfolio_Data_Load", fDialog.SelectedItems(1)
  
  'First isolate the file name from the selected path, then change the file extension to .xls
  strNewPath = Right(fDialog.SelectedItems(1), Len(fDialog.SelectedItems(1)) - InStrRev(fDialog.SelectedItems(1), "\"))
  strNewPath = "C:\" &amp; Left(strNewPath, Len(strNewPath) - 4) &amp; ".xlsx"
  
  'Copy the Portfolio data extract file to the user's C: drive as a .xls file
  Call SaveAsFile(CStr(fDialog.SelectedItems(1)), strNewPath)
  
  'Now import the new data as selected above
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_Project_Portfolio_Data_Load", strNewPath, True
  
  'Now update the Load_Date to be today
  DoCmd.RunSQL "Update tbl_Project_Portfolio_Data_Load set [Load Date] = #" &amp; Date &amp; "# Where [Load Date] IS NULL"
  
  'Let the user know the process finished successfully
  MsgBox "Project Portfolio Data Extract Data has been uploaded", , "Victory!"
  
  GameOver:
  
  'Turn our warnings back on
  DoCmd.SetWarnings True
  
  'Set this back to nothing
  Set fDialog = Nothing
  
  'Check if an error occurred that would prevent the expected data from being loaded
  If Err.Number &lt;&gt; 0 Then
      MsgBox Err.Description
  End If
  
  End Sub
  </code></pre>
  
  <p><strong>I get this erorr when my code try to transfer excel sheet into the empty table</strong></p>
  
  <p>here is save as code </p>
  
  <pre><code>Sub SaveAsFile(currpath As String, newpath As String)
  
  Dim wb As Workbook, strWB As String
  Dim NewWB As String
  
  'The purpose of this module is to copy a file from a given filepath to a user's C: Drive.
  'It is also converting the file from .txt to a .xls format
  'This is originally intended to be used with the Project Portfolio Data Extract load
  
  'Delete any existing workbook that is there now with the same name
  On Error Resume Next
  Kill newpath
  On Error GoTo GameOver
  
  'Open the current file
  Set wb = Workbooks.Open(currpath)
  
  'MsgBox wb.FileFormat
  'Application.DisplayAlerts = False
  
  'Save it as a .xls file
  wb.SaveAs newpath, xlNormal
  
  'Application.DisplayAlerts = True
  'MsgBox wb.FileFormat
  
  'Close the workbook
  wb.Close False
  
  GameOver:
  
  Set wb = Nothing
  
  End Sub
  </code></pre>