Answered Read or get cell value (Excel)

[freddyboy]

Good evening, I am quite new to VB. I will try to describe as best as I can what I am trying to do.

I have a treeview called "TreeView_Insp_Records". It has several nodes and each nodes text has been renamed by inspections records names. For exemple ( Insp_blabla1, Insp_blabla2, and so on).
Theses inspections can all be found on my excel document shee1. I would like to be able to select the inspection node in the treeview, so that vb search the exact string match on the 1st column and for exemple return the value for column3 (barsalon) in a textbox.
Can I get a hand ?

Column1 (Insp Records)	Column2 (Insp Name)	Column3 ( Company)	Column4 (numbers)
Insp_blabla1	fred	barsalon	345-334-3333
Insp_blabla2 Insp_blabla3 and so on

 

[jmcilhinney]

You can get a hand but, as it stands, you appear to be asking us to do or show you how to do everything. What have you tried and where are you stuck? Have you researched how to use Excel Automation in VB.NET?

 

[freddyboy]

You can get a hand but, as it stands, you appear to be asking us to do or show you how to do everything. What have you tried and where are you stuck? Have you researched how to use Excel Automation in VB.NET?

Thank you for pointing me in the right direction. I got it to work, it's not all pretty but it works. The only issue I have now is that when I select a node in my treeview that has no match my (5) excel sheets, it lags a bit because it goes through all my for-next.

I'd like to add a code line in front of all my for-next to make sure they skip them if no match is found. Here is the code:

Private Sub TreeView_Insp_Records_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records.AfterSelect

Dim row As Long
        row = 1

        Dim Inspfred As Worksheet
        Dim Inspchris As Worksheet
        Dim Inspjr As Worksheet
        Dim Inspluc As Worksheet
        Dim Inspted As Worksheet

        Inspfred = Form1.xlWorkBook.Sheets("Inspfred")
        Inspchris = Form1.xlWorkBook.Sheets("Inspchris")
        Inspjr = Form1.xlWorkBook.Sheets("Inspjr")
        Inspluc = Form1.xlWorkBook.Sheets("Inspluc")
        Inspted = Form1.xlWorkBook.Sheets("Inspted")

        Dim rg1 As Range 'Range pour fred
        Dim rg2 As Range 'Range pour chris
        Dim rg3 As Range 'Range pour jr
        Dim rg4 As Range 'Range pour luc
        Dim rg5 As Range 'Range pour ted

        rg1 = Inspfred.Range("C1").CurrentRegion 'Loop for sheet Inspfred column C 
        rg2 = Inspchris.Range("C1").CurrentRegion 'Loop for sheet Inspchris column C
        rg3 = Inspjr.Range("C1").CurrentRegion 'Loop for sheet Inspjr column C
        rg4 = Inspluc.Range("C1").CurrentRegion 'Loop for sheet inspluc column C
        rg5 = Inspted.Range("C1").CurrentRegion 'Loop for sheet inspted column C

    Dim i As Long

        For i = 1 To rg1.Rows.Count 'Range for Inspfred

            If rg1.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' If Colonne C value = treeview selected node
                TextBox_RDIMS.Text = rg1.Cells(i, 19).value
                TextBox_Type.Text = rg1.Cells(i, 6).value
                TextBox_ABC.Text = rg1.Cells(i, 7).value
                TextBox_Railway.Text = rg1.Cells(i, 5).value
             'And a bunch of other textbox bla bla bla.
   row = row + 1
            End If
        Next

For i = 1 To rg2.Rows.Count 'Range pour Inspchris
            If rg2.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = rg2.Cells(i, 19).value
                TextBox_Type.Text = rg2.Cells(i, 6).value
                TextBox_ABC.Text = rg2.Cells(i, 7).value
                TextBox_Railway.Text = rg2.Cells(i, 5).value
  row = row + 1
            End If
  Next

For i = 1 To rg3.Rows.Count 'Range pour Inspjr
            If rg3.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = rg3.Cells(i, 19).value
                TextBox_Type.Text = rg3.Cells(i, 6).value
                TextBox_ABC.Text = rg3.Cells(i, 7).value
                TextBox_Railway.Text = rg3.Cells(i, 5).value
     'And a bunch of other textbox bla bla bla.
   row = row + 1
            End If
  Next

'And samething for rg4 & rg5

    End Sub

 

[jmcilhinney]

I'd like to add a code line in front of all my for-next to make sure they skip them if no match is found.

Maybe I'm missing something but isn't it in those loops that you find a match? If so, how could you not execute the loops?

Are you actually saying that, if you find a match in one loop, you want to looking any further? That would make more sense. It seems to me that you should have an Exit For inside each of those If blocks, so your don't keep looping if a match has already been found. You can also set a Boolean variable to False before the first loop and set it it to True inside each If block, then test that variable before each subsequent loop.

 

[freddyboy]

Thank you for being patient with me. As you can tell, I am realy new to this.
I have added the "Exit for" in each of my If block as you mentionned, it is making it a bit easier for the program as it stops it from looping when a match is found.
These loops are looking for match in the treeview for "Parent nodes". When a user select any of the Child nodes, I want to avoid going through any of the loops. Is there a simple way to tell vb not to try to find a match ?

Realy appreciate you're guidance!

 

[freddyboy]

Just a picture to give you an idea of the treeview and it's nodes. (and the child nodes)

 

[jmcilhinney]

If I'm understanding you correctly, you only want to act if a top-level node is selected. Each node has a Parent and that will be Nothing top-level nodes but set for others. That means that you can do something like this:

Dim selectedNode = TreeView_Insp_Records.SelectedNode

If selectedNode.Parent Is Nothing Then
    'A top-level node has been selected.'
    
    Dim selectedNodeText = selectedNode.Text
    
    'Put your loops here and use selectedNodeText.'
End If

 

[jmcilhinney]

Actually, I'd forgotten about this (don't use TreeView's much) but there's also a Level property, so you can just use that:

If selectedNode.Level = 0 Then

 

[freddyboy]

I tried both codes, they both works in terms that I don't get any errors.
But it still feels laggy when I move from one node to another.
(Feels like almost 3 secondes of looping).
Not sure what do to at this point.
Would it run faster if I change all my for-next with cases ?

 

[Sheepings]

Sounds like you might be best introducing tasks or threading operations.

If you are experiencing lag on your app. Consider using a backgroundworker to run long running executions of code, such as looping. Use your events as triggers and not as events to do long running code executions. Start new threads for long running code loops to execute on. You do this from within your events.

You can use a background worker and have it report back progress should a condition in your long running loop be met. This might help with some of that jittery lag you are experiencing.

 

[JohnH]

Getting values from a sheet range once as array is as fast as getting a single cell value, so if you get 20 values from a range speed can be improved 20 times... or so

Dim values As Object(,) = rg1.Value

Then you can get each one value from array:

Dim value = values(row, column)

 

[freddyboy]

Alright, sounds like I have a lot to read.
Tried the threads but ended up with errors.
Thanks. This forum is very handy, very well supported.

 

[Sheepings]

If you don't show what you changed, we can't help you fix the errors.

I'd guess being a beginner, you are getting the dreaded crosstread exception and not knowing how to handle it....

 

[freddyboy]

Imports System.Threading

Public Class RSIform

    Dim MonThread As New Thread(AddressOf Rangeloops)
    
    Private Sub TreeView_Insp_Records_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records.AfterSelect
      
       MonThread.Start()

    End Sub
    
     Private Sub Rangeloops()
        Clearalltextbox()   'Make sure all fields are empty while switching form child to child
        TextBox_selectednodes.Text = TreeView_Insp_Records.SelectedNode.Text
        Ping_network()

        Dim row As Long
        row = 1
        Dim Inspfred As Worksheet
        Dim Inspchris As Worksheet
        Dim Inspjr As Worksheet
        Dim Inspluc As Worksheet
        Dim Inspted As Worksheet
        Inspfred = Form1.xlWorkBook.Sheets("Inspfred")
        Inspchris = Form1.xlWorkBook.Sheets("Inspchris")
        Inspjr = Form1.xlWorkBook.Sheets("Inspjr")
        Inspluc = Form1.xlWorkBook.Sheets("Inspluc")
        Inspted = Form1.xlWorkBook.Sheets("Inspted")
        Dim rg1 As Range 'Range pour fred
        Dim rg2 As Range 'Range pour chris
        Dim rg3 As Range 'Range pour jr
        Dim rg4 As Range 'Range pour luc
        Dim rg5 As Range 'Range pour ted
        rg1 = Inspfred.Range("C1").CurrentRegion 'Loop les valeurs situées dans la colonne C pour Inspfred (dans la sheet Inspfred)
        rg2 = Inspchris.Range("C1").CurrentRegion 'Loop les valeurs situées dans la colonne C pour Inspchris (dans la sheet Inspchris)
        rg3 = Inspjr.Range("C1").CurrentRegion 'Loop les valeurs situées dans la colonne C pour Inspjr (dans la sheet Inspjr)
        rg4 = Inspluc.Range("C1").CurrentRegion 'Loop les valeurs situées dans la colonne C pour Inspluc (dans la sheet Inspluc)
        rg5 = Inspted.Range("C1").CurrentRegion 'Loop les valeurs situées dans la colonne C pour Inspted (dans la sheet Inspted)

        Dim i As Long

        For i = 1 To rg1.Rows.Count 'Range pour Inspfred
            If rg1.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = rg1.Cells(i, 19).value
                TextBox_RSIG.Text = rg1.Cells(i, 18).value
                TextBox_LocationNAME.Text = rg1.Cells(i, 4).value
                TextBox_Type.Text = rg1.Cells(i, 6).value
                TextBox_ABC.Text = rg1.Cells(i, 7).value
                TextBox_Railway.Text = rg1.Cells(i, 5).value
                TextBox_issue.Text = rg1.Cells(i, 8).value
                TextBox_InspFROM.Text = rg1.Cells(i, 9).value
                TextBox_InspTO.Text = rg1.Cells(i, 10).value
                TextBox_Total_Insp.Text = rg1.Cells(i, 11).value
                TextBox_Date.Text = rg1.Cells(i, 12).value
                TextBox_Year.Text = rg1.Cells(i, 2).value
                TextBox_Lead_RSI.Text = rg1.Cells(i, 15).value
                TextBox_2nd_RSI.Text = rg1.Cells(i, 16).value
                TextBox_Letterofconcern.Text = rg1.Cells(i, 21).value
                TextBox_Prenotice.Text = rg1.Cells(i, 23).value
                TextBox_notice_order.Text = rg1.Cells(i, 26).value
                TextBox_NAIAT.Text = rg1.Cells(i, 28).value
                TextBox_AMP.Text = rg1.Cells(i, 29).value
                TextBox_letterofwarning.Text = rg1.Cells(i, 31).value
                TextBox_Comments.Text = rg1.Cells(i, 32).value
                'RadioBtn_TAV.Checked = True
                'RadioBtn_LONE.Checked = False
                'RadioBtn_WALK.Checked = False
                row = row + 1
                Exit For
            End If
        Next
    
    'And all my other loops
    
          MonThread.Abort()

        End Select
   End Sub

Yep, and I get the same error for Clearalltextbox(), Ping_network(), and others.

 

[Sheepings]

    Public Delegate Sub Callback(ByVal s As String)

    Private Sub UpdateUI(ByVal this_Result As String)
        RichTextBox1.Text = this_Result
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim thread As New Thread(Sub() ExecuteWork())
        thread.Start()
    End Sub

    Private Sub ExecuteWork()
        'I am the working method, the method we called from UI thread.
        'I am not on the UI thread any more so i can't talk to it.
        Dim s As String = "Testing Worker Method"
        While True
            Debug.Write(s) 'This always runs and is running on non-ui thread
        End While
        'How to talk to your ui?
        RichTextBox1.Invoke(New Callback(AddressOf UpdateUI), s)
        'We call the control we MUST invoke, create a new delegate, send it the method to update from, and provide the value to that method.
    End Sub

The above is a working example. I've left it like this so you can adapt it yourself.
Adapt that threading template to your current code. When you start a thread, you are no longer on the same thread as that which created it, so you can't change the UI values without delegating.

In your case, my Button1_Click is your Private Sub TreeView_Insp_Records_AfterSelect event.
My ExecuteWork() method is your Private Sub Rangeloops().

In your case, you have a lot of controls you want to update. So I would make an adjustment to my template above.

Change :

    Private Sub UpdateUI(ByVal this_Result As String)
        RichTextBox1.Text = this_Result
    End Sub

To :

    Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)
        this_Control.Text = CType(vRange, String)
    End Sub

Change :

    Public Delegate Sub Callback(ByVal s As String)

Change the CallBack to :

    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)

And change :

    Private Sub ExecuteWork()
        'I am the working method, the method we called from UI thread.
        'I am not on the UI thread any more so i can't talk to it.
        Dim s As String = "Testing Worker Method"
        While True
            Debug.Write(s) 'This always runs and is running on non-ui thread
        End While
        'How to talk to your ui?
        RichTextBox1.Invoke(New Callback(AddressOf UpdateUI), s)
        'We call the control we MUST invoke, create a new delegate, send it the method to update from, and provide the value to that method.
    End Sub

To be more like the code you have :

    Private Sub ExecuteWork()
        'I am the working method, the method we called from UI thread.
        'I am not on the UI thread any more so i can't talk to it.
        Dim s As String = "Testing Worker Method"
        For i = 1 To rg1.Rows.Count 'Range pour Inspfred
            Debug.Write(s) 'This is running on non-ui thread
            'How to talk to your ui?
           TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, YourRangeValueHere) 'example rg1.Cells(i, 19).value and don't forget to cast if needed.
            'We call the control we MUST invoke, create a new delegate, send it the method to update from, and provide the control to that method, with the new value.
            'See on updating values from cells : https://stackoverflow.com/questions/23004274/vb-net-excel-worksheet-cells-value
        Next
    End Sub

Since you have a lot of controls, you will need to do that on each of them. The control you want to invoke, must be sent to the delegate method with the new value and your UI will update safely from the other thread.
Don't forget to call .Text on the control in the UpdateUI method, as you are passing in the control you want to update, and set its text value to the value you passed from the other thread : this_Control.Text = CType(vRange, String) and cast it since it is an object. I believe you can also send the whole cell if you want, since I think that's an object too, and then in the UpdateUI method, you can call .Value on vRange.

I wrote this freehand based off the template, so you may need to iron out some bugs. Post back if you get stuck, but that should fix your problem.