MS Access 2016 TransferSpreadsheet Import Error 3274: Not in the expected format

I'm trying to set up a macro using VBA in MS Access 2016 to import a number of .xls files into my table.

I was able to run this macro on 13 of the files, but after the 13th file, every remaining file throws a "Run-time error '3274': External table is not in the expected format." error on the DoCmd.TransferSpreadsheet line:

Function ImportAllExcel()
Dim myfile
Dim mypath
Dim finpath

mypath = REDACTED
finpath = REDACTED

ChDir (mypath)
myfile = Dir(mypath)

Do While myfile <> ""
  If myfile Like "*.xls" Then
    DoCmd.TransferSpreadsheet acImport, 8, _
        "Table Name", mypath & myfile, True

    ' Move imported files to Imported folder
    FileCopy mypath & "/" & myfile, finpath & "/" & myfile
    SetAttr mypath & "/" & myfile, vbNormal
    Kill mypath & "/" & myfile
  End If
  myfile = Dir()
Loop

MsgBox "Import complete."

End Function

I've attempted several "fixes" from other posts without success:

• Changing the SpreadsheetType to any other value (including blank, numeric, and all versions of acSpreadsheetTypeExcel)
• Opening the file and running the macro while open
• Opening and re-saving the file as .xls
• Opening and saving the file as .xls using a different name
• SetAttr to vbNormal before attempting TransferSpreadsheet

None of the column names contain any spaces (although one contains underscores and doesn't import the column at all on the ones that have successfully run, but that's a separate issue - I manually added the column to the Access table just in case but it's empty of data entries).

All of the .xls files are from the same source, in the same format, with the same column names and data types - they're an automated daily report from a machine source. The first 13 files imported just fine, and I can find no discernable difference between the files that ran and the ones remaining.

Can anyone help me understand what is happening with this macro and how to fix it?

EDIT TO ADD: I added an index to my table to prevent duplicate entries, which significantly reduced the number of imported records, but it still stops working on the exact same files. Manually running the import wizard on one of the files that the macro will not process works just fine, but I have a large number of files to import and would prefer to avoid manually importing them one-by-one.

I figured it out by more experimentation - Error 3274 can be caused by files that exceed Access's cell data limits. I believe it was that one Long Text column that was borking the import.

After manually importing some of the files, I ran into another error while trying to do a manual import - "The wizard is unable to access information in the file ''. Please check that the file exists and is in the correct format."

This led me to https://support.microsoft.com/en-us/help/2836058/access-errors-during-import-export-to-excel-xls which suggested trying .xlsx format... which fixed the manual import.

Since that worked, I added some code to my macro to convert the files to .xlsx format before import, and it fixed it and ran beautifully on all the remaining files.

In case anyone's interested, here's the results:

Function ImportAllExcel()

Dim myfile
Dim mypath
Dim endpath
Dim oExcel As Object
Dim oExcelWb As Object
Dim bExcelOpened As Boolean

' Folders to import from/to
mypath = REDACTED
endpath = REDACTED

ChDir (mypath)
myfile = Dir(mypath)

' Suppress confirmation of failed import rows caused by indexing
DoCmd.SetWarnings False

Do While myfile <> ""
  If myfile Like "*.xls" Then

    ' Convert XLS file to XLSX to prevent errors
    On Error Resume Next
        ' Use existing instance of Excel if already open
        Set oExcel = GetObject(, "Excel.Application") 
        If Err.Number <> 0 Then
            'Could not get instance of Excel, so create a new one
            Err.Clear
            Set oExcel = CreateObject("Excel.Application")
            bExcelOpened = False
        Else
            bExcelOpened = True
       End If
    On Error GoTo -1

    oExcel.Visible = False
    Set oExcelWb = oExcel.Workbooks.Open(mypath & myfile)
    oExcelWb.SaveAs Replace(mypath & myfile, ".xls", ".xlsx"), 51, , , , False
    oExcelWb.Close False
    If bExcelOpened = True Then oExcel.Quit

    ' Delete the converted file & select the new one
    Kill mypath & "/" & myfile
    myfile = myfile & "x"

    ' Import the file
    On Error GoTo SkipFile
    SetAttr mypath & "/" & myfile, vbNormal
    DoCmd.TransferSpreadsheet acImport, 8, "TABLE NAME", mypath & myfile, True

    ' Move imported files to Imported folder
    FileCopy mypath & "/" & myfile, endpath & "/" & myfile
    Kill mypath & "/" & myfile

SkipFile:
    ' Clear error handling
    On Error GoTo -1
  End If
  myfile = Dir()
Loop

DoCmd.SetWarnings True

MsgBox "Import complete."

End Function