Update Textbox after each iteration VB.Net WPF

Question

I have a For loop which runs through xlsx files in a directory, I need to append the filenames in a TextBlock after each loop and refresh the TextBlock to show the updated text.

The code I have below only displays the filenames after the loop has executed.

 Dim lcFileName As String = ""
 Dim fileArray() As String = Directory.GetFiles(txtDirectory.Text, "*.xlsx", SearchOption.AllDirectories)

    For Each file As String In fileArray

        Dim ExcelApp As Excel.Application = New Excel.Application
        Dim Workbook As Excel.Workbook = ExcelApp.Workbooks.Open(file)
        Dim Worksheet As Excel.Worksheet = Workbook.Sheets(1)
        Dim Range As Excel.Range = Worksheet.UsedRange

        Dim rowCount As Integer = Range.Rows.Count
        Dim colCount As Integer = Range.Columns.Count

        Dim tmpOrder(rowCount, colCount) As String
        tbResults.Text = tbResults.Text + Environment.NewLine + Path.GetFileName(file) + " imported."

        For i = 1 To rowCount
            For j = 1 To colCount
                'New line
                If (i = 1 And j = 1) Then
                    tmpOrder(i - 1, j - 1) = Range.Cells(i, j).Value
                    lcFileName = tmpOrder(i - 1, j - 1).ToString()

                Else

                    If (Not String.IsNullOrEmpty(Range.Cells(i, j).Value)) Then
                        tmpOrder(i - 1, j - 1) = Range.Cells(i, j).Value.ToString()
                    End If
                End If
            Next
        Next

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Worksheet)
        Worksheet = Nothing
        ExcelApp.ActiveWorkbook.Close(True)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Workbook)
        Workbook = Nothing
        ExcelApp.Quit()
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelApp)
        ExcelApp = Nothing
        '
    Next

Any help would be appreciated, VB.Net required.

You need to do it in an asynchronous pattern, because you won't be able to refresh UI while computing. — scharette, Jun 12 '18 at 17:38
@scharette could you please provide an example if its not too much hassle? thanks — Rhysf93, Jun 12 '18 at 17:40
@scharette so after a quick look i used async, however on executing i get the error "'The calling thread cannot access this object because a different thread owns it." any suggestions? thankyou — Rhysf93, Jun 12 '18 at 18:09
Take a look at [this](https://stackoverflow.com/a/661662/7692463) — scharette, Jun 12 '18 at 18:11
Incidentally, you may be able to speed it up somewhat by using just one instance of Excel instead of repeatedly making a New one and Quitting it. — Andrew Morton, Jun 12 '18 at 18:13
@scharette I am using the dispatcher.invoke which is now running fine but im still getting the issue where the textbox is only updated at the end of the loop. my code is as follows Await Dispatcher.BeginInvoke(New Action(AddressOf Test)) Private Sub Test() tbResults.Text = tbResults.Text + Environment.NewLine + Path.GetFileName("Test") + " imported." End Sub — Rhysf93, Jun 12 '18 at 18:30

score 0 · Accepted Answer · answered Jun 12 '18 at 19:44

Finally got it working. Ok first I created my Method to update the TextBlock with the parameter passed for the filename.

Public Sub UpdateResults(ByVal lcFile As String)
    tbResults.Text = tbResults.Text + Environment.NewLine + Path.GetFileName(lcFile) + " imported."
End Sub

In my For Loop i called the method with the following code

Application.Current.Dispatcher.Invoke(DispatcherPriority.Background, New ThreadStart(Sub() Me.UpdateResults(lcFile)))

Where UpdateResults(lcFile) is the method and parameter passed.

If you are not passing any parameters then call your method using this, where 'MyMethod' is the name of the method you want to run.

Application.Current.Dispatcher.Invoke(DispatcherPriority.Background, New ThreadStart(AddressOf MyMethod))

No need to edit the title of the question to say it is solved, just click accept on the appropriate answer (presumably the one you posted). — Blackwood, Jun 12 '18 at 23:51

score -1 · Answer 2 · answered Jun 12 '18 at 17:39

-1

Something isn't playing nicely and is preventing the form from updating.

You can allow the app to process pending display operations by adding Application.DoEvents() inside one of the loops.

There is some overhead involved, so you probably want it in the outer loop, not the inner loop.

answered Jun 12 '18 at 17:39

Terry Carmen

3,720
1
16
32

I have tried Application.DoEvents() which worked but very poorly, it updated the textblock twice then skipped a few rather than one after the other. any other suggestions? thankyou – Rhysf93 Jun 12 '18 at 17:47
It's not guaranteed to show every single change, it's just there to let the UI "catch up". It's about the best you're going to do unless you want to run the program logic and screen update on separate threads. – Terry Carmen Jun 12 '18 at 17:56
@TerryCarmen Please see the answers to [Use of Application.DoEvents()](https://stackoverflow.com/q/5181777/1115360). – Andrew Morton Jun 12 '18 at 17:59
Why? I'm not having a problem. – Terry Carmen Jun 13 '18 at 12:29

Update Textbox after each iteration VB.Net WPF

2 Answers2