Victor Charles
asked on
Help with loading data from excel file by workbook's name
Hi,
How do I modify the code below to load my DataGrids with multiple workbooks from an excel file by using the name of the workbook, when attempting to use the current approach the workbook does not match ds.Tables(x). For example the 5th workbook does not match ds.Tables(5)
How do I modify the code below to load my DataGrids with multiple workbooks from an excel file by using the name of the workbook, when attempting to use the current approach the workbook does not match ds.Tables(x). For example the 5th workbook does not match ds.Tables(5)
Try
' InitializeComponent()
'MsgBox(username.Text)
' Dim filename As String = "\Data\MasterFiles\combineSheets.xls"
Dim filename As String = Application.StartupPath & "\Data\MasterFilesExcel\MasterFiles" & username.Text & ".xls"
'Dim filename As String = Application.StartupPath & "\Data\LinkFiles\aop29combinedaa.xls"
Dim myConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " & "data source='" & filename & "';" & "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"" ")
myConnection.Open()
Dim mySheets As DataTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
' Dim ds As New DataSet()
'Dim dt As DataTable
For i As Integer = 0 To mySheets.Rows.Count - 1
dt = makeDataTableFromSheetName(filename, mySheets.Rows(i)("TABLE_NAME").ToString())
ds.Tables.Add(dt)
Next
Master.Visible = True
Master.C1TrueDBGrid1.DataSource = ds.Tables(1)
Master.C1TrueDBGrid1.Splits(0).DisplayColumns(0).AutoSize()
Master.C1TrueDBGrid1.Splits(0).DisplayColumns(1).AutoSize()
Master.C1TrueDBGrid1.Splits(0).DisplayColumns(2).Visible = False
'****************************************************************************
Master.C1TrueDBGrid2.DataSource = ds.Tables(13)
Master.C1TrueDBGrid2.Splits(0).HighLightRowStyle.BackColor = Drawing.Color.Blue
Master.C1TrueDBGrid2.Splits(0).HighLightRowStyle.ForeColor = Drawing.Color.White
'For Each row As DataRow In dtsetcol13.Tables(0).Rows
'Master.C1TrueDBGrid2.Columns("C_ID").ValueItems.Values.Add(New C1.Win.C1TrueDBGrid.ValueItem(row("C_ID"), row("C_ID")))
'Next
' Master.C1TrueDBGrid2.Columns("C_ID").ValueItems.Validate = True
'Master.C1TrueDBGrid2.Columns("C_ID").ValueItems.Presentation = C1.Win.C1TrueDBGrid.PresentationEnum.SortedComboBox
' Master.C1TrueDBGrid2.Columns(0).Caption = "W_ID"
'Master.C1TrueDBGrid2.Splits(0).DisplayColumns(0).AutoSize()
Master.C1TrueDBGrid2.Splits(0).DisplayColumns(0).AutoSize()
Master.C1TrueDBGrid2.Splits(0).DisplayColumns(1).AutoSize()
Master.C1TrueDBGrid2.Splits(0).DisplayColumns(2).Visible = False
'****************************************************************************
'Exit Sub
Master.C1TrueDBGrid3.DataSource = ds.Tables(10)
Master.C1TrueDBGrid4.DataSource = ds.Tables(12)
Master.C1TrueDBGrid5.DataSource = Nothing
Master.C1TrueDBGrid7.DataSource = ds.Tables(11)
Master.C1TrueDBGrid8.DataSource = ds.Tables(6)
Master.C1TrueDBGrid9.DataSource = ds.Tables(9)
Master.C1TrueDBGrid10.DataSource = Nothing
Master.C1TrueDBGrid11.DataSource = Nothing
Master.C1TrueDBGrid6.DataSource = ds.Tables(0)
Master.C1TrueDBGrid13.DataSource = ds.Tables(10)
Master.C1TrueDBGrid14.DataSource = ds.Tables(6)
Master.C1TrueDBGrid15.DataSource = ds.Tables(4)
Master.C1TrueDBGrid16.DataSource = ds.Tables(5)
Master.C1TrueDBGrid17.DataSource = ds.Tables(9)
Master.C1TrueDBGrid18.DataSource = ds.Tables(13)
Master.C1TrueDBGrid19.DataSource = ds.Tables(14)
Master.C1TrueDBGrid20.DataSource = Nothing
Master.C1TrueDBGrid21.DataSource = Nothing
Catch
MsgBox(Err.Description)
End Try
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You!
Since the collection is 0-based, the 5th worksheet would be ds.Tables(4).