CODEWITHSUNDEEP
excelpowershell
Emanuele Ciriachi
Emanuele Ciriachi

Reputation: 2264

Insert an empty row in an Excel document using PowerShell

I am trying to programmatically insert a new row at a specified place in my Excel sheet, and despite extensive searching around the web I was unable to find a way to achieve this.

The following code successfully inserts an empty column:

$xlShiftToRight = -4161

#Get list of files
$File = Get-ChildItem c:\path\to\file.xlsx

#Launch Excel
$XL = New-Object -ComObject Excel.Application

#Open the file and get the Data sheet
$WB = $XL.Workbooks.Open($File.Fullname)

$XL.Visible = $true
$Sheet = $WB.Worksheets.Item('Naira')

$objRange = $XL.Range("C3").EntireColumn
[void]$objRange.Insert($xlShiftToRight)

But the specular alternative - using EntireRow instead of EntireColumn does not.

QUESTION

How do I insert a row at after another specific row in an Excel document using PowerShell?

Upvotes: 2

Views: 5297

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

Changing just the range does not suffice. You also need to change the direction in which to move the existing cells (shifting to the right doesn't make much sense when you're inserting an entire row).

Change this:

$xlShiftToRight = -4161 

...

$objRange = $XL.Range("C3").EntireColumn 
[void] $objRange.Insert($xlShiftToRight)

into this:

$xlShiftDown = -4121

...

$objRange = $XL.Range("C3").EntireRow
[void]$objRange.Insert($xlShiftDown)

Upvotes: 5

Related Questions