I'm trying split up the following code onto multiple threads (one for each sheet in the workbook). With over 400,000 rows to work on collectively, this would take quite some time on 1 thread, I'm hoping that if I split it, this will speed it up.
It's been a while since I've worked with Excel Interop, but I'm pretty sure the following code should work. I want this to run on each thread:
Dim CurrentRow As String = Nothing
For i As Integer = 1 To sheet.Rows.Count
If Not String.IsNullOrEmpty(sheet.Range("A" & i).ToString) Then
CurrentRow = _
sheet.Range("H" & i).Value.ToString.Trim & "," & _
sheet.Range("A" & i).Value.ToString.Trim & "," & _
sheet.Range("B" & i).Value.ToString.Trim & "," & _
sheet.Range("C" & i).Value.ToString.Trim & "," & _
sheet.Range("D" & i).Value.ToString.Trim & "," & _
sheet.Range("E" & i).Value.ToString.Trim & "," & _
sheet.Range("F" & i).Value.ToString.Trim & "," & _
sheet.Range("G" & i).Value.ToString.Trim & "," & _
sheet.Range("I" & i).Value.ToString.Trim & "," & _
sheet.Range("J" & i).Value.ToString.Trim & "," & _
sheet.Range("K" & i).Value.ToString.Trim & ","
My.Computer.FileSystem.WriteAllText("D:\Lists\" & sheet.Name & ".txt", _
CurrentRow & vbCrLf, True)
ItemCount = ItemCount + 1
ElapsedTime = Now().Subtract(StartTime)
Me.Invoke(Me.AddItemDelegate, New Object() {sheet.Name, ElapsedTime})
Else
ItemCount = 0
Exit For
End If
Unfortunately, I'm getting the following error on the if statement:
System.Runtime.InteropServices.ComException (0x800401A8): Exception from HRESULT 0x800401A8 at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
Not sure what I'm doing wrong here, can anyone assist?
Thanks!
** EDIT **
The loop seems to work, but only through one iteration. When it wants to move onto the next run, it throws the exception.
In your IF statement ... sheet.Range("A" & i) ...
is missing second argument.
I suspect you meant to access single cell (NOT the range of cells) - I am not sure of the exact VB syntax - you will have to look it up yourself.
The same problem will occur in your assignment statements too.
User contributions licensed under CC BY-SA 3.0