CODEWITHSUNDEEP
.netvb.net
lolikols
lolikols

Reputation: 87

Passing Excel variables between Background Workers

I have two background workers and need to pass things from the first one to the other.

This is what I'm trying to pass:

Dim APP As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Excel.Workbook
Dim worksheet As Excel.Worksheet
Dim worksheetRange As Excel.Range
Dim status As String


And this is how I'm trying to pass it:

Private Sub bwExport_DoWork(sender As Object, e As DoWorkEventArgs) Handles bwExport.DoWork
    Dim APP As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    Dim workbook As Excel.Workbook
    Dim worksheet As Excel.Worksheet
    Dim worksheetRange As Excel.Range
    Dim status As String

    'code stuff here

    Dim ExportList As New List(Of Object)(5)
    ExportList.Add(APP)
    ExportList.Add(workbook)
    ExportList.Add(worksheet)
    ExportList.Add(worksheetRange)
    ExportList.Add(status)
    e.Result = ExportList
End Sub


Private Sub bwExport_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bwExport.RunWorkerCompleted
        bwExportFinish.RunWorkerAsync(e.Result)
End Sub


And this is how I'm trying to retrieve it:

Private Sub bwExportFinish_DoWork(sender As Object, e As DoWorkEventArgs) Handles bwExportFinish.DoWork
    Dim ExportList As List(Of Object) = TryCast(e.Result, List(Of Object))
    Dim APP As Excel.Application = TryCast(ExportList(0), Excel.Application)
    Dim workbook As Excel.Workbook = TryCast(ExportList(1), Excel.Workbook)
    Dim worksheet As Excel.Worksheet = TryCast(ExportList(2), Excel.Worksheet)
    Dim worksheetRange As String = TryCast(ExportList(3), String)
    Dim status As String = TryCast(ExportList(4), String)

    'Save spreadsheet
    Dim SavePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\" & label1.Text & " " & Status & " Status " & System.DateTime.Now.ToString("MM.dd.yyyy HH;mm;ss") & ".xlsx"
    workbook.SaveAs(SavePath)
    workbook.Close(False)
End Sub


And this is the error that I'm getting:
1. The line: Dim APP As Excel.Application = TryCast(ExportList(0), Excel.Application())
gives me the error: Value of type 'Application()' cannot be converted to 'Application'.
I'm blind. I just made a formatting error. This is fixed.

2. I get an error that brings up a page in VS2015 that says "Source Not Available" and then An unhandled exception of the type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll



Does anyone have any ideas?

Upvotes: 0

Views: 101

Answers (1)

41686d6564
41686d6564

Reputation: 19651

Well, there are a couple mistakes in your code, I'm going to point them out.

  • As mentioned in my comment above:

    The error actually explains it pretty well. Your original variable is of type Excel.Application, while Excel.Application() is an array of the same type. Hence, you would need to cast it as Excel.Application if casting is even required.

  • In your second BackgroundWorker (bwExportFinish), you're trying to get the List(Of Object) which you passed from the first BackgroundWorker, but you're using the wrong variable (e.Result). How's that? Well, you store the list in e.Result of the first BackgroundWorker not the second one. Now after you pass that list to the second BackgroundWorker, you can access it using e.Argument not e.Result.

  • Another minor mistake is that you created the variable worksheetRange of type Excel.Range and then you cast it as String.

Hence, your code in the second BackgroundWorker should look something like the following:

Private Sub bwExportFinish_DoWork(sender As Object, e As DoWorkEventArgs) Handles bwExportFinish.DoWork
    Dim ExportList As List(Of Object) = TryCast(e.Argument, List(Of Object))
    If ExportList Is Nothing Then
        ' Casting has failed. Choose how you want to handle this.
    End If

    'Dim APP As Excel.Application = TryCast(ExportList(0), Excel.Application)
    'Or you can skip the casting if you already know you passed the right object.
    Dim APP As Excel.Application = ExportList(0)
    Dim workbook As Excel.Workbook = ExportList(1)
    Dim worksheet As Excel.Worksheet = ExportList(2)
    Dim worksheetRange As Excel.Range = ExportList(3)
    Dim status As String = ExportList(4)

    'Save spreadsheet
    workbook.SaveAs(SavePath)
    workbook.Close(False)
    APP.Quit()
End Sub

Hope that helps :)

Upvotes: 2

Related Questions