Click here to Skip to main content
15,861,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to build an application that searches a string in a specific column of an excel file and if the string is found, display the corresponding column value For Ex: Say I am searching for a string in an excel file column "N", If the string I am searching for is found then display the "E"th Column value of the same row.The thing is that my code is displaying a single value multiple times in multiple labels of second form.

What I have tried:

FORM1:

Imports Excel = Microsoft.Office.Interop.Excel
 Imports Microsoft.Office.Interop.Excel
 Imports System.Globalization
 Imports System.Runtime.InteropServices

 Public Class Form1
    Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim Obj As Object
        Dim pass As String

        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim sr As New System.IO.StreamReader(OpenFileDialog1.FileName)
            MessageBox.Show("You have selected" + OpenFileDialog1.FileName)
            'sr.Close()
        End If

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(OpenFileDialog1.FileName)
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")

        range = xlWorkSheet.UsedRange
        For rCnt = 1 To range.Rows.Count
            For cCnt = 14 To range.Columns.Count
             If xlWorkSheet.Cells(rCnt, cCnt).value = "3" Or  xlWorkSheet.Cells(rCnt, cCnt).value = "4" Or xlWorkSheet.Cells(rCnt, cCnt).value = "5" Or xlWorkSheet.Cells(rCnt, cCnt).value = "6" Or xlWorkSheet.Cells(rCnt, cCnt).value = "7" Or xlWorkSheet.Cells(rCnt, cCnt).value = "8" Or xlWorkSheet.Cells(rCnt, cCnt).value = "9" Or xlWorkSheet.Cells(rCnt, cCnt).value = "10" Then

                    Obj = CType(range.Cells(rCnt, "E"), Excel.Range)
                    'MessageBox.Show(Obj.value)

                    Foo = Obj.value
                    Form2.Show()
                End If
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class  



FORM2:

Imports System.Linq
Imports System.Drawing

Public Class Form2
    Private Sub Panel1_Paint(sender As Object, e As PaintEventArgs) Handles Panel1.Paint
        Dim label As New Label()
        Dim count As Integer = Panel1.Controls.OfType(Of Label)().ToList().Count

        label.Location = New Point(10, (25 * count))
        'label.Size = New Size(40, 20)
        label.Name = "label_" & (count + 1)
        label.Text = Foo '& (count + 1)
        label.AutoSize = True
        Panel1.Controls.Add(label)

        Dim button As New Button()
        button.Location = New System.Drawing.Point(250, 25 * count)
        button.Size = New System.Drawing.Size(60, 20)
        button.Name = "Print" & (count + 1)
        button.Text = "Print" '& (count + 1)
        AddHandler button.Click, AddressOf Button_Click
        Panel1.Controls.Add(button)
        MessageBox.Show(Foo)
    End Sub

    Private Sub Button_Click(sender As Object, e As EventArgs)
        Dim button As Button = TryCast(sender, Button)
        MessageBox.Show(button.Name + " clicked")
    End Sub
End Class



MODULE:

Module Module1

    Public Foo As String

End Module
Posted
Updated 10-Mar-17 1:45am
v2
Comments
Ralf Meier 6-Mar-17 3:58am    
Have you thought about using a DataGridView (which nearly looks like an Excel-Sheet) to display your data ?
Member 12712519 6-Mar-17 4:18am    
No sir since I am a rookie in visual basic. Can you please help me out?
Ralf Meier 6-Mar-17 6:07am    
Yes ... of course.
At first take the DataGridView-Control and place it on your Form.
Now add Columns as required for your Project - arrange them as required for you.
Now you can insert your data with adding rows.

I suggest you do these steps at first and when you got a new Point where you stuck you give me a Feedback (or ask a new question)
CHill60 10-Mar-17 7:56am    
By the way - be careful how you tag your questions. I picked this up because I regularly check for posts on VB6. This isn't VB6.

1 solution

You have a couple of issues with your code.
Firstly you are attempting to show Form2 within the loop and secondly you are showing it in a modeless state. In other words processing of the loop continues after the Form2.Show line.

@Ralf_Meier has given an excellent suggestion of using a DataGridView[^]. It simplifies all of the code for working out the size of the controls, the positions, adding them to a collection.

Here is a working example. I've made some other adjustments to the code as well so watch out for the comments explaining why.
VB
Public Class Form1
    Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As Application
        Dim xlWorkBook As Workbook
        Dim xlWorkSheet As Worksheet
        Dim range As Range

        'You can make the OpenFileDialog look and behave nicely for the user
        OpenFileDialog1.Filter = "Excel files (*.xls*)|*.xls*|All files (*.*)|*.*"
        OpenFileDialog1.FileName = ""
        If OpenFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        Dim sr As New IO.StreamReader(OpenFileDialog1.FileName)
        MessageBox.Show("You have selected" + OpenFileDialog1.FileName)

        'Get all of the results before attempting to show the form
        Dim listOfResults = New List(Of String)()

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(OpenFileDialog1.FileName)
        xlWorkSheet = xlWorkBook.Worksheets(1) 'It's safer to access "the first worksheet" in case a user has renamed it.

        range = xlWorkSheet.UsedRange
        For rCnt = 1 To range.Rows.Count
            For cCnt = 14 To range.Columns.Count
                Dim tst As String = xlWorkSheet.Cells(rCnt, cCnt).Value.ToString()

                'This is a neat way of saying "is my value in a list of possible values"
                If (New String() {"4", "5", "6", "7", "8", "9", "10"}).Contains(tst) Then
                    Dim foo As String = range.Cells(rCnt, "E").Value.ToString()
                    listOfResults.Add(foo)
                End If
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        'Only show Form2 after we have everything we're looking for
        If listOfResults.Count > 0 Then
            Form2.PopulateForm2(listOfResults)
        End If
        Form2.ShowDialog()

    End Sub
And Form2...
VB.NET
Public Class Form2
    'Note this is a public method on this class
    Public Sub PopulateForm2(results As List(Of String))

        'This could be done in the designer
        DataGridView1.Columns.Clear()
        DataGridView1.Columns.Add(New DataGridViewColumn())
        DataGridView1.Columns.Add(New DataGridViewButtonColumn())
        DataGridView1.AllowUserToAddRows = False

        DataGridView1.Rows.Clear()
        For count As Integer = 0 To results.Count - 1

            Dim dr As DataGridViewRow = New DataGridViewRow()
            Dim dc As DataGridViewCell = New DataGridViewTextBoxCell()
            dc.Value = results(count)
            dr.Cells.Add(dc)

            Dim button As DataGridViewButtonCell = New DataGridViewButtonCell()
            button.Value = "Print" '& (count + 1)
            dr.Cells.Add(button)

            DataGridView1.Rows.Add(dr)
        Next

    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

        If e.ColumnIndex = 1 Then 'i.e. the button
            Dim dgv = CType(sender, DataGridView)
            MessageBox.Show(String.Format("button {0} clicked. Associated Text is {1}", e.RowIndex.ToString(), dgv.Rows(e.RowIndex).Cells(0).Value))
        End If
    End Sub
End Class

Notice I haven't had to wire up a button click event - I've used the CellContentClick event for the DataGridView and just checked that the User has clicked on my Button cell (ColumnIndex = 1)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900