Importing data from an Excel spreadsheet into a VB 2005 application SQL table

Original post by Carl Am at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2375068&SiteID=1

The Question :

I am trying to re-write a VB6 application in VB 2005. One of the problems I have is I need to be able to import a spreadsheet into an SQL table. I have my VB 2005 application interface, where I need to enter the filename to import and then click a button. The old VB6 way was the transferspreadsheet command. I set my application to use an Access database.

What is the best way to achieve this in VB2005?.

The accepted answer : (by gpasp)


Private Sub loadDataFromExcel()
Dim excelFile As String = "c:\someDirectory\someFile.xls" 'replace with valid path - file
Dim excelSheet As String = "[Sheet1$]" 'replace with valid Sheet Name
Dim sql As String = "SELECT * FROM " & excelSheet ' add WHERE and ORDER if required
Dim tableName As String = "theNameYouLike"
Try
Using cn As New OleDb.OleDbConnection
' HDR = YES if first row contain column name else NO
' Excel 8.0 / 9.0 ... depending on your Excel version
cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & excelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"""

Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn)
cn.Open()
da.Fill(yourDataSetName, tableName)
cn.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'TODO proccess data to add / update other tables
End Sub