Background worker STA error when using excel

EDN Admin · Nov 25, 2011

Hi,
I am trying to interface with excel in a vb.net windows app I am writting. Basically it imports csv files to a data table, adds some additional data from with a oledb datasource, creates a view to sort the data before pushing back in to a datatable and spitting
it out to a listview. The list view then allows you to further filter and sort the data (or will do shortly but thats not an issue).
From the data in the list there are then 2 options, it will either create individual reports for managers in a excel sheet, which it colour bands based on a total field and then emails out to the managers. This works fine.
The other feature is that it produces a summary report that goes to a central email account. Again it creates an excel workbook, adds 4 work sheets, copies all the data to a master sheet (sheet1), it then starts at the bottom of the master worksheet and
copies the total values from the total row for each employee and copies this value in to the next blank column. (This is basically used so I can determine based on the 3 score bandings which sheet each line should go on. The lines for an employee are made
up of individual leave lines and total line that has the summation of the individual lines for them).
At this point I have a spreadsheet with 4 sheets and the master sheet has all the data and a reference column on it. I then do a loop which select cell A2 (as we have headers in row 1) and loops selecting the entire row, looking a the reference value, pasting
that row to the relevant sheet and deleting the line off the summary sheet shifting the cells upwards until cell a2 = nothing.
Initially I had this running in my main thread (being a newb to background workers and threads), I soon found that I got an error relating to a long running processes and the solution was to add a background worker.
So I added a background worker with report and cancel enabled and a delegate to update the percentage complete on the main user form. This sets off running with no issues (there are on average 7000 lines on the master sheet) and after about 2000 lines it
stops moving the data, but continues working. What I mean is the percentage continues increasing and I also put a select cell a2 in there, so if I click another cell I can see it is running as it moves back to cell a2.
I played around a little further and then discovered the STA error, something about STA mode before OLE calls?
I also tried adding a clear clipboard incase it was something to do with that, but the sta error occurs when the delegate to update the percentage completed label is called now. So it is erroring on the first loop now.
Any ideas would be much appreciated.

Andy

<div style="color:black; background-color:white
<pre><span style="color:blue Private <span style="color:blue Sub Button6_Click(sender <span style="color:blue As System.Object, e <span style="color:blue As System.EventArgs) <span style="color:blue Handles Button6.Click

<span style="color:blue If btState = <span style="color:blue False <span style="color:blue Then
Button6.Text = <span style="color:#a31515 "Stop"
btState = <span style="color:blue True
dlg = <span style="color:#a31515 "\OPCHRIS21APPbradford_factor_testREPORTS" & <span style="color:#a31515 "BFR SUMMARY- " & Strings.Left(Replace(Today, <span style="color:#a31515 "/", <span style="color:#a31515 "", 1, , CompareMethod.Text), 4) & Strings.Right(Today, 2) & <span style="color:#a31515 ".xls"
<span style="color:green Process.Start(dlg)
btState = summaryXls(dlg, ListView1, m3)
<span style="color:blue If btState = <span style="color:blue False <span style="color:blue Then Button6.Text = <span style="color:#a31515 "Summary"
<span style="color:blue Else
Button6.Text = <span style="color:#a31515 "Summary"
btState = <span style="color:blue False
<span style="color:green Is the Background Worker do some work?
<span style="color:blue If My_BgWorker.IsBusy <span style="color:blue Then
<span style="color:green If it supports cancellation, Cancel It
<span style="color:blue If My_BgWorker.WorkerSupportsCancellation <span style="color:blue Then
<span style="color:green Tell the Background Worker to stop working.
My_BgWorker.CancelAsync()
<span style="color:blue End <span style="color:blue If
<span style="color:blue End <span style="color:blue If
<span style="color:blue End <span style="color:blue If
<span style="color:blue End <span style="color:blue Sub

