GameBuilder GameBuilder - 6 months ago 33
Vb.net Question

Create and Read table in Excel vbUsing VB

I have Created Excel Workbook 2010 Project in Visual Studio 2010.

enter image description here

I have Added a ribbon and Added a Button on it.

I want to Read the Value from the Excel file when I click on the Button.

Private Sub Button1_Click(sender As System.Object, e As
Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click


Also is it possible that on the Load of Excel Sheet, to create a Table?

If yes I want to read that table after Clicking on Button.

Achieved to create table in Excel, please advice , if it is good practice or not.

Private Sub Feuil1_Startup() Handles Me.Startup

With Me
.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$C$2:$T$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"
.Range("C2").Value = "ZRouteName"
.Range("D2").Value = "MondayIndicator"


On Load of Excelsheet Table is Created

Private Sub Feuil1_Startup() Handles Me.Startup
' Dim nr As Microsoft.Office.Tools.Excel.NamedRange = _
'Me.Controls.AddNamedRange(Me.Range("A2"), "NamedRange1")
'nr.Value2 = "This text was added by using code"
With Me
.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$C$2:$T$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"
.Range("C2").Value = "ZRouteName"
.Range("D2").Value = "MondayIndicator"
.Range("E2").Value = "TuesdayIndicator"
.Range("F2").Value = "WednesdayIndicator"
.Range("G2").Value = "ThursdayIndicator"
.Range("H2").Value = "FridayIndicator"
.Range("I2").Value = "SaturdayIndicator"
.Range("J2").Value = "SundayIndicator"
.Range("K2").Value = "StartDate"
.Range("L2").Value = "Zorganiser"
.Range("M2").Value = "DayNumber"
.Range("N2").Value = "ZEmployeeResponsible"
.Range("O2").Value = "VisitDate"
.Range("P2").Value = "StartTime"
.Range("Q2").Value = "DriveTime"
.Range("R2").Value = "PreparationTime"
.Range("S2").Value = "Duration"
.Range("T2").Value = "ZAccountID"
End With
End Sub


Now I want to read the values form the Table 1 OnClick Event of Button

Private Sub Button1_Click(sender As System.Object, e As
Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click

Answer

This is an example of accessing your sheet from the ribbon class.

Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
    MessageBox.Show(Globals.Feui11.Cells(1, 1).Value)
End Sub

Or this using Range

Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
        MessageBox.Show(Globals.Feui11.Range("A1").Value)
End Sub

This outer loop iterates through the rows in UsedRange and for each row we iterate through the columns. You may not need the column loop if you know which columns you need to look in for your data.

For row As Integer = 1 To Globals.Feui11.UsedRange.Rows.Count Step 1
            For column As Integer = 1 To Globals.Feui11.UsedRange.Columns.Count Step 1
                'Do stuff with this
                'Globals.Feui11.Cells(row, colunm).Value
            Next
        Next