Writing to Excel

[shers]

Hi,

I have done some code to write to Excel from a String Array of 3 items. These 3 items are written to columns 1, 2, 17 or 3, 4, 17, depending on the value of the 3rd item in the array. For certain Excel files, this is taking too long to write. Is it because I'm writing cell by cell? Is there any other way of writing faster to Excel?

Thanks

[adam_k]

How many cells are you writing?

[shers]

3 cells. In one case, cells 1, 2, 17 and in a second case, cells 3, 4, 17.

Thanks

[adam_k]

I am interested in seeing your code. Even if you type character by character in 3 cells you don't say it's taking too long.

PS: When you debug your program and step through the commands, do you notice any particular delays in any of the commands?

[shers]

Yes, I do notice delays in the line where the data is writing to the cell.

Here's my code:

For Each row As String() In lstItems
      If row(3) = "Yes" Then
            xs.Cells(row, 1).Value = row(0)
            xs.Cells(row, 2).Value = row(1)
            xs.Cells(row, 17).Value = row(2)
            row += 1
       ElseIf row(3) = "No" Then
            xs.Cells(hrow, 3).Value = row(0)
            xs.Cells(hrow, 4).Value = row(1)
            xs.Cells(hrow, 17).Value = row(2)
            hrow += 1
     End If
Next

Thanks

[ShahanDev]

Hi!

to writing faster in an excel sheet you need A Fast bulk copy Method:

See this heading in the link "A "Fast Bulk-Copy" Method" :
http://www.codeproject.com/KB/office/FastExcelExporting.aspx

[ShahanDev]

Have a look at this link as well:
http://www.codeproject.com/KB/vb/VB_Net.aspx

I myself have used the previous method of "Bulk Copy".