<span style="color:blue Public <span style="color:blue Function summaryXls(<span style="color:blue ByVal filename <span style="color:blue As <span style="color:blue String, <span style="color:blue ByVal lv <span style="color:blue As ListView, m3 <span style="color:blue As <span style="color:blue String) <span style="color:blue As <span style="color:blue Boolean
dimmer: <span style="color:blue Dim xlApp <span style="color:blue As <span style="color:blue New Excel.Application <span style="color:green Creates a new instance of excel
<span style="color:blue Dim xlWkb <span style="color:blue As Excel.Workbook <span style="color:green Workbook object
<span style="color:blue Dim xlSh <span style="color:blue As Excel.Worksheet <span style="color:green Worksheet object
<span style="color:blue Dim xlSh1 <span style="color:blue As Excel.Worksheet <span style="color:green Worksheet object
<span style="color:blue Dim xlSh2 <span style="color:blue As Excel.Worksheet <span style="color:green Worksheet object
<span style="color:blue Dim xlSh3 <span style="color:blue As Excel.Worksheet <span style="color:green Worksheet object
<span style="color:blue Dim xlsh4 <span style="color:blue As Excel.Worksheet <span style="color:green Worksheet object
<span style="color:blue Dim misValue <span style="color:blue As <span style="color:blue Object = System.Reflection.Missing.Value <span style="color:green No value overide

<span style="color:blue Try
xlApp.Visible = <span style="color:blue True
<span style="color:blue Catch
<span style="color:blue GoTo dimmer
<span style="color:blue End <span style="color:blue Try

xlWkb = xlApp.Workbooks.Add(misValue) <span style="color:green Create a workbook without a name
<span style="color:green xlWkb.Application.ScreenUpdating = False Turn Screen updating off
xlSh = xlWkb.Worksheets(1) <span style="color:green Reference sheet
xlSh.Name = <span style="color:#a31515 "MASTER" <span style="color:green Rename sheet
xlSh1 = xlWkb.Worksheets(2) <span style="color:green Reference sheet
xlSh1.Name = <span style="color:#a31515 "BRADFORD SUMMARY GREEN" <span style="color:green Rename sheet
xlSh1.Tab.Color = Color.LightGreen <span style="color:green Colour Tab
xlSh2 = xlWkb.Worksheets(3) <span style="color:green Reference sheet
xlSh2.Name = <span style="color:#a31515 "BRADFORD SUMMARY AMBER" <span style="color:green Rename sheet
xlSh2.Tab.Color = Color.Orange <span style="color:green Colour Tab
xlSh3 = xlWkb.Worksheets.Add(After:=xlWkb.Worksheets(3)) <span style="color:green Add sheet
xlSh3.Name = <span style="color:#a31515 "BRADFORD SUMMARY RED" <span style="color:green Rename sheet
xlSh3.Tab.Color = Color.Red <span style="color:green Colour Tab
xlsh4 = xlWkb.Worksheets.Add(After:=xlWkb.Worksheets(4)) <span style="color:green Add sheet
xlsh4.Name = <span style="color:#a31515 "Exceptions" <span style="color:green Rename sheet
xlsh4.Tab.Color = Color.Yellow <span style="color:green Colour Tab
xlSh.<span style="color:blue Select() <span style="color:green Select master

<span style="color:green Write Headers
<span style="color:blue For i <span style="color:blue As <span style="color:blue Integer = 1 <span style="color:blue To lv.Columns.<span style="color:blue Count - 5 <span style="color:green Loops accross columns
xlSh.Cells(1, i) = lv.Columns(i).Text <span style="color:green Adds column heading
xlSh1.Cells(1, i) = lv.Columns(i).Text
xlSh2.Cells(1, i) = lv.Columns(i).Text
xlSh3.Cells(1, i) = lv.Columns(i).Text
<span style="color:blue Next

<span style="color:green Write Values
<span style="color:blue Dim r <span style="color:blue As <span style="color:blue Integer = 2 <span style="color:green Holds row to write to
<span style="color:blue For i <span style="color:blue As <span style="color:blue Integer = 0 <span style="color:blue To lv.Items.<span style="color:blue Count - 1 <span style="color:green Loops through Rows
<span style="color:blue For j <span style="color:blue As <span style="color:blue Integer = 1 <span style="color:blue To lv.Columns.<span style="color:blue Count - 5
<span style="color:blue Dim c <span style="color:blue As Excel.Range = xlSh.Cells(r, j)
<span style="color:blue If j = 11 <span style="color:#a31515 "" <span style="color:blue Then
c.Value = <span style="color:blue CDate(lv.Items(i).SubItems(j).Text)
<span style="color:blue End <span style="color:blue If
<span style="color:blue Else
c.Value = lv.Items(i).SubItems(j).Text
 <span style="color:#a31515 "" <span style="color:blue Then
<span style="color:blue Dim cVal <span style="color:blue As <span style="color:blue Integer
cVal = c.Value
 100 = 200 <span style="color:blue Then
xlSh.Range(xlSh.Cells(r, 1), c).Interior.Color = Color.Red
<span style="color:blue End <span style="color:blue If
<span style="color:blue End <span style="color:blue If
<span style="color:blue Next
r = r + 1
<span style="color:blue Next

<span style="color:blue For x <span style="color:blue As <span style="color:blue Integer = 2 <span style="color:blue To r - 1
<span style="color:blue Dim uRow <span style="color:blue As Excel.Range = xlSh.Cells(r - 1 - (x - 2), 20) <span style="color:green This cell u39
<span style="color:blue Dim vRow <span style="color:blue As Excel.Range = xlSh.Cells(r - (x - 2), 19) <span style="color:green s40
<span style="color:blue Dim xRow <span style="color:blue As Excel.Range = xlSh.Cells(r - (x - 2), 20) <span style="color:green t40
<span style="color:blue Dim yRow <span style="color:blue As Excel.Range = xlSh.Cells(r - 1 - (x - 2), 19) <span style="color:green s39
<span style="color:blue Dim eRow <span style="color:blue As Excel.Range = xlSh.Cells(r - 1 - (x - 2), 1) <span style="color:green a39
<span style="color:blue Dim cRow <span style="color:blue As Excel.Range = xlSh.Cells(r - (x - 2), 1) <span style="color:green a40
<span style="color:blue If eRow.Value = cRow.Value <span style="color:blue Then
<span style="color:blue If vRow.Value = <span style="color:blue Nothing <span style="color:blue Then
uRow.Value = xRow.Value
<span style="color:blue Else
uRow.Value = vRow.Value
<span style="color:blue End <span style="color:blue If
<span style="color:blue Else
uRow.Value = yRow.Value
<span style="color:blue End <span style="color:blue If
<span style="color:blue Next
xlSh.Cells(1, 20) = <span style="color:#a31515 "Summary"

m_CountTo = r - 1
My_BgWorker.RunWorkerAsync(<span style="color:blue New <span style="color:blue Object() {xlApp, xlWkb, xlSh, xlSh1, xlSh2, xlSh3, xlsh4})
<span style="color:blue Return <span style="color:blue False
<span style="color:blue End <span style="color:blue Function

<span style="color:blue Private <span style="color:blue Sub My_BgWorker_DoWork(<span style="color:blue ByVal sender <span style="color:blue As <span style="color:blue Object, <span style="color:blue ByVal e <span style="color:blue As System.ComponentModel.DoWorkEventArgs) <span style="color:blue Handles My_BgWorker.DoWork
<span style="color:blue Dim args <span style="color:blue As <span style="color:blue Object() = <span style="color:blue DirectCast(e.Argument, <span style="color:blue Object())
<span style="color:blue Dim xlapp <span style="color:blue As Excel.Application = args(0)
<span style="color:blue Dim xlwkb <span style="color:blue As Excel.Workbook = args(1)
<span style="color:blue Dim xlsh <span style="color:blue As Excel.Worksheet = args(2)
<span style="color:blue Dim xlsh1 <span style="color:blue As Excel.Worksheet = args(3)
<span style="color:blue Dim xlsh2 <span style="color:blue As Excel.Worksheet = args(4)
<span style="color:blue Dim xlsh3 <span style="color:blue As Excel.Worksheet = args(5)
<span style="color:blue Dim xlsh4 <span style="color:blue As Excel.Worksheet = args(6)
<span style="color:blue Dim s1r <span style="color:blue As <span style="color:blue Integer = 2
<span style="color:blue Dim s2r <span style="color:blue As <span style="color:blue Integer = 2
<span style="color:blue Dim s3r <span style="color:blue As <span style="color:blue Integer = 2
<span style="color:blue Dim s4r <span style="color:blue As <span style="color:blue Integer = 2
<span style="color:blue Dim nRow <span style="color:blue As Excel.Range = xlSh.Cells(2, 1)
<span style="color:blue Dim myRow <span style="color:blue As Excel.Range = xlSh.Cells(2, 20)
<span style="color:blue Dim i <span style="color:blue As <span style="color:blue Integer = 0
<span style="color:blue On <span style="color:blue Error <span style="color:blue GoTo escape

<span style="color:blue For p <span style="color:blue As <span style="color:blue Integer = 1 <span style="color:blue To 1
nRow.<span style="color:blue Select()
<span style="color:blue Dim yQ = vbNo
<span style="color:blue Do <span style="color:blue Until nRow.Value = <span style="color:blue Nothing
<span style="color:green Has the background worker be told to stop?
<span style="color:blue If My_BgWorker.CancellationPending <span style="color:blue Then
<span style="color:green Set Cancel to True
e.Cancel = <span style="color:blue True
<span style="color:blue Exit <span style="color:blue For
<span style="color:blue End <span style="color:blue If
i = i + 1
<span style="color:blue If myRow.Value < 101 <span style="color:blue Then <span style="color:green GREENS
myRow.EntireRow.Copy(xlsh1.Cells(s1r, 1))
myRow.EntireRow.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftUp)
s1r = xlsh1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row + 1
 100 <span style="color:blue And myRow.Value < 200 <span style="color:blue Then <span style="color:green AMBERS
myRow.EntireRow.Copy(xlsh2.Cells(s2r, 1))
myRow.EntireRow.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftUp)
s2r = xlsh2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row + 1
 200 <span style="color:blue Then <span style="color:green REDS
myRow.EntireRow.Copy(xlsh3.Cells(s3r, 1))
myRow.EntireRow.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftUp)
s3r = xlsh3.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row + 1
<span style="color:blue Else
myRow.EntireRow.Copy(xlsh4.Cells(s4r, 1))
myRow.EntireRow.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftUp)
s4r = xlsh4.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row + 1
<span style="color:blue End <span style="color:blue If

