Answered Read or get cell value (Excel)

freddyboy

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
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_blabla1fredbarsalon345-334-3333
Insp_blabla2
Insp_blabla3
and so on

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,350
Location
Sydney, Australia
Programming Experience
10+
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

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
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:
VB.NET:
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
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,350
Location
Sydney, Australia
Programming Experience
10+
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

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
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

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
Just a picture to give you an idea of the treeview and it's nodes. (and the child nodes)
RSI_Space1.jpg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,350
Location
Sydney, Australia
Programming Experience
10+
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:
VB.NET:
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
 

freddyboy

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
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

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
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

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
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 :)
VB.NET:
Dim values As Object(,) = rg1.Value
Then you can get each one value from array:
VB.NET:
Dim value = values(row, column)
 

freddyboy

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
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

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
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

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
VB.NET:
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
1.jpg


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

Sheepings

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
VB.NET:
    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 :
VB.NET:
    Private Sub UpdateUI(ByVal this_Result As String)
        RichTextBox1.Text = this_Result
    End Sub
To :
VB.NET:
    Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)
        this_Control.Text = CType(vRange, String)
    End Sub
Change :
VB.NET:
    Public Delegate Sub Callback(ByVal s As String)
Change the CallBack to :
VB.NET:
    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)
And change :
VB.NET:
    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 :
VB.NET:
    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.
 

freddyboy

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
VB.NET:
Option Explicit On
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim MonThread As New Thread(AddressOf Rangeloops)

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

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

    End Sub
    
     Private Sub ExecuteWork()

        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        '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 rg.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, rg.Cells(i, 2).value) '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
    
    Public Shared xlApp As Excel.Application
    Public Shared Sheets As Excel.Worksheet

    Public Shared XlWorkInspfred As Excel.Worksheet

    Public Shared xlWorkBook As Excel.Workbook
    Dim appPath As String = My.Application.Info.DirectoryPath


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("D:\Users\PC\Desktop\test.xlsx")

    End Sub

    Private Sub TreeView_Insp_Records__AfterSelect(sender As System.Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records_AfterSelect.AfterSelect
        Dim thread As New Thread(Sub() ExecuteWork())
        thread.Start()
    End Sub

    Private Sub Rangeloops()

        Dim row As Long
        row = 1
        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        Dim i As Long
        For i = 1 To rg.Rows.Count

            If rg.Cells(i, 1).value = TreeView_Insp_Records_AfterSelect.SelectedNode.Text Then
                TextBox_RDIMS.Text = rg.Cells(i, 2).value

            End If
            row = row + 1
        Next
    End Sub
End Class


Ok, so I got this part working with no errors (I started a small project just to make sure I can get this working fine). Now the range value doesn't change when I select other nodes in the treeview when running it because I have to add this line: ( If rg.Cells(i, 1).value = TreeView_Insp_Records_AfterSelect.SelectedNode.Text Then ) in the UpdateUI sub and then in the ExecuteWork() sub. I've tried to look at some exemples on how to do it and all my attempts have failed. I'd rather not past anything I've tried simply because I feel it's getting quite complex for my level. If I can get the range working, I should be good to pass the rest of the text control in the UpdateUI and then the Executework() by myself. But that range part is blocking me.

One more thing, I was confuse with:

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

I changed this_Control.Text by Textbox_RDIMS.Text and didn't get any errors. So I figured it didn't matter… So I changed it back to this_Control.Text. I suppose it has to stay like this ?
 

Sheepings

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
You're getting there slowly. Lets deal with one issue at a time. For the AfterSelect not doing anything. well it should have been evident to add parameters to your method that's executed by your thread, and to do that, you need to specify your parameters in your thread creation for the methods parameter also... you do that like this :

Change :
VB.NET:
        Dim thread As New Thread(Sub() ExecuteWork())
        thread.Start()
Change to :
VB.NET:
        Dim thread As New Thread(Sub() ExecuteWork(TreeView_Insp_Records.SelectedNode.Text))
        thread.Start()
Change :
VB.NET:
     Private Sub ExecuteWork()
Change to :
VB.NET:
    Private Sub ExecuteWork(ByVal value As String)

Then in your ExecuteWork method :
VB.NET:
If rg.Cells(i, 1).value = value Then
'Do whatever
value Then is referring to the : ExecuteWork(ByVal value As String) being value in the parameter.
 

Sheepings

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
I changed this_Control.Text by Textbox_RDIMS.Text and didn't get any errors. So I figured it didn't matter…
It does matter. Don't change the code I gave you. That's why I told you to send the whole control from your loop : For i = 1 To rg1.Rows.Count and the method will handle which control gets updated.
 

Sheepings

Senior Programmer
Staff member
Joined
Mar 7, 2014
Messages
132
Location
UK
Programming Experience
10+
Given you had in your original code :
VB.NET:
                TextBox_RDIMS.Text = rg2.Cells(i, 19).value
                TextBox_Type.Text = rg2.Cells(i, 6).value
These would become :
VB.NET:
TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg2.Cells(i, 19).value)
TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg2.Cells(i, 6).value)
And so on for each textbox in your loop...
 

freddyboy

Active member
Joined
Jun 18, 2020
Messages
27
Programming Experience
Beginner
VB.NET:
Option Explicit On
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim MonThread As New Thread(AddressOf Rangeloops)

    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)
    
     Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)

        this_Control.Text = CType(vRange, String)

    End Sub
    Private Sub ExecuteWork(ByVal value As String)

        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        '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 rg.Rows.Count 'Range pour Inspfred
            Debug.Write(s) 'This is running on non-ui thread
            'How to talk to your ui?
            If rg.Cells(i, 1).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg.Cells(i, 2).value) '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
            End If
        Next
    End Sub
    
    Public Shared xlApp As Excel.Application
    Public Shared Sheets As Excel.Worksheet

    Public Shared XlWorkInspfred As Excel.Worksheet

    Public Shared xlWorkBook As Excel.Workbook
    Dim appPath As String = My.Application.Info.DirectoryPath


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("D:\Users\PC\Desktop\test.xlsx")

    End Sub

    Private Sub TreeView_Insp_Records_AfterSelect(sender As System.Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records.AfterSelect
        Dim thread As New Thread(Sub() ExecuteWork(TreeView_Insp_Records.SelectedNode.Text))
        thread.Start()
    End Sub

    Private Sub Rangeloops()

        Dim row As Long
        row = 1
        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        Dim i As Long
        For i = 1 To rg.Rows.Count

            If rg.Cells(i, 1).value = TreeView_Insp_Records.SelectedNode.Text Then
                TextBox_RDIMS.Text = rg.Cells(i, 2).value

            End If
            row = row + 1
        Next
    End Sub
End Class


Line 55 gives me the error attached. (1.PNG)
 

Attachments

  • 1.PNG
    1.PNG
    45.2 KB · Views: 8
Top Bottom