Start Free Trial

asked on

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

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

ASKER

But I get this doc from another old program and then it should automatically entered into the database

ASKER

jeli moguce ovakav xls procitati u netu.

ASKER

excuse me ... is it possible to read this doc in vb net.

Try removing IMEX=2.

ASKER

External table is not in the expected format.

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'.

ASKER

the same mistake. URGENT... I'll give double points for the answer.

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.

ASKER

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

ASKER

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.

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.

User generated image

ASKER

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

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.

ASKER

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

ASKER

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

ASKER

same content but the doc can. and the first source can not

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?

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?

ASKER

All documents have the same problem

Have you tried opening them in other ways using VB.NET?

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

after

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

i get

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

ASKER

which version of the office and vb net

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.:)