-
Dec 12th, 2017, 01:57 PM
#1
Thread Starter
New Member
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!
-
Dec 13th, 2017, 05:05 PM
#2
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:
Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load End Sub 'A method MUST be declared Async when using Await within it's body 'It must return a Task or Task(Of T) object if you want the method 'to be awaitable Private Async Function LongMethod() As Task 'Simulates a long running method. 'The Await will yield this method to the caller for '500 milliseconds. Await Task.Delay(2000) End Function Private Async Sub btnDoWork_Click(sender As Object, e As EventArgs) Handles btnDoWork.Click 'Await here will yield to this button's click to the caller 'which in this case would the the application's message loop. 'This is why the UI would remain responsive. It's because the message 'loop will be running while Await waits for the LongMethod 'to complete. Await LongMethod() 'When LongMethod is complete, execution will continue from here MessageBox.Show("Asynchronous work has been completed.") End Sub 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:
Imports System.Threading Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load End Sub 'Pretend this is your Excel method Private Sub PretendThisIsYourLongRunningExcelCall() 'It takes 2 whole seconds to complete, potentially locking up 'the UI if we don't run it asynchronously Thread.Sleep(2000) End Sub Private Async Sub btnDoExcel_Click(sender As Object, e As EventArgs) Handles btnDoExcel.Click 'This will run your "Excel method" on another thread 'and Await will yield to the caller, the message loop in this case, 'while your Excel method runs. By yielding to the message loop, 'your app remains responsive. Await Task.Run(Sub() PretendThisIsYourLongRunningExcelCall()) MessageBox.Show("The work has been completed.") End Sub End Class
Last edited by Niya; Dec 13th, 2017 at 05:23 PM.
-
Dec 13th, 2017, 05:39 PM
#3
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.
-
Dec 13th, 2017, 06:21 PM
#4
Re: Error when trying to convert BackgroundWorker to async/await using Interop.Excel
Originally Posted by Sitten Spynne
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|