Excel Reading

[brix_zx2]

I'm working on a piece of my project that will take the records from a spreadsheet (anywhere from 10 to 200) with only 6 fields and bring them in to the program and then copy them to the Database. Help or a different suggestion would help.

(VB .NET 2k3 Access 2000 and Excel 2000). I'm not sure if I'm doing this right but here it is.


PrivateSub cmdImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdImport.Click

Dim intRow AsInteger
Dim intCol AsInteger
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myDataReader As System.Data.OleDb.OleDbDataReader
Dim ExcelCommand AsNew System.Data.OleDb.OleDbCommand

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Program Files\97CS\SelfInspect\CheckList\NewCheckList.XLS; " & _
"Extended Properties=Excel 8.0;")

MyConnection.Open()

Dim commandString AsString
commandString = "SELECT * FROM [Sheet1$]"
ExcelCommand.Connection = MyConnection
ExcelCommand.CommandText = commandString

OnErrorGoTo HandleErr

myDataReader = ExcelCommand.ExecuteReader
Dim myTable AsNew System.Data.DataTable
myTable.Columns.Add("dbmFilterList")
myTable.Columns.Add("dbmCheckList")
myTable.Columns.Add("dbmNumber")
myTable.Columns.Add("dbmQuestion")
myTable.Columns.Add("dbmReference")
Dim myRow As DataRow = myTable.NewRow
intRow = 1

DoWhile myDataReader.Read
For intCol = 1 To myDataReader.FieldCount
myRow.Item("dbmFilterList") = myDataReader(0)
myRow.Item("dbmCheckList") = myDataReader(1)
myRow.Item("dbmNumber") = myDataReader(2)
myRow.Item("dbmQuestion") = myDataReader(3)
myRow.Item("dbmReference") = myDataReader(4)
myRow = myTable.NewRow
DataGrid1.Refresh()
Next intCol
myDataReader.NextResult()
intRow = intRow + 1
Loop

MyConnection.Close()
MyConnection = Nothing

ExitHere:
ExitSub

HandleErr:

MsgBox("Error " & Err.Number & ": " & Err.Description, _
vbOKOnly, "GetDataLoop")

Resume ExitHere
Resume

EndSub