Author Posts

September 11, 2015 at 2:38 pm

Hello folks,

I am trying to write a PS script where it will select entire row or a range and then insert the selection to row below.
$selection = $sourceWorkbook.Worksheets.item(1).Range("A125:E125").EntireRow.Select
$selection.Copy()
$selection.insert($xlShiftDown)

This code is giving me the error below:
Method invocation failed because [System.Management.Automation.PSMethod] doesn't contain a method named 'insert'

September 11, 2015 at 6:37 pm

Ok. If anyone interested, below code works when I run it with PowerShell ISE (x86); however, when I run it via Run Command it won't work. This is the part that doesn't work:
# xlDirection is the parent containing constants related to Direction
$xlDirection = "microsoft.office.interop.excel.xlDirection" -as [type]
$xlDown = $xlDirection::xlDown
.......
....(There are couple of statements in between)
.
.
$selection = $sourceWorkbook.Worksheets.item(1).Range("A125:E125")
$selection.Copy()
$selection.insert($xlDown)

The error reads as below:
Exception calling "Insert" with "1" argument(s): "Insert method of Range class failed"

I don't get this error with ISE. It runs smoothly and everything works perfect.