External table is not in the expected format error while trying to access Excel


I am trying to access Excel from my vb application by using the Oledb Connection . Here is my connection string:

<add key="ExcelConnection" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 12.0 Xml;HDR=NO;IMEX=1;MAXSCANROWS=1000;READONLY=FALSE;&quot;" />

Here is the code:

 Dim connection As New System.Data.OleDb.OleDbConnection(ExcelConnectionString)
 connection .Open()
 Dim schTbl As DataTable = connection .GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)

 Dim dbAdapter As System.Data.OleDb.OleDbDataAdapter
 Dim execQuery As String
 Dim rtnData As System.Data.DataTable

 execQuery = "SELECT * FROM [" & schTbl.Rows.Item(0)("TABLE_NAME") & "]"

    dbAdapter = New System.Data.OleDb.OleDbDataAdapter(execQuery , ExcelConnectionString)

    dbAdapter.Fill(rtnDat )

    connection .Close()
    schTbl .Reset()

What my problem is that ,the above is working 70% of the times but rest of the times i am getting the following error:

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.

Could anyone please help me that why at times i am getting this error .Is there any way to rectify it or any other way to achive the same result.

Any help will be appreciated.


asked on Stack Overflow Mar 17, 2015 by Vipin Nair • edited Mar 17, 2015 by Vipin Nair

1 Answer


External table is not in the expected format. typically occurs when trying to use an Excel 2007 file.

Using the following connection string seems to fix most problems.

dim path as string = yourfilepath
dim connStr as string = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"


some times this too may fail in a case when your are using an .xlsx file.if that is the case you should install a JET database engine in your pc to override those errors.it is availale http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

answered on Stack Overflow Mar 17, 2015 by akhil kumar • edited Mar 17, 2015 by akhil kumar

User contributions licensed under CC BY-SA 3.0