Private Function CreateDataSource() As DataSet
Dim StrFile As String
StrFile = Me.TexBoxExcelDir.Text
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + StrFile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim conn As OleDbConnection = New OleDbConnection(strConn)
Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Dim myDataSet As DataSet = New DataSet
myCommand.Fill(myDataSet)
Return myDataSet
End Function
Private Sub DataGridSet()
Dim myDataGrid As New DataGrid
myDataGrid = ME.DataGrid1
myDataGrid.SetDataBinding(CreateDataSource(), ExcelData.Tables("Sheet1$").ToString)
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.