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="Excel 12.0 Xml;HDR=NO;IMEX=1;MAXSCANROWS=1000;READONLY=FALSE;"" />
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
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
User contributions licensed under CC BY-SA 3.0