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

excel, vb.net

Solution

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

Problem

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 ```

Original source