How to get data from DataGridView to a Listbox? 1

[Rick_Stanich]

Not wanting to type a long-drawn-out subject, I am new to VB.net, and it's been many many years since I was into VB6.
On Form1 I have a Combobox and a Listbox, Form2 is my DataGridView.
With help from others on forums, I have been able to populate the Combobox with a single column of data from the DataGridView.

https://www.vbforums.com/showthread.php?901690-Populate-a-Combobox-from-an-Excel-workbook-column

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Fill the ComboBox with Distinct Product names from Sheet
        ComboBox1.DataSource = ExcelOleDb("V:\CMM Data Files\Micura CMM PCM Programs (252)\Inline Blocks\Parameters.xls", "SELECT Distinct Part FROM [Dimensions$A:BZ]")
        ComboBox1.DisplayMember = "Part"
        ListBox2.Items.Clear()
        'Form2.Show() 'for testing

    End Sub

I am now trying to understand how to populate a Listbox with a Row of data from the DataGridView based on a selection from the Combobox.
The data going into the Listbox has to be in a single column. I have spent days reading and trying to accomplish this, but I think I am over my head this time.
Are there any tutorials/examples that discuss getting and placing data as I have described?
Not sure if not showing the DataGridView is normal or a hindrance, I prefer it to be hidden (Shown for example only)

All assistance is appreciated.

Rick Stanich
CMM Programming and Consulting

 

[Rick_Stanich]

Thank you, I am looking into these methods.

Rick Stanich
CMM Programming and Consulting

 

[Rick_Stanich]

Using method 1 of your two solutions.

"Select * From [Sheet1$] where [Part] is null or [Part] = '" & ComboBox1.Text & "' order by [Part]")

I get odd results.
See images Capture1.PNG and Capture2.PNG
Its adding a lot of "=" signs.

Also attaching my Excel workbook. (DataGridView is not retrieving all the cell values from Row2, so many of Row2 variables are missing?).

Also adding a copy of my excel file (Reduced list to ten Part numbers).

Edit:
The Code used above is not repeating the same results.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

If you want to 'convert' / treat your Excel as a source of your Table, there are some guidlines that need to be fallow.

Your field names (headers) cannot start with a Number, that creates problems. Also, no special characters, like a hard-return, equal sign, etc. (Column AQ and BG), and Column BH has no header.

That would be a good start to fix these problems.

I would create some simple Excel file following the rules above and code against that first.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

I did not know this; I am working with legacy Excel files.
I do know this has been done before, I have an EXE file that is doing what I am attempting to do, I just need to update things for new versions of OS and the CMM software. Unfortunately, the source code is unknown.

I will create a new Excel file as you described for testing.
Again, Thank you.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

If your goal is to use Excel file(s) and display their context in an application other than Excel, you do not necessary need to 'convert' your Excel into a DataTable.
To populate the combo with the Part, you may just get that from the Excel itself.
For any selected part, you can get all other columns from Excel, too. Without converting that into a DataTable.

More ways to 'skin the cat'. . .

I will have some simple, not very efficient code tomorrow...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

If your goal is to use Excel file(s) and display their context in an application other than Excel

That was my initial goal, but I was led to using DataGridView instead.
In VBA, its easy.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

What I did is - start a new VB.NET Project (I have VB.NET 2015)
On the Form I've placed a combo box named [tt]cboPart[/tt] (I did change its DropDownStyle to DropDownList) and a list box named [tt]lstDetail[/tt]
I added a Reference (Project - Add Reference...) to Microsoft Excel ##.# Object Library

and this code:
(you need to provide [red]your path[/red] to your Excel file)

Imports System.ComponentModel
Imports Microsoft.Office.Interop

Public Class Form1
    Dim xlApp As New Excel.Application
    Dim strExcelFile As String = [red]"E:\Andrzej\VBNET\ReadExcelFile\Parameters.xls"[/red]

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim intR As Integer

        With xlApp
            '.Visible = True
            .Workbooks.Open(strExcelFile)
            With .Sheets(1)
                intR = 3
                Do While .cells(intR, 1).value <> ""
                    cboPart.Items.Add(.cells(intR, 1).value)
                    intR += 1
                Loop
            End With
        End With
        With cboPart
            If .Items.Count > 0 Then
                .SelectedIndex = 0
            End If
        End With
    End Sub

    Private Sub cboPart_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboPart.SelectedIndexChanged
        Dim intR As Integer
        Dim intC As Integer

        lstDetail.Items.Clear()

        With xlApp
            With .Sheets(1)
                intR = 3
                intC = 1

                Do While CStr(.cells(intR, intC).value) <> ""
                    If CStr(.cells(intR, 1).value) = cboPart.Text Then
                        intC = 2
                        Do While CStr(.cells(intR, intC).value) <> ""
                            lstDetail.Items.Add(CStr(.cells(2, intC).value) & " -- " & CStr(.cells(intR, intC).value))
                            intC += 1
                        Loop

                    End If
                    intR += 1
                Loop

            End With
        End With
    End Sub

    Private Sub Form1_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
        xlApp.Quit()
    End Sub
End Class

This is what I have when I run it:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

I am getting an error at:

Do While .cells(intR, 1).value <> ""

Format error, it displayed "" (nothing displayed).

System.InvalidCastException: 'Conversion from string "" to type 'Double' is not valid.'
Inner Exception
FormatException: Input string was not in a correct format.

I changed the double quotes with "-1", this now shows the first part number but again an error.

System.InvalidCastException: 'Conversion from string "00-294528" to type 'Double' is not valid.'
Inner Exception
FormatException: Input string was not in a correct format.

I am using VS 2022.

Is it too much to ask to see your project so I can open it?

Rick Stanich
CMM Programming and Consulting

 

[Rick_Stanich]

This appears to have solved the error.

Do While .cells(intR, 1).value <> Nothing

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

You may also try:

Do While [highlight #FCE94F]CStr([/highlight].cells(intR, 1).value[highlight #FCE94F])[/highlight] <> ""

But, did you get it going and got what you hoped

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

I did get the code going and I not only got what I hoped, but it seems so much simplified now.

Thank you again!

Rick Stanich
CMM Programming and Consulting

 

[Rick_Stanich]

Do we have a message system on this forum?
I'd like to send you a small gift card, just to say thank you.

Rick Stanich
CMM Programming and Consulting, LLC

 

[Andrzejek]

Great!

Another way would be to read Excel file once, copy all data into 2D (String) array, close Excel, and do all display from the 2D array. But that's for another day. . .

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

No 'message system' on this forum (whatever that is...).
All what you can do is give a little purple star, which you did.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson