CODEWITHSUNDEEP
excelpowershell
Garry
Garry

Reputation: 35

PowerShell find next empty column in Excel

I need a PowerShell snippet that searches for the next empty column in an Excel sheet.

Every help is appreciated.

I've tried this so far:

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$WorkBook = $Excel.Workbooks.Open("C:\Users\Garry\Desktop\test.xlsx")
    $Sheet = $Excel.WorkSheets.Item(1)
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastColumn
$z=1
Do{$z, $z++}until ($Sheet.Column.Item(1, $xlLastCell).Value -eq "")

I tried to loop it so it checks every column if there is a value with the do and until statement, but I always get the error "It is not possible to call a method for an expression that has the value NULL." The errorstatement refers to the Do until loop.

Upvotes: 2

Views: 2919

Answers (1)

f6a4
f6a4

Reputation: 1782

Change condition from "0" to "$null" and replace $xlLastCell with counter $z:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excel = New-Object -ComObject Excel.Application
$excel.visible = $true

$workBook = $excel.Workbooks.Open("C:\Users\Garry\Desktop\test.xlsx")
$workSheet = $excel.WorkSheets.Item(1)
# not needed $xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastColumn
$z = 1
Do {
    $z
}
until ( $workSheet.Cells.Item(1, $z++).Value2 -eq $null )

Upvotes: 1

Related Questions