none
OleDbConnection to Excel

    Question

  • Hi,

    I'm having a little trouble reading from a excel file into a datatable using the OledbConnection and OleDbDataAdpter.  I get an error when calling the OleDbConnection.Open() method (""External table is not in the expected format") and the excel workbook I'm referencing isn't open.  I dont get this error if the file is open.  Am I doing something wrong or does the excel file need to be open for the connection to work?  Thanks in advance!

    Code:

    Private Function getSpreadSheetData(ByVal xlFileName As String) As DataTable

    Dim xlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"""

    Dim xlconn As New OleDbConnection(xlConnString)

    Dim xlda As New OleDbDataAdapter("select distinct G, J, M, N from [Arb Deals$] where E = 'USD'", xlconn)

    Dim xldt As New DataTable

    Try

    xlconn.Open()

    xlda.Fill(xldt)

    Catch ex As Exception

    MsgBox(ex.Message)

    Throw New Exception(ex.Message, ex.InnerException)

    Finally

    xlconn.Close()

    End Try

    Return xldt

    End Function

    Thursday, October 05, 2006 6:19 PM

All replies

  • Hi,

    Could be a couple of things and it depends on your data.... Unless you have a heading row in row 1 that contains the field names then you don't need HDR=Yes in the connection string. It looks like you don't have this row as your referencing the columns using A, F, etc.. but I could be wrong. 

    let me know if that fixes things as there could be other problems. Also it would be good to post an example of your data as it could determine a few things with your connection strings and your select statement. It can be dummy data but seeing the structure of your data would be good.

    Thursday, October 05, 2006 7:32 PM
  • What happens if you remove the double-quotes from around the extended properties in the connection string?  I've got very similar code right now and it seems to be working fine.

    Thursday, October 05, 2006 8:14 PM
  • My guess is, you are opening office 2007 (xlsx) with jet.oledb driver. So if the file is open you might not be getting any error.

    Either save you excel file in xls format (File Save As, office 97-2003 format)

    Or Download 2007 driver if you dont have

    And connectionstring for new driver looks like this

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlFileName  + ";Persist Security Info=False;Extended Properties=""Excel 8.0;HDR=YES"""

    Arjun Paudel
    Monday, June 15, 2009 5:27 AM
  • hi, my question is, how to connect with a excel file protected by password with oleDB?

    Wednesday, August 05, 2009 9:12 PM
  • Hi,

    I'm having the same problem. I want to open an excel 2007 file (.xlsx). I have downloaded and installed the new 2007 driver, change the connection string to the ACE machine but it still doesn't work :(

    here is a snipet of the code:

                string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ fileName + ";Extended Properties=Excel 12.0;";
               
                OleDbConnection DBCon = new OleDbConnection(con);
                DBCon.Open();

    It failed at DBCon.Open() with exception of "External table is not in the expected format"

    and it only work if i have the file open in excel application.. does anyone have the same problem and can help?

    thanks..
    Tuesday, August 11, 2009 1:00 AM
  • You may need the XML argument. See the below connection string for examples:

    http://www.connectionstrings.com/excel-2007


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, August 11, 2009 6:08 PM
  • Hi Paul,

    done that as well and still throwing that exception.. :


    string con = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " + fileName + ";Extended Properties =\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
                        OleDbConnection DBCon = new OleDbConnection(con);
                        DBCon.Open();

    Found out that it only have issues with files that have graphs on it.. guessing that the graphs caused the "external table not in the expected format" exception.
    Also thought that "IMEX=1" would solve the issue but no :(

    any suggestions?

    Wednesday, August 12, 2009 5:52 AM
  • Hi

    Have anyone found a solution for the issue. 

     

    Binil

    Tuesday, September 13, 2011 6:46 AM
  • Hi

    Have anyone found a solution for the issue. 

     

    Binil

    I opened the excel file and went for the "Save As" option. The file type displayed was "Xml Spreadsheet 2003(*.xml)". I saved the file as Excel workbook(*.xls) . Now I am able to open the OledbConnection without the error.

     

     

    Tuesday, September 13, 2011 7:12 AM