cant open excel in vb net

Dim conn As OleDb.OleDbConnection
        Dim cmd As OleDb.OleDbCommand
        Dim da As OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim query As String
        Dim connString As String = ""
       Dim strFileType As String = System.IO.Path.GetExtension(text).ToString().ToLower()
              connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            Dim dt As New DataTable
        'Open connection
        Try
            conn = New OleDb.OleDbConnection(connString)
            If conn.State = ConnectionState.Closed Then conn.Open()

i have ERROR      "External table is not in the expected format."

MY FILE IN ATT
ERCU.xls
cnerminAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
This worked for me.

First part opens the file, saves it and closes Excel.

Then 2nd part extracts data from file without opening.
        ' open and save file
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook
        Dim inputFile As String = "C:\ERCU.xls"

        excelBook = excelApp.Workbooks.Open(inputFile)

        excelBook.SaveAs(inputFile)

        excelApp.Quit()

        excelApp = Nothing

        ' extract data from file
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\ERCU.xls';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [UNIS ERC$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window

Note, there will be a warning about overwriting the file just click Yes.
0
 
NorieVBA ExpertCommented:
The data in the file isn't fomatted as a table.

The first 10 rows appear to have some sort of header data, then there's some sort of table and that's followed by some other data (footer?).

I removed everything apart from the table and used this 'standard code' to load the table into a gridview.
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\ERCU.xls';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [UNISERC$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Open in new window

0
 
cnerminAuthor Commented:
But I get this doc from another old program and then it should automatically entered into the database
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
cnerminAuthor Commented:
jeli moguce ovakav xls procitati u netu.
0
 
cnerminAuthor Commented:
excuse me ... is it possible to read this doc in vb net.
0
 
NorieVBA ExpertCommented:
Try removing IMEX=2.
0
 
cnerminAuthor Commented:
External table is not in the expected format.  


0
 
NorieVBA ExpertCommented:
Can I ask what value is in text?

It should be the path and name of the file ECRU.xls.

I assumed that wasn't the problem, and it wasn't for me when I hardcoded the value as 'C:\ECRU.xls'.
0
 
cnerminAuthor Commented:
the same mistake. URGENT... I'll give double points for the answer.
0
 
NorieVBA ExpertCommented:
Not sure what you mean.

The data in the file is not in the right format to be loaded with this method

Try removing the header and footer rows I mentioned earlier, that worked for me with the code I posted and your code.

I also named the range, A11:L30?, which did have data in table format and that also worked.
0
 
cnerminAuthor Commented:
original name is ATT.
At first I thought it was a problem in the title, but it is not. I do normal open if opened first in MS Excel and save.I think that the old software from which I receive xls, xls export to the old format, Office 95 or 97
UNIS-ERC-9-1-11-1-49-06-PM.xls
0
 
cnerminAuthor Commented:
I get this doc in the export of some old software and could be changed before.
ako ga otvorim u office excel i snimim kasnije ga normalno citam u vb net.
a ako ga odmah otvorim u vb net, dobijem gresku.
0
 
NorieVBA ExpertCommented:
Sorry, but this the file format is not the problem - it's the format of the data in the file.

If I open the 2nd attachment it's the same thing - there's a header and footer, with a table in the middle

Can't you just open the file, delete the rows above and below the table, save the file and try the code again. Table highlighted in yellow.
0
 
cnerminAuthor Commented:
these documents get more and we should automatically be imported.  I have another solution to get the html. Can I somehow import into DataTable
data.html
0
 
NorieVBA ExpertCommented:
Same problem as far as I can see.

Is it not possible to simply open the files, deleted the problem rows and then import the data?

You could even write code for that if you wanted.
0
 
cnerminAuthor Commented:
to open the file, it is impossible, because these files have a thousand a day. How can I write code when you can not open the doc in vb net. can not be opened even if imports as a new workbook.How can I write code, you can just start coding
0
 
cnerminAuthor Commented:
If I open the xls and save, then he normally open regardless of the header and fute. ovo je otvoren i snimljen doc u att.  zato mislim da je verzija problem.
ERCU.xls
0
 
cnerminAuthor Commented:
same content but the doc can. and the first source can not
0
 
NorieVBA ExpertCommented:
Sorry but that's the only way I can see of doing this.

You say you can't open the document in vb.net?

This type of code isn't used to open documents, it's used to extract data from them without opening them.

Have you tried anything in VB.NET to just open the file?
0
 
NorieVBA ExpertCommented:
As far as I can see there is no difference in version, and yes once the file has been opened and saved it can be opened.

The only other thing I can think of is doing just that - opening and saving the documents.

Does the problem happen with all the documents?
0
 
cnerminAuthor Commented:
All documents have the same problem
0
 
NorieVBA ExpertCommented:
Have you tried opening them in other ways using VB.NET?
0
 
cnerminAuthor Commented:
like dis..

     Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open(obj)
        xlWorkSheet = xlWorkBook.Worksheets("name od xls")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)



BUT CAN NOT
0
 
cnerminAuthor Commented:
after

 " excelBook = excelApp.Workbooks.Open(inputFile)"

i get

"Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
0
 
cnerminAuthor Commented:
which version of the office   and vb net
0
 
NorieVBA ExpertCommented:
Sorry I don't knwo if I can help any further.

You posted VB.Net code and you appear to want a VB.Net solution.

The code I posted, and the code you posted,  works for me but you would need to adapt it for your purposes, setup, version etc.

I could post code in VBA for this, or at least part of it, but since you want VB.Net that might not be of any use.

Have you considered that there might be a problem somewhere else?

This is happening to 1000s of files after all.:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.