Question Read the excel file

[pactools]

I am able to read single excel file,I have folder (C:\BOM) in that i have no.of excel files is there. So what we have written code is for single file. but i need to automate this process.. Application automatically read the selected excel file . Code should read both 2003 and 2007 excel files Can any one help me on this,thanks

Code to read the single excel file

Dim _filename As String = "C:\Book.xls"
Dim _conn As String
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _fileName & ";" & "Extended Properties=Excel 8.0;"
Dim ds as new Dataset
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da as oledbDataAdapter=New oledbDataAdapter
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection
_command.CommandText="Select * from [sheet1$]"
da.selectCommand=_Command
da.fill(ds)
gridview1.datasource=ds
gridview1.databind()

 

[kulrom]

For Each strfile As String In Directory.GetFiles("C:\Bom")
            Dim excel As New FileInfo(strfile)
            If excel.Extension = ".xls" Or excel.Extension = ".xslx" Then
                ' do something 
            End If
        Next

 

[kulrom]

Or you may use the following approach:

Dim filters As String = "*.xls;*.xlsx"
        Dim extensions() As String = filters.Split(";"c)
        For Each filter As String In extensions
            Dim files() As String = Directory.GetFiles("C:\Bom", filter)
            ' do something with the files 
        Next

 

[vforvijay]

hi...if you want to read both .xlx and .xlsx file check this it may helpful to you ....
.NET ASP.NET ARTICLES IN VB coding: IMPORT THE EXCEL TABLE INTO SQL TABLE