Read Excel file except fist 14 rows

[NickThissen]

Hi,

I need to read an Excel file (.xls, 2003 format). The data in the excel file is composed of two parts:
- The first 14 rows consist of all kinds of stuff, buttons, merged columns, stuff I don't need. You can see this as a header for the worksheet.
- The next row (number 15 viewed in Excel) contains column header names
- All remaining rows contain the data I need to read.


I have tried reading this file using two different methods. The first method is simply using OleDb:

Private Function GetDataTable(ByVal sheetName As String)
        Dim dt As DataTable = Nothing
 
        Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR={1};IMEX={2}"";", _
                                       Me.FilePath, _
                                       "No", _
                                       "1")
        Using conn As New OleDbConnection(connString)
            Using cmd As New OleDbCommand()
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = String.Format("select * from [{0}$]", sheetName.Trim())
 
                conn.Open()
                dt = New DataTable
                dt.Load(cmd.ExecuteReader)
                conn.Close()
            End Using
        End Using
 
        Return dt
    End Function

I have tried fiddling with the HDR and IMEX settings, but the result is always the same. I keep getting an error that says "Not a legal OleAut date", which is supposed to mean it cannot convert a date to .NET DateTime format.

I am sure this error occurs because it is trying to read all the stuff in the first 14 rows but fails, because it does not contain simple data.

So my first question is:
How can I tell it to only start reading after row 14?

The file comes from a third party so I have no control over the contents. I could make a copy of the file and delete the first 14 columns, then try to read that copy instead, but the only way I think that is possible (I haven't tried this though) is via Office Interop, which brings its own problems.





That brings me to the second method I tried to read the file: using Office Interop.

I simply create a new Excel.Application, open the Workbook (excel file), find the correct worksheet (there's multiple sheets that I have to read) and read the data from the cells directly.

This works. I can read the data. But there is a very big problem: this method seems to actually open an instance of Excel and the workbook, and after it is closed it actually closes this instance again. When that happens, Excel tells the user that the file has changed and asks whether it needs to be saved. It seems that merely opening the file changes something (because my code is only reading, not changing, and the same happens when I just open the file directly in windows), and when it is closed a dialog pops up asking for user input.

This is a big problem, because my code does not continue until the workbook is closed (workbook.Close method). And the user has to close the workbook. This application is supposed to be run as a service, and it must run once per day, and I cannot guarantee that a user will be around every day to close the Excel dialog...


EDIT
See post 2 for update


Thanks for any help!