Move columns in xlsx file using PowerShell

Question

PowerShell and Excel question here.

I have a few spreadsheets with about 9000 rows of data in each.

I want to swap column A with column B (and keep the data in both columns) and use PowerShell to do so.

I have done quite a bit of looking on google to see if this can be done, but nothing seems to be matching and jumping out at me. I have tried the following but it throws an error saying

Unable to get the Paste property of the Worksheet class.

$Sheets = Get-ChildItem -Path "<PATH HERE>" | Where-Object {$_.PSIsContainer -eq $false}

foreach ($Sheet in $Sheets) {
    $wb = $excel.Workbooks.Open($Sheet.FullName)
    $ws = $wb.ActiveSheet

    $c = $ws.Columns
    $c1 = $c.Item(1)
    $c1.select()
    $cut = $ws.Cells.Cut()
    $c3 = $c.Item(3)
    $c3.Select()
    $ws.Paste($cut)
}

Basically I want to do the same as right click on a column A (selecting the column and showing the context menu), click 'Cut', then right click on column C (showing the context menu) and click 'Insert Cut Cells', to insert the column in position of column B.

Thanks in advance for any assistance.

Answer 1

Figured it out!

$Sheets = Get-ChildItem -Path "<PATH HERE>" | Where-Object {$_.PSIsContainer -eq $false}
foreach ($Sheet in $Sheets) {
    $wb = $excel.Workbooks.Open($Sheet.FullName)
    $ws = $wb.ActiveSheet

    $c = $ws.Columns
    $c.Item(1).Cut()
    $c.Item(3).Insert()

    $wb.Close()
}

Super simple in the end. I was too busy looking for 'Paste' instead of looking at 'Insert'. The column you are inserting on, automatically shifts to the right as per default behaviour when doing it through the GUI and right clicking then selecting 'Insert Cut Cells'