[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

cant open excel in vb net

Posted on 2011-10-30
26
Medium Priority
?
415 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:cnermin
  • 15
  • 11
26 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 37052822
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
 

Author Comment

by:cnermin
ID: 37052874
But I get this doc from another old program and then it should automatically entered into the database
0
 

Author Comment

by:cnermin
ID: 37052876
jeli moguce ovakav xls procitati u netu.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cnermin
ID: 37052893
excuse me ... is it possible to read this doc in vb net.
0
 
LVL 35

Expert Comment

by:Norie
ID: 37052985
Try removing IMEX=2.
0
 

Author Comment

by:cnermin
ID: 37053041
External table is not in the expected format.  


0
 
LVL 35

Expert Comment

by:Norie
ID: 37053048
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
 

Author Comment

by:cnermin
ID: 37053049
the same mistake. URGENT... I'll give double points for the answer.
0
 
LVL 35

Expert Comment

by:Norie
ID: 37053065
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
 

Author Comment

by:cnermin
ID: 37053081
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
 

Author Comment

by:cnermin
ID: 37053097
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
 
LVL 35

Expert Comment

by:Norie
ID: 37053132
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
 

Author Comment

by:cnermin
ID: 37053161
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
 
LVL 35

Expert Comment

by:Norie
ID: 37053175
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
 

Author Comment

by:cnermin
ID: 37053196
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
 

Author Comment

by:cnermin
ID: 37053216
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
 

Author Comment

by:cnermin
ID: 37053218
same content but the doc can. and the first source can not
0
 
LVL 35

Expert Comment

by:Norie
ID: 37053220
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
 
LVL 35

Expert Comment

by:Norie
ID: 37053240
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
 

Author Comment

by:cnermin
ID: 37053264
All documents have the same problem
0
 
LVL 35

Expert Comment

by:Norie
ID: 37053306
Have you tried opening them in other ways using VB.NET?
0
 

Author Comment

by:cnermin
ID: 37053352
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
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 37053376
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
 

Author Comment

by:cnermin
ID: 37053442
after

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

i get

"Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
0
 

Author Comment

by:cnermin
ID: 37053448
which version of the office   and vb net
0
 
LVL 35

Expert Comment

by:Norie
ID: 37053866
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question