Transferspreadsheet method (Import) "External table is not in the expected format"

Question

In Access 2010 and 2013, same results. Can some dear person shed some light on how I can resolve this problem?

I'm doing a very straightforward automated import of excel data to a new table.  here is the code:       

Case ".xlsx"

            DoCmd.TransferSpreadsheet acImport, , sTableName, sFileName, True

'Note I'm purposely not specifying the Excel version (although I've tried this too, same results)

I get error message: "Error #3274, External table is not in the expected format."

The spreadsheet is generated from an external system, has an .xlsx extension, and it opens to Excel with no messages or apparent format problems, no odd properties that I can discern.

>SO, If I try to run the import, using either the VBA method or by using Access' External Data - Import, same error. If I OPEN the spreadsheet and SAVE it the problem goes away.

:-/ I 'd rather not make the user do this extra step three times a day.

Thanks for any suggestions!

Answer 1

Because you are already using VBA code then you could add code like the following to open the workbook with Excel and Save it prior to the import line of code.

Sub test2()

    Dim xlApp As Object

    Dim wkb As Object

    Dim wks As Object

    Dim sFileName As String

    Dim xlAlreadyOpen As Boolean

    On Error Resume Next

    Set xlApp = GetObject(, "Excel.Application")   'If Excel already open then use the already open instance.

    On Error GoTo 0

    If xlApp Is Nothing Then    'Excel was not already open

        Set xlApp = CreateObject("Excel.Application")

    Else

        xlAlreadyOpen = True    'Used to determine whether to Quit Excel (Don't Quit if already open)

    End If

    sFileName = "C:\Users\User\Documents\Excel\Test Macros\Import to Access.xlsx"

    'xlApp.Visible = True    'Optional. If NOT visible user does not see Excel open

    Set wkb = xlApp.Workbooks.Open(sFileName)

    'Set wks = wkb.Worksheets(1)     'Optional

    'Set wks = wkb.Worksheets("Sheet1")  'Optional. Alternative to previous line

    'wks.Activate   'Optional

    wkb.Save

    wkb.Close

    Set wks = Nothing

    Set wkb = Nothing

    If xlAlreadyOpen = False Then    'Don't Quit if Excel was already open

        xlApp.Quit

    End If

    Set xlApp = Nothing

    'Your import code goes here. Your Import code NOT tested

'DoCmd.TransferSpreadsheet acImport, , sTableName, sFileName, True

End Sub

Answer 2

Thank you OssieMac, this is good information. THOUGH it seems absurd to have to do all this just to do a simple import!  In the meantime I've settled on an acceptable workaround: when the source file generated as an .XLS instead of .XLSX, the Transferspreadsheet method works without a hitch. not sure why....

Any other input welcome.