In order to speed up writing values to Excel, in VB.Net is it possible to write an Array to a Row rather than a Value to a Cell?

I have tried several ways, it either writes nothing or writes just the first value of the Array.

Any help would be greatly appreciated.

Thanks.

```
Imports Excel = Microsoft.Office.Interop.Excel
...
Dim Array(2) As String
Array(1) = "Hello"
Array(2) = "World"
...
' Tried several ways one at a time...
objSheet.Cells("C5:C6") = Array
objSheet.Cells("C5:C6").Value = Array
objSheet.Range("C5:C6").Value = Array
objSheet.Range("C5").Value = Array
```

After the first answer, here is the revised code

```
Dim Array(2, 0) As String
Array(0, 0) = "Hello"
Array(1, 0) = "World"
Array(2, 0) = "One"
...
' Test 1
objSheet.Cells("C5:C6").Value = Array 'I get Invalid Parameter (Exception HRESULT : 0x80070057 (E_INVALIDARG))
' Test 2
objxlRange = objSheet.Range("C5:C7") ' Writes Array content as a column (Vertically)
objxlRange.Value = Array
' Test 3
objxlRange = objSheet.Range("C5:E5") ' Writes only first entry 'Hello' in each cell
objxlRange.Value = Array
```

How can I write the Array to a Row (Horizontally)?

Thanks

EDIT

OK Thanks, now it works!

Here is the final working code for all to share!

```
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
' Create an Excel file and write ArrayRow as a Row and ArrayCol as a Column
Dim objApp As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim Rng As Excel.Range
Dim StartRow, StartCol
' Array as a Row
Dim ArrayRow(0, 3) As String
ArrayRow(0, 0) = "This"
ArrayRow(0, 1) = "is"
ArrayRow(0, 2) = "a"
ArrayRow(0, 3) = "Row"
' Array as a Column
Dim ArrayCol(3, 0) As String
ArrayCol(0, 0) = "Now"
ArrayCol(1, 0) = "it's"
ArrayCol(2, 0) = "a"
ArrayCol(3, 0) = "Column"
' New instance of Excel and start a new workbook.
objApp = New Excel.Application()
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets(1)
'Write Array as a Row
StartRow = 1
StartCol = 1
With objSheet
Rng = .Range(.Cells(StartRow, StartCol), _
.Cells(UBound(ArrayRow, 1) - LBound(ArrayRow, 1) + StartRow, _
UBound(ArrayRow, 2) - LBound(ArrayRow, 2) + StartCol))
End With
Rng.Value = ArrayRow ' Row
'Write Array as a Column
StartRow = 3
StartCol = 1
With objSheet
Rng = .Range(.Cells(StartRow, StartCol), _
.Cells(UBound(ArrayCol, 1) - LBound(ArrayCol, 1) + StartRow, _
UBound(ArrayCol, 2) - LBound(ArrayCol, 2) + StartCol))
End With
Rng.Value = ArrayCol ' Column
' Save
objBook.SaveAs("C:\Excel_Range_Test.xls", FileFormat:=56)
objBook.Close()
End Sub
End Class
```

It's been awhile, but I think you have to use a multidimensional array to do this.

So, something like this (remember, arrays are 0-based):

```
Dim Array(1, 0) As String
Array(0, 0) = "Hello"
Array(1, 0) = "World"
objSheet.Range("C5:C6").Value = Array
```

To do it as a row instead of a column, flip the dimensions of your array.

```
Dim ArrayRow(0, 1) As String
ArrayRow(0, 0) = "Goodnight"
ArrayRow(0, 1) = "Moon"
objSheet.Range("C1:D1").Value = ArrayRow
```

User contributions licensed under CC BY-SA 3.0