none
Error "External table is not in the expected format" RRS feed

  • Question

  • When I try to open an Excel file it returns an error "External table is not in the expected format". The Excel file is in "web page" format. Interestingly, if I open Excel document with Excel and don't close it, no error occurs with the same code.

    Any idea?


    Saludos, Javier J

    Wednesday, April 27, 2016 3:55 PM

All replies

  • Hi Javier Jiménez,

    According to your error message, Usually, if your connection string and version of your excel is inconsistent, which could cause the issue, when you open the document by using excel, which could change the format, the following links about the issue solve it by changing the connection, please refer and change you connection string.

    http://stackoverflow.com/questions/28923158/external-table-is-not-in-the-expected-format

    http://www.codeproject.com/Questions/537510/Externalplustableplusisplusnotplusinplustheplusexp

    In addition, If you still occur the issue, could you please share a simple demo via OneDrive. we could reproduce the issue on our side and try to find a solution to solve it.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 28, 2016 1:32 AM
    Moderator
  • No result. This is the link to the Excel file.

    I tried to open it with differents connection strings:

        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"""

    But the same error still occurs.

    Why if this excel file is opened with Excel the error does not occurs using that connection string?


    Thanks,

    Javier J


    Thursday, April 28, 2016 6:59 AM
  • Are there any formulas in the Excel Worksheet? There are certain formats used by Excel that the OLEDB provider cannot read or process since it cannot resolve expressions like the Excel application can.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 28, 2016 11:33 AM
  • No, there are not any formula. I think it's happening because the Excel has the "web page" format but I can't modify that format.


    Thanks, Javier J


    Thursday, April 28, 2016 11:39 AM
  • Have you tried the HTML Import argument?

    https://www.connectionstrings.com/html-table/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, April 28, 2016 3:52 PM
  • No result. The result is the same error.

    Thanks, Javier J


    Thursday, April 28, 2016 4:33 PM
  • No result. The result is the same error.

    Thanks, Javier J


    I would suspect that you may need to use Excel automation instead of OLEDB since it doesn't appear that the provider can read the file. Another option is to use ExcelDataReader:

    https://github.com/ExcelDataReader/ExcelDataReader


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 2, 2016 11:46 AM