Add a column in excel sheet using powershell

Question

I want to add a column after a particular column number in excel sheet using Powershell. I am able to add it at starting of sheet, but couldn't insert after a specific column.

Perhaps this can help: gallery.technet.microsoft.com/office/… — Raf
– Raf, Commented Jul 3, 2014 at 9:47
I had checked this link earlier. Can u tell me what does -4161 represents? — Nipun
– Nipun, Commented Jul 3, 2014 at 9:52
Scripting guy puts it well "yes, minus 4161; go figure". This article will shine some light as the PS from my previous link is a direct adaptation: blogs.technet.com/b/heyscriptingguy/archive/2005/04/11/… — Raf
– Raf, Commented Jul 3, 2014 at 10:06

SteveB12345 · Accepted Answer · 2019-12-26 23:54:31Z

2

#This will insert a column at column R

$Excel = New-Object -ComObject  excel.application 
$ExcelWorkSheet = $ExcelWordBook.Worksheets.Add()
$ExcelWorkSheet.Name = "TestThis"
#do other things

$ColumnSelect = $ExcelWorkSheet.Columns("R:R")
$ColumnSelect.Insert()

answered Dec 26, 2019 at 23:54

SteveB12345

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexis-Emmanuel Haeringer · Accepted Answer · 2015-04-27 10:15:52Z

1

Alas, I agree, I have not found neither documentation or examples :-/ .
Nevertheless here is below how to insert a column 7th and give it a name:

(Get-ChildItem "*.xlsb")|
    foreach-object {
        $xl=New-Object -ComObject Excel.Application
        $wb=$xl.workbooks.open($_)
        $ws = $wb.worksheets.Item(1)
        $ws.Columns.ListObject.ListColumns.Add(7)
        $ws.Cells.Item(1,7) ='Comment'
        $wb.Save()
        $xl.Quit()
        while([System.Runtime.Interopservices.Marshal]::ReleaseComObject([System.__ComObject]$xl)){'released'| Out-Null}
    }

Best regards

answered Apr 27, 2015 at 10:15

Alexis-Emmanuel Haeringer

3144 silver badges13 bronze badges

2 Comments

Roman Over a year ago

And to add a row: $ws.rows.Listobject.ListRows.Add(1)

Roman Over a year ago

And, one must clear filters before running this add rows command or it will give an error.

Collectives™ on Stack Overflow

Add a column in excel sheet using powershell

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related