Results 1 to 4 of 4

Thread: Error when trying to convert BackgroundWorker to async/await using Interop.Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2017
    Posts
    2

    Error when trying to convert BackgroundWorker to async/await using Interop.Excel

    I was given some code recently and asked to optimize it due to its long running nature. There are 2 items I want to look at. First is their use of BackgroundWorker and the other is looking at a faster way of creating these Excel reports they have. They currently open templates in Excel, modify these templates and then move onto the next. I believe I should be able to gain some major speed improvements modifying this portion of logic as well.

    First off however, one of the initial problems I saw after analysis was this below block and the amount of time spent in here:

    BackgroundWorker3.RunWorkerAsync(xlsWorkBook)
    While BackgroundWorker3.IsBusy()
    Windows.Forms.Application.DoEvents()
    End While

    I haven't messed with VB.Net in ages, but further reading online verified my assumptions about the above code block. I initially thought about using async/await or Task.Run. However I have been running into numerous access issues that seem to be around either Interop.Excel.Workbook or Interop.Excel.Application not being set to an instance of an object. Since this seems to be happening outside of my changes, I've been finding it difficult to determine the exact cause of this error. I have seen some issues surrounding Interop.Excel handling not being able to be used asynchronously, but I find it hard to believe that there isn't another solution and that this just might be due to my disconnect from VB.Net for so many years.

    So if anyone has any advice or knowledge you can share on why I might be seeing the issues I am when trying to convert over to something other than background worker, I would love to hear any advice. I would also like to potentially move to using TPL if at all possible as there is no parallelization going on in this app at all.

    So from a simplistic standpoint, I have 2 excel sheets in a directory that I wish to open and perform an excel workbook refresh on both. These should be able to be performed in parallel using Task.Run, however even just using async await should be fine as the work being done is using a passed Excel.Workbook. My main goal here is to get away from the above while loop.

    I currently am getting an exception stating "Object reference not set to an instance of an object." in the RefreshSub Catch.

    Currently I have the work being done in this function:
    Code:
     Private Function RefreshExcel(ByRef xlWorkbook As Excel.Workbook) As Task
            Try
                xlWorkbook.RefreshAll()
                xlWorkbook.Save()
            Catch ex As Exception
                Console.Log("Error: " & ex.Message)
            Finally
    
            End Try
        End Function
    Async calling function is below:
    Code:
    Private Async Sub RefreshSub(refFile As String, xlsApp As Excel.Application, ByVal func As CpFilesDelegate)
    	Dim xlsWorkBook As Excel.Workbook = Nothing
    	Try
    		xlsWorkBook = xlsApp.Workbooks.Open(refFile)
    		Await RefreshExcel(xlsWorkBook)
    
    		'BackgroundWorker3.RunWorkerAsync(xlsWorkBook)
    
    		'While BackgroundWorker3.IsBusy()
    		'    Windows.Forms.Application.DoEvents()
    		'End While
    		
    		'Will add onComplete logic below
    		
    	Catch ex As Exception
    		Debug.Print("Error: " + ex.Message)
    	Finally
    
    	End Try
    End Sub
    Finally the call to RefreshSub occurs in the below block:
    Code:
    Private Sub RefAndCpRefFiles(ByRef xlsApp As Excel.Application)
    	Try
    		xlsApp.DisplayAlerts = False
    		Using conn As SqlConnection = Connect()
    			Dim refQuery As String = "SELECT COUNT(1) FROM JB_REF_FILES WHERE JB_ID = " & jbID
    			Dim sqlReader As SqlDataReader = Query(refQuery)
    			Dim refRefreshCnt As Integer = 0
    			If sqlReader.Read() Then
    				refRefreshCnt = sqlReader.GetInt32(0)
    				sqlReader.Close()
    
    				If refRefreshCnt = 0 Then
    					Console.WriteLine("Files refreshed already")
    					Return
    				Else
    					Dim upQry As String = "UPDATE JB_REF_FILES SET STATUS = 'True' WHERE JB_ID = " & jbID
    					Dim setCmd As New SqlCommand(upQry, conn)
    					setCmd.ExecuteNonQuery()
    					RefreshSub(REF_FILE1, xlsApp, AddressOf CpFiles)
    
    					RefreshSub(REF_FILE2, xlsApp, AddressOf CpFiles)
    				End If
    			End If
    		End Using
    	Catch ex As Exception
    		Console.WriteLine("Error: " & ex.Message)
    	End Try
    End Sub
    Does anyone see why I would be getting the error for the above logic? Is this not the proper approach to take? Any light or kick in the right direction would be extremely helpful. I've been tryign to figure this out off and on since last week and have yet to figure it out and thought it was time to reach out. I also tried the logic in RefreshExcel in a Task.Run loop but this again was an issue because of Excel workbook and app being used byRef I believe, however I thought going to async await would have resolved this. Using the original while loop and calling DoEvents in it does work, however it just seems hacky to me and I don't like the continuous call to IsBusy. Also if there are any recommended directions for working with Excel in VB.Net in parallel, I would love to hear them.

    Thanks!

  2. #2
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Error when trying to convert BackgroundWorker to async/await using Interop.Excel

    Before we can even get into what's causing your exception, that attempt to use asynchronous methods must be fixed. You used them wrong. Here is the simplest sample I could come up with that shows how they should be used:-
    vbnet Code:
    1. Public Class Form1
    2.  
    3.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    4.  
    5.     End Sub
    6.  
    7.  
    8.     'A method MUST be declared Async when using Await within it's body
    9.     'It must return a Task or Task(Of T) object if you want the method
    10.     'to be awaitable
    11.     Private Async Function LongMethod() As Task
    12.  
    13.         'Simulates a long running method.
    14.         'The Await will yield this method to the caller for
    15.         '500 milliseconds.
    16.         Await Task.Delay(2000)
    17.  
    18.     End Function
    19.  
    20.     Private Async Sub btnDoWork_Click(sender As Object, e As EventArgs) Handles btnDoWork.Click
    21.  
    22.         'Await here will yield to this button's click to the caller
    23.         'which in this case would the the application's message loop.
    24.         'This is why the UI would remain responsive. It's because the message
    25.         'loop will be running while Await waits for the LongMethod
    26.         'to complete.
    27.         Await LongMethod()
    28.  
    29.         'When LongMethod is complete, execution will continue from here
    30.         MessageBox.Show("Asynchronous work has been completed.")
    31.  
    32.     End Sub
    33. End Class

    Refactor your code to properly use them.

    Here's a more succinct example that may be more applicable to your needs:-
    vbnet Code:
    1. Imports System.Threading
    2.  
    3. Public Class Form1
    4.  
    5.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    6.  
    7.     End Sub
    8.  
    9.     'Pretend this is your Excel method
    10.     Private Sub PretendThisIsYourLongRunningExcelCall()
    11.  
    12.         'It takes 2 whole seconds to complete, potentially locking up
    13.         'the UI if we don't run it asynchronously
    14.         Thread.Sleep(2000)
    15.  
    16.     End Sub
    17.  
    18.     Private Async Sub btnDoExcel_Click(sender As Object, e As EventArgs) Handles btnDoExcel.Click
    19.  
    20.         'This will run your "Excel method" on another thread
    21.         'and Await will yield to the caller, the message loop in this case,
    22.         'while your Excel method runs. By yielding to the message loop,
    23.         'your app remains responsive.
    24.         Await Task.Run(Sub() PretendThisIsYourLongRunningExcelCall())
    25.  
    26.         MessageBox.Show("The work has been completed.")
    27.  
    28.     End Sub
    29. End Class
    Last edited by Niya; Dec 13th, 2017 at 05:23 PM.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  3. #3
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Error when trying to convert BackgroundWorker to async/await using Interop.Excel

    Don't COM objects have thread affinity? Isn't it dangerous to use them from multiple threads?

    Getting Async/Await right is extremely complex, there are many ways to shoot yourself in the foot and I don't like trying to implement it beneath the level of a client. I don't think it's going to make things faster if a BackgroundWorker solution is already slow.
    Last edited by Sitten Spynne; Dec 13th, 2017 at 05:43 PM.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  4. #4
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Error when trying to convert BackgroundWorker to async/await using Interop.Excel

    Quote Originally Posted by Sitten Spynne View Post
    Don't COM objects have thread affinity? Isn't it dangerous to use them from multiple threads?
    Funny, there was a time when I would have known a sure the answer to this question but since leaving VB6 many many years ago, my dealings with COM has been very minimal. I almost have to be reminded it even exists.

    That being said, I do remember something about the .Net CLR using proxy objects to communicate with COM objects which helps mitigate some of the threading issues, though I'm having a bit of trouble finding the documentation on this within the time I took to write this post. My instincts tell me that his threading code would play nicely with COM. However, I don't like not knowing hard facts.

    He could answer this easily by simply testing it himself. All he has to do is create an app to do something very simple, like opening an Excel worksheet, and writing a value to a cell. From there he could refactor it to create the Excel objects on one thread and call their methods on another to see what gremlins show up.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width