CODEWITHSUNDEEP
excelpowershellworksheet
AdilZ
AdilZ

Reputation: 1197

Do an If statement relating to Excel Sheets existing

I want to be able to check if a Sheet or worksheet in Excel with name "ALPHA" (for example) exists or does not exist as part of my automated script.

My main goal is to be able to do something that functions like:

if ("ALPHA" exists) {Code A}

if ("ALPHA" does not exist) {Code B}

You know the standard If statement stuff

But I cant seem to figure out how to do it

I have Tried 

$file = "$path\excel.xlsx"

$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false

$wbk = $Exl.Workbooks.Open($file)
$wst = $wbk.worksheets | where {$_.name -eq "*ALPHA*"}

 If (Test-Path $wst) 

    {echo 1}

  Else {echo 2}

But all I get is:

Test-Path : Cannot bind argument to parameter 'Path' because it is null.

Part of the Requirements is that it needs to be able to work if Alpha is a variable from the 

ForEach (Alpha in Bet)

And the the fact that there might be other Sheets than just Alpha, and I dont want to trigger the code just by doing an "if -eq" or a "if -ne" affecting those sheets

Thanks to @mklement0 for the clarification and answer

It was much more simpler to implement than I thought possible

The Following is the Answer code that is what I was looking for:

 $wst = $wbk.worksheets | where {$_.name -like "*ALPHA*"}

 if ($wst) { Echo 1 }
 Else {Echo 2}

Upvotes: 0

Views: 848

Answers (1)

mklement0
mklement0

Reputation: 440657

From what I can tell, the only thing that needs to change is:

$wst = $wbk.worksheets | where {$_.name -eq "*ALPHA*"}

If (Test-Path $wst) { ... }

must be (note the use of -like instead of -eq, and the absence of Test-Path):

$wst = $wbk.worksheets | where {$_.name -like "*ALPHA*"}

if ($wst) { ... }

To use wildcard (pattern) matching, you must use the dedicated -like operator, not -eq.

If no worksheets match, $null is returned, so conditional if ($wst) will only evaluate to $true if at least 1 worksheet was returned.
Thus, be aware that $ws may actually be an array of worksheets, if more than one matched the name pattern.

(The Test-Path cmdlet is designed to work with PowerShell drive providers, which do not come into play here.)

Upvotes: 1

Related Questions