Writing Workbook with multiple sheets into a single text file

0

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.

vb.net
excel-2007
excel-interop
asked on Stack Overflow Sep 20, 2012 by Ortund • edited Sep 20, 2012 by Ortund

1 Answer

0

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.

answered on Stack Overflow Sep 20, 2012 by Germann Arlington

User contributions licensed under CC BY-SA 3.0