In VB.Net, how to write an Array to Excel

Question

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

aphoria · Accepted Answer · 2014-03-28 12:15:58Z

5

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

EDIT

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

edited Mar 28, 2014 at 12:15

answered Mar 27, 2014 at 15:27

aphoria

20.3k8 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerhard Powell Over a year ago

I agree. Select the range and assign an array to it. You can assign multiple datatypes to it (int, real, string) It is actually better to use .Value2 instead of .Value

Collectives™ on Stack Overflow

In VB.Net, how to write an Array to Excel

1 Answer 1

EDIT

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related