0

I download an Excel 2003 formated XLS file form a web page (No choice on formatting) and need to extract the data. I should be able to read the contents as a database without the need to open it first but it won't let me read it without opening it first!

Since its a 2003 xls file if I do open it it gives a warning that it might be corrupt blah blah and I need to confirm the opening of the file! Then I click on my read data and it shows it fine in my datagridview! 

Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel  
Try

            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim dataSet As System.Data.DataSet
            Dim datareader As OleDbDataReader
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim path As String = "C:\holding\list_demands.xls"
            MyConnection = New 
System.Data.OleDb.OleDbConnection("Provider=Microsoft.ace.OLEDB.12.0;Data 
Source=" + path + ";Excel 8.0 XML;HDR=YES;Format=xls")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from 
[List_demands$]", MyConnection)
            MyConnection.Open()
            dataSet = New System.Data.DataSet
            MyCommand.Fill(dataSet)
            DataGridView1.DataSource = dataSet.Tables(0)

            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
Peter
  • 148
  • 1
  • 2
  • 18
  • 2
    Verify that the downloaded file in really an Excel file. Many websites download Html (text) and just give it a `.xls` extension to _trick_ Excel into opening it. Excel detects that it is Html and converts it. Try opening the file in Notepad. If it is readable text, it is not an Excel file. – TnTinMn Nov 09 '19 at 16:18
  • Yeah I just saw that, I changed the file extension to html and chrome opened it and showed the data! Now I just have to figure out how to read it as html or convert it to a valid XLSX formated file! – Peter Nov 09 '19 at 16:29
  • What bugs me is that windows detects the dupe and THEN requires confirmation.If I manually accept then the code works fine even if its in html format! An actual excel.xls file also works fine with the need to confirm! – Peter Nov 09 '19 at 16:31
  • 1
    @Peter If you *know* it is HTML in the file then you could look at [HTML Agility pack - parsing tables](https://stackoverflow.com/q/655603/1115360) for inspiration. – Andrew Morton Nov 09 '19 at 17:05
  • Just installed the Agilitypack and it did the trick! Thanks Andrew – Peter Nov 11 '19 at 19:32
  • I'd mark the comment from Andrew Morton as the answer but , I don't see the checkmark! – Peter Nov 11 '19 at 19:37

0 Answers0