CODEWITHSUNDEEP
arraysexcelpowershell
user1000584
user1000584

Reputation: 67

Array to Excel Range only gives first element of the Array

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?

Upvotes: 1

Views: 1497

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36332

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

Upvotes: 2

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200453

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)

Upvotes: 1

Related Questions