nRow = xlsh.Cells(2, 1)
myRow = xlsh.Cells(2, 20)
My_BgWorker.ReportProgress(

Code:

CInt

Code:

((i / m_CountTo) * 100)

)
SetLabelText_ThreadSafe(<span style="color:blue Me.Lbl_Status, FormatPercent(i / m_CountTo, 2))
nRow.<span style="color:blue Select()
<span style="color:blue If yQ = vbYes <span style="color:blue Then
<span style="color:green SetButtonText_ThreadSafe(Me.Button6, "Summary")
<span style="color:blue Exit <span style="color:blue Sub
<span style="color:blue End <span style="color:blue If
<span style="color:blue If i = m_CountTo <span style="color:blue Then
yQ = MsgBox(<span style="color:#a31515 "Oh no it may have gone over the number of lines available, stop process?", vbYesNo)
<span style="color:blue End <span style="color:blue If
Clipboard.Clear()
<span style="color:blue Loop

<span style="color:blue Dim s1d <span style="color:blue As Excel.Range = xlSh1.Cells(1, 20)
<span style="color:blue Dim s2d <span style="color:blue As Excel.Range = xlSh2.Cells(1, 20)
<span style="color:blue Dim s3d <span style="color:blue As Excel.Range = xlsh3.Cells(1, 20)
<span style="color:blue Dim s4d <span style="color:blue As Excel.Range = xlsh4.Cells(1, 20)

s1d.EntireColumn.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftToLeft)
s2d.EntireColumn.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftToLeft)
s3d.EntireColumn.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftToLeft)
s4d.EntireColumn.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftToLeft)

<span style="color:blue Dim uRng <span style="color:blue As Excel.Range
uRng = xlSh.UsedRange
uRng.Font.Name = <span style="color:#a31515 "Arial Unicode"
xlSh.Columns.AutoFit()
<span style="color:green xlSh.AutoFilter.ApplyFilter()
uRng = xlSh2.UsedRange
uRng.Font.Name = <span style="color:#a31515 "Arial Unicode"
xlSh2.Columns.AutoFit()
<span style="color:green xlSh.AutoFilter.ApplyFilter()
uRng = xlSh3.UsedRange
uRng.Font.Name = <span style="color:#a31515 "Arial Unicode"
xlSh3.Columns.AutoFit()
<span style="color:green xlSh.AutoFilter.ApplyFilter()
uRng = xlsh4.UsedRange
uRng.Font.Name = <span style="color:#a31515 "Arial Unicode"
xlsh4.Columns.AutoFit()
<span style="color:green xlSh.AutoFilter.ApplyFilter()
xlWkb.Application.DisplayAlerts = <span style="color:blue False <span style="color:green turn off alerts
xlSh.Delete()
xlWkb.Application.DisplayAlerts = <span style="color:blue True <span style="color:green turn on alerts
<span style="color:green xlWkb.Application.ScreenUpdating = True Turn Screen updating on
xlWkb.SaveAs(Filename:=dlg, FileFormat:=39)
xlWkb.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWkb)
releaseObject(xlsh)
releaseObject(xlsh1)
releaseObject(xlsh2)
releaseObject(xlsh3)
releaseObject(xlsh4)

EmailMgr(<span style="color:#a31515 "Payroll", <span style="color:#a31515 "", m3, dlg)
<span style="color:blue Next
<span style="color:blue Exit <span style="color:blue Sub
escape: MsgBox(Err.Number & <span style="color:#a31515 " - " & Err.Description)
<span style="color:blue End <span style="color:blue Sub

<span style="color:green The delegate
<span style="color:blue Delegate <span style="color:blue Sub SetLabelText_Delegate(<span style="color:blue ByVal [Label] <span style="color:blue As Label, <span style="color:blue ByVal [text] <span style="color:blue As <span style="color:blue String)

<span style="color:green The delegates subroutine.
<span style="color:blue Private <span style="color:blue Sub SetLabelText_ThreadSafe(<span style="color:blue ByVal [Label] <span style="color:blue As Label, <span style="color:blue ByVal [text] <span style="color:blue As <span style="color:blue String)
<span style="color:green InvokeRequired required compares the thread ID of the calling thread to the thread ID of the creating thread.
<span style="color:green If these threads are different, it returns true.
<span style="color:blue If [Label].InvokeRequired <span style="color:blue Then
<span style="color:blue Dim MyDelegate <span style="color:blue As <span style="color:blue New SetLabelText_Delegate(<span style="color:blue AddressOf SetLabelText_ThreadSafe)
<span style="color:blue Me.Invoke(MyDelegate, <span style="color:blue New <span style="color:blue Object() {[Label], [text]})
<span style="color:blue Else
[Label].Text = [text]
<span style="color:blue End <span style="color:blue If
<span style="color:blue End <span style="color:blue Sub

<span style="color:blue Private <span style="color:blue Sub My_BgWorker_ProgressChanged(<span style="color:blue ByVal sender <span style="color:blue As <span style="color:blue Object, <span style="color:blue ByVal e <span style="color:blue As System.ComponentModel.ProgressChangedEventArgs) <span style="color:blue Handles My_BgWorker.ProgressChanged
<span style="color:green Update the progress bar
progress(e.ProgressPercentage, 100)
<span style="color:blue End <span style="color:blue Sub

<span style="color:blue Private <span style="color:blue Sub My_BgWorker_RunWorkerCompleted(<span style="color:blue ByVal sender <span style="color:blue As <span style="color:blue Object, <span style="color:blue ByVal e <span style="color:blue As System.ComponentModel.RunWorkerCompletedEventArgs) <span style="color:blue Handles My_BgWorker.RunWorkerCompleted
<span style="color:blue If e.Cancelled <span style="color:blue Then
<span style="color:blue Me.Lbl_Status.Text = <span style="color:#a31515 "Cancelled"
<span style="color:blue Else
<span style="color:blue Me.Lbl_Status.Text = 
 

 

View the full article

Background worker STA error when using excel

EDN Admin

Well-known member

Similar threads