Using Cell within Range

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 11 months ago.

  • Author
    Posts
  • #5019

    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.

You must be logged in to reply to this topic.