Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel VB.NET OleDb
I am having the code as follows in vb.net
 
Dim connectionString As String =[String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", filepath)
Using oleDbConn As New OleDbConnection(connectionString)
oleDbConn.Open()
 

when i try to open *.xls file it works fine but if I try to open *.xlsx page than it throws me an error External table is not in the expected format. I searched in net but couldnot sort out with it. Please someone help me to sort it out its really urgent
Posted 30-Jan-13 23:31pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Use the following code as the Excel 2007 vcersion files are Excel 12.0
 
Dim connectionString As String =[String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", filepath)
 

Best of luck!!
  Permalink  
Comments
sagarjainhr at 31-Jan-13 4:56am
   
It didnt work
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You need OLEDB 12 drivers installed on your machine.
Install from this link
 
http://www.microsoft.com/en-us/download/details.aspx?id=13255[^]
And the code will be like this .
 
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            // if you don't want to show the header row (first row) use 'HDR=NO' in the string
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            // Get the data table containg the schema guid.
            DataTable dtWorksheetTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtWorksheetTables == null || dtWorksheetTables.Rows.Count == 0) return null;
            string worksheetName = GetWorksheetName(dtWorksheetTables);
            string strExcelSQL = "SELECT * FROM [" + worksheetName + "]";
            OleDbCommand oleDbCommand = new OleDbCommand(strExcelSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleDbCommand);
            DataTable excelDataTable = new DataTable();
            dataAdapter.Fill(excelDataTable);
            //Dispose 
            dataAdapter.Dispose();
            oleDbCommand.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();
            GC.Collect();
  Permalink  
v3
Comments
sagarjainhr at 31-Jan-13 4:56am
   
I have installed access database 10 installed in my machine still its throwing an error
aspnet_regiis -i at 31-Jan-13 5:03am
   
Change the conenction string to
 
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
sagarjainhr at 31-Jan-13 5:06am
   
I am using the vb.net platform and not C# so cant use "\" slashes as its throws an error.
If I convert it to vb.net and use also its throwing the same error
aspnet_regiis -i at 31-Jan-13 5:11am
   
Dont look at the \ . Look at the contents of the connection string.... You are using Excel 8.0.. Instead use 12.0.. Did you get my point or should I explain it in more detail?
sagarjainhr at 31-Jan-13 5:19am
   
I tried using Excel 12.0 still its giving the same error. I am struck up over here and I am really not getting what to do I tried with 8 & 12 both but still not able I am getting the same error
aspnet_regiis -i at 31-Jan-13 5:20am
   
Post your complete code.. Your reply is not enough to guess what is wrong...
sagarjainhr at 31-Jan-13 5:24am
   
Private Sub importFromExcel(ByVal filepath As String)
Try
Dim tablesNames As DataTable
Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;""")
Using oleDbConn As New OleDbConnection(connectionString)
oleDbConn.Open()
tablesNames = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If tablesNames Is Nothing Then
cmbNames.Items.Add("-Select-")
Else
 
cmbNames.Items.Clear()
cmbNames.Items.Add("-Select-")
For i As Integer = 0 To tablesNames.Rows.Count - 1 Step +2
Dim tableName As String = tablesNames.Rows(i)("TABLE_NAME").ToString().Replace("$", "")
tableName = tableName.Replace("'", "")
If tableName.StartsWith("_") Then
Else
cmbNames.Items.Add(tableName.Trim())
End If
Next
End If
 

End Using
Catch generatedExceptionName As Exception
MessageBox.Show(generatedExceptionName.ToString)
 

Finally
OleDbConnection.ReleaseObjectPool()
 
End Try
End Sub
sagarjainhr at 31-Jan-13 5:26am
   
this is my code I want to fetch the *.xlsx file and access worksheet of it but i am not able to access *.xlsx file but I can access *.xls file. As all the datas are there in *.xlsx Its very much necessary to access *.xlsx file than *.xls file
When oleDbConn.Open() tries to execute it throws an error External table is not in the expected format.
aspnet_regiis -i at 31-Jan-13 5:30am
   
The code seems to be correct.. Can you tell me the line number where this error is thrown ?
sagarjainhr at 31-Jan-13 5:33am
   
it says line 59 the same line where oleDbConn.Open() code try to executes
sagarjainhr at 31-Jan-13 5:34am
   
Private Sub btn_FileBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_FileBrowse.Click
 
Using ofd As New OpenFileDialog()
cmbNames.Items.Clear()
chklboxName.Items.Clear()
ofd.Title = "Select file"
'If tbBrowse.Text = "" Then
ofd.InitialDirectory = "My Documents:\"
'Else
' ofd.InitialDirectory = tbBrowse.Text
'End If
ofd.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
If ofd.ShowDialog() = DialogResult.OK Then
filename = ofd.FileName
tbBrowse.Text = filename
Try
System.IO.File.Open(filename, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
Catch
Dim Response As MsgBoxResult = MsgBox("Please ensure that the file is closed before proceeding", MsgBoxStyle.Information, "Alert")
tbBrowse.Text = ""
End Try
Else
tbBrowse.Text = ""
Dim Response As MsgBoxResult = MsgBox("No file selected, Please select a file to proceed", MsgBoxStyle.Information, "Alert")
MessageBox.Show("No file selected, Please select a file and proceed")
End If
Call importFromExcel(filename)
End Using
End Sub
 

 
Private Sub importFromExcel(ByVal filepath As String)
Try
Dim tablesNames As DataTable
' Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", filepath)
'Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""", filepath)
' Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""", filepath)
Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;""")
Using oleDbConn As New OleDbConnection(connectionString)
oleDbConn.Open()
tablesNames = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If tablesNames Is Nothing Then
cmbNames.Items.Add("-Select-")
Else
 
cmbNames.Items.Clear()
cmbNames.Items.Add("-Select-")
For i As Integer = 0 To tablesNames.Rows.Count - 1 Step +2
Dim tableName As String = tablesNames.Rows(i)("TABLE_NAME").ToString().Replace("$", "")
tableName = tableName.Replace("'", "")
If tableName.StartsWith("_") Then
Else
cmbNames.Items.Add(tableName.Trim())
End If
Next
End If
 

End Using
Catch generatedExceptionName As Exception
MessageBox.Show(generatedExceptionName.ToString)
 

Finally
OleDbConnection.ReleaseObjectPool()
 
End Try
End Sub
aspnet_regiis -i at 31-Jan-13 5:36am
   
Okay.. Install these OLEDB drivers http://www.microsoft.com/en-us/download/details.aspx?id=13255
sagarjainhr at 31-Jan-13 5:36am
   
Where btn_FileBrowse_Click() fnc browse the file and calls the importFromExcel() function
and this is how the function work
aspnet_regiis -i at 31-Jan-13 5:37am
   
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
sagarjainhr at 31-Jan-13 5:42am
   
Its also not working....
sagarjainhr at 1-Feb-13 0:22am
   
Thanks for your help
Its working fine now. thr was no problem with connection string it was problem with browsing the file function I just commented 1 block its working fine
Member 10684591 at 26-Mar-14 10:12am
   
i am also facing same problem can you just share the connection string
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
sagarjainhr at 31-Jan-13 4:57am
   
I have tried with this link still not able to sort it out
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I solved it by my self there was no problem with the connection string and everything was fine over there. The problem was with I was checking whether the file is open or not and I have not closed that file after that process. so it was used by other process and connection string was failing to access the excel sheet and it was throwing an error.
 
Please ensure that the file is not open or used by any other process while trying to read with *.xlsx file, If file is open also it works fine with *.xls and not in *.xlsx file keep it in mind while accessing *.xlsx file
  Permalink  
v2
Comments
vikramaditya9 at 26-Nov-13 3:20am
   
I have also facing same problem "External table is not in the expected format".I am using Connectionstring like > ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
Please suggest anyone any problem with connectionstring?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100