Using Cell within Range

PowerShell Help
1

by jack_death at 2012-08-17 22:00:34

Hi all,

First post on this board. I’ve been working on a script to take a flash report that is formatted poorly for use in a pivot table and "clean up" the data. Everything works fine if I iterate through cells that I want to change with this command:

$sheet.Cells.Item($row,$col).PasteSpecial(7)

Problem, it is rather slow. I would prefer to used the Filldown() method, but I am having a problem addressing the range using Cells. This works fine:

$sheet.Range("G6:G18").FillDown()

Not a permissions issue or anything environmental.

If I try this (grabbed from sample code on Technet):

$sheet.Range(Cells(6,7),Cells(18,7)).FillDown()

It doesn’t work. I am of course not using simple values as above, but even dumbed down this isn’t working.

Anyone have working code using Cells inside a Range?
by surveyor at 2012-08-18 02:26:52
Hi,
please try this: (Table-Name must be changed to your need)

# With help from
# https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.windows.powershell/l3XX4jUtEPE[1-25]


$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Arbeitsmappe = $Excel.Workbooks.Open(‘D:\Test.xlsx’)

If ($Arbeitsmappe) {

If ($Arbeitsmappe.ReadOnly) {
Write-Host "Test.xlsx is readonly." -ForegroundColor Red
Exit
} else {
$Tabellenblatt = $Arbeitsmappe.WorkSheets.Item("Tabelle1") # Maybe "Table1" in english?
# Autofill
$Tabellenblatt.Cells.Item(1,1).Value2 = (get-date -Format ‘dd/MM/yyyy’)
$Bereich1 = $Tabellenblatt.Range("A1")
$Bereich2 = $Tabellenblatt.Range("A1:A11")
$Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)
# Two steps for a matrix!
$Bereich1 = $Tabellenblatt.Range("A1:A11")
$Bereich2 = $Tabellenblatt.Range("A1:B11")
$Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)

# Filldown
$Tabellenblatt.Cells.Item(1,4).Value2 = (get-date -Format ‘dd/MM/yyyy’)
$Spalte = $Tabellenblatt.Range("D:D")
$Spalte.FillDown()
}
}

$Arbeitsmappe.Save()

$Excel.Quit()
Remove-Variable -Name Excel -Force
[GC]::Collect()
Remove-ComObject
by jack_death at 2012-08-18 06:38:48
Thanks for the reply, but the examples all use A1 references, I need a way to use R1C1.
by surveyor at 2012-08-18 08:06:47
Then try this. You can variables instead of fixed numbers in …item(…)

# With help from
# https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.windows.powershell/l3XX4jUtEPE[1-25]


$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Arbeitsmappe = $Excel.Workbooks.Open(‘D:\Test.xlsx’)

If ($Arbeitsmappe) {

If ($Arbeitsmappe.ReadOnly) {
Write-Host "Test.xlsx is readonly." -ForegroundColor Red
Exit
} else {
$Tabellenblatt = $Arbeitsmappe.WorkSheets.Item("Tabelle1") # Maybe "Table1" in english?
# Autofill
$Tabellenblatt.Cells.Item(1,1).Value2 = (get-date -Format ‘dd/MM/yyyy’)
$Bereich1 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(1,1))
$Bereich2 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,1))
$Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)
# Two steps for a matrix!
$Bereich1 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,1))
$Bereich2 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,2))
$Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)

# Filldown
$Tabellenblatt.Cells.Item(1,4).Value2 = (get-date -Format ‘dd/MM/yyyy’)
$Spalte = $Tabellenblatt.Range($Tabellenblatt.Columns.Item(4),$Tabellenblatt.Columns.Item(4))
$Spalte.FillDown()
}
}

$Arbeitsmappe.Save()

$Excel.Quit()
Remove-Variable -Name Excel -Force
[GC]::Collect()
Remove-ComObject
by jack_death at 2012-08-20 07:39:27
Thanks for the reply. Creating a variable with Cells.Item and then calling the range is the solution, as you have pointed out here. I was trying to get VBA code to work - the example I copied was on a Powershell page but was actually using VBA methods.
by surveyor at 2012-08-20 09:47:18
Sometimes I too record macros in Excel to get the methods and properties for the things I want to do. But I find it very difficult to translate the macros to powershell because of the other usage of objects with …item etc. And also in macros and VBA there is the WITH-statement to make things shorter, which does not exist in Powershell.

Helpful is the COM-reference in http://msdn.microsoft.com/en-us/library/office/ff846370
by willsteele at 2012-08-20 10:13:26
It has been my hope either a PowerShell or .NET based script/command generator would have been developed for Office. Maybe vNext.