You should always make sure that when you access elements of the UI that you do so on the UI thread otherwise you can get the error you're experiencing.
The easiest way to avoid this issue is to separate out the code that reads the data from the UI and the code that writes your Excel file.
First start by defining a simple class to hold your data:
Private Class BgwData
Public FileName As String
Public Headers As String()
Public Data As String()()
I defined this inside the form class as it doesn't need to be exposed outside of the form.
Now the code that calls the background worker needs to change so that an instance of
BgwData is created, populated with the data from the
ListView, as well as the file name, and sent to the worker as its argument.
Dim data As New BgwData() With _
.FileName = saveFileDialog1.FileName, _
.Headers = _
.Cast(Of System.Windows.Forms.ColumnHeader)() _
.Select(Function(ch) ch.Name) _
.Data = _
Me.ListView1.Items.Cast(Of ListViewItem)() _
.Select(Function(lvi) lvi.SubItems _
.Cast(Of ListViewItem.ListViewSubItem)() _
.Select(Function(lvsi) lvsi.Text) _
This code runs in the
PictureBox2_Click so is still on the UI thread.
BackgroundWorker1_DoWork method changes slightly to call
saveExcelFile like this:
The signature for
saveExcelFile changes to
Private Sub saveExcelFile(ByVal data As BgwData)
And the code that populates the spreadsheet becomes:
Dim row As Integer = 1
Dim col As Integer = 1
For i = 0 To data.Headers.Length - 1
sheet.Cells(1, i + 1) = data.Headers(i)
For i = 0 To data.Data.Length - 1
For j = 0 To data.Data(i).Length - 1
sheet.Cells(i + 2, j + 1) = data.Data(i)(j)
And, of course, there is a small change to the
SaveAs method call to become:
Also, as a side note, do keep in mind that you need release your COM objects after you use them too: