Array to Excel Range only gives first element of the Array

Question

I have created the following Function which will export the values of an array to Excel, but it only exports the first element.

Function Export_Excel ($Array,$Column,$Sheet) {
  [System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US"
  [System.Threading.Thread]::CurrentThread.CurrentUICulture = "en-US"

  $Global:sheet = $Global:wkbk.WorkSheets.Item("$Sheet")
  $intRow = 3

  $LastRow = $Array.count + 2
  $Range = "$Column" + $intRow +":$Column" + "$LastRow"

  $RangeSet = $Global:sheet.Range("$Range")
  $RangeSet.Value2 = $Array

  [gc]::collect()
  [gc]::WaitForPendingFinalizers() 
  [System.Threading.Thread]::CurrentThread.CurrentCulture = $Global:OldCulture
  [System.Threading.Thread]::CurrentThread.CurrentUICulture = $Global:OldUICulture
}

My array is generated with $Array+=$ValueForArray.

When I execute

Export_Excel $Array "B" "Sheet1"

it exports for the entire length of the array only the first element in each cell. Can anybody see what I'm doing wrong?

Just in case while I test I we need to know how $array was declared as well. $Array = @()? — Matt
– Matt, Commented May 20, 2015 at 12:22
When I use Export_Excel $Array "3" "Sheet1" The array is exported 'correctly' then you have all the information on Row 33. — user1000584
– user1000584, Commented May 20, 2015 at 12:52

TheMadTechnician · Accepted Answer · 2015-05-21 03:12:28Z

2

Is this just an array of strings? If so this can be done so much simpler. Convert each string to an object, convert the array of objects to a CSV with no type information and make it tab delimited, skip the header row, copy to the clipboard, paste in wherever you want it to flow down from. If you really want to keep it as a function, you could do it like:

Function Export_Excel ($Array,[String]$Column,[String]$Sheet) {
    $ColNum = ([int][char]$Column.ToUpper()) - 64
    $Array | Select @{n='Header';e={$_}} | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | Select -Skip 1 | clip
    [void]$Wkbk.WorkSheets.Item("Sheet1").Columns.Item($ColNum).Cells.Item(3).PasteSpecial()
}

Oh, right, and I converted the column letter into it's numeric equivalent in there too. So, to test this...

Function Export_Excel ($Array,[String]$Column,[String]$Sheet) {
    $ColNum = ([int][char]$Column.ToUpper()) - 64
    $Array | Select @{n='Header';e={$_}} | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | Select -Skip 1 | clip
    [void]$Wkbk.WorkSheets.Item("Sheet1").Columns.Item($ColNum).Cells.Item(3).PasteSpecial()
}

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Wkbk = $Excel.Workbooks.Add()

$Fruit = @("Apple","Orange","Banana")

Export_Excel $Fruit 'f' 'Sheet1'

Results: Excel opened, created a blank workbook, and then populated cell F3 with the word Apple, cell F4 with the word Orange, and cell F5 with the word Banana. I am pretty sure that was your intent. enter image description here

answered May 21, 2015 at 3:12

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

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

2 Comments

user1000584 Over a year ago

This works like a charm. Thank you very much. The export of the arrays to Excel takes less then a second now.

user1000584 Over a year ago

The only problem with the CovertTo-Csv is that if you have "é,ô,ö,à,è,..." in your array string, it will remove these characters and change it to ??. Using the swithc -encoding UTF8 doesn't change this

Ansgar Wiechers · Accepted Answer · 2015-05-20 13:32:54Z

1

The values are assigned in rows, not columns. Use a loop instead:

for ($i = 0; $i -lt $Array.Length; $i++) {
  $global:sheet.Cells.Item($i+3, $Column).Value2 = $Array[$i]
}

Note that you need to make $Column a numeric parameter for this.

As an alternative you could put the array in a row on a temp sheet, then copy/paste that range while transposing the values:

$temprange = "A1:" + [string][char]($Array.Length + 64) + "1"
$range     = "$Column${intRow}:$Column$LastRow"

$global:tempsheet.Range($temprange).Value2 = $Array
[void]$global:tempsheet.Range($temprange).Copy()
[void]$global:sheet.Range($range).PasteSpecial(-4163, -4142, $false, $true)

answered May 20, 2015 at 13:32

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

6 Comments

user1000584 Over a year ago

The only problem is that there will be Arrays with more than 100.000 elements in it. So the loop will take ages and the transposing won't be possible. Is there any way to assign the values in columns instead of rows?

Ansgar Wiechers Over a year ago

If you have 100000 rows or more, you shouldn't be using Excel in the first place.

user1000584 Over a year ago

I know, but this is a request from the management. The For Loop works, but it is really slow. If I could have the array in a column, it would be done in 50x the time.

Ansgar Wiechers Over a year ago

You could process the array in chunks of a couple thousand elements ($Array[0..15000], $Array[15001..30000], ...) using the copy/paste transposed approach.

TheMadTechnician Over a year ago

I'll have to agree with the OP here... looping through cells and setting their value is amazingly slow with the Excel ComObject. Excellent use of PasteSpecial with transposing though! That is awesome =)

|

Collectives™ on Stack Overflow

Array to Excel Range only gives first element of the Array

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related