0

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 )

    dbAdapter.Dispose()
    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.

Thanks

Vipin Nair
  • 517
  • 4
  • 9
  • 33

1 Answers1

0

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;"

Edit

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

akhil kumar
  • 1,598
  • 1
  • 13
  • 26
  • thanks for the reply.My connection string is almost similar to what you have shown ,and regarding your next suggestion.If that is a problm of database engine then it should throw the error everytime this is executed ,but it is only giving me error at random times. – Vipin Nair Mar 17 '15 at 05:23
  • may be you are using `.xlsx` file where you get exception. – akhil kumar Mar 17 '15 at 05:25
  • ok as my last suggestion,is [access 2010 engine](http://www.microsoft.com/en-us/download/details.aspx?id=10910) this installed on your machiene?? – akhil kumar Mar 17 '15 at 05:26
  • i am using .xlsx file only everytime. – Vipin Nair Mar 17 '15 at 05:26
  • ok so you are getting this exception when using `.xlsx` file?? and only for some random files? – akhil kumar Mar 17 '15 at 05:29
  • Yes only for random files (.xlsx) – Vipin Nair Mar 17 '15 at 05:47
  • i am not able to get into problem well as i am stuck with another.anyway this [excel database problem](http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format) will help. – akhil kumar Mar 17 '15 at 05:53