I'm using this function to bind a named range in an excel spreadsheet to a datagrid:
VB Code:
Public Function GetDataFromExcel(ByVal FileName As String, _
ByVal RangeName As String) As System.Data.DataSet
' Returns a DataSet containing information from
' a named range in the passed Excel worksheet
Try
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
objConn.Open()
' Create objects ready to grab data
Dim objCmd As New System.Data.OleDb.OleDbCommand( _
"SELECT * FROM " & RangeName, objConn)
Dim objDA As New System.Data.OleDb.OleDbDataAdapter()
objDA.SelectCommand = objCmd
' Fill DataSet
Dim objDS As New System.Data.DataSet()
objDA.Fill(objDS)
' Cleanup and return DataSet
objConn.Close()
Return objDS
Catch
' Possible errors include Excel file
' already open and locked, et al.
Return Nothing
End Try
End Function
I call the function like this:
VB Code:
DataGrid1.DataSource = GetDataFromExcel(Application.StartupPath & _
"\MyTestWorkbook.xls", "SampleNamedRange").Tables(0)
Everything works fine except for one problem. I have a column in the spreadsheet that has a value for time (e.g. 11:07:00 AM). The problem is that when i bind the data to the data grid, all of the values in this column are converted to "12/30/1899"...I tried changing the cell format to general, text and time but I cant seem to get it working. Any ideas are greatly appreciated!