Looping Through Files to Run PowerShell Script

When working with PowerShell, there are two things to remember.

1. PowerShell is object-oriented.
2. PowerShell uses pipelines.

Here we have a variable and a loop. Both can be eliminated by switching to a pipeline.

Take care to read the help file for Get-ChildItem.

As Doug mentioned, the -Name parameter:

-Name

Gets only the names of the items in the location. The output is a string object that can be sent down the pipeline to other commands. Wildcards are permitted.

Here we go from a FileInfo object to a String. So we lose valuable information like the location of each file: $_.FullName This contributed to the issue with $i.fullname

Also, -Include requires a special wildcard in the -Path.

-Include

Specifies, as a string array, an item or items that this cmdlet includes in the operation. The value of this parameter qualifies the Path parameter. Enter a path element or pattern, such as "*.txt". Wildcard characters are permitted. The Include parameter is effective only when the command includes the contents of an item, such as C:\Windows\*, where the wildcard character specifies the contents of the C:\Windows directory.

Get-ChildItem won't produce any output unless you follow this pattern or (for some reason) include the -Name parameter. This might be why you started using the -Name parameter in the first place.

Final Answer

Get-ChildItem -Path "C:\PowerShell\BA\*" -Include "*.xlsx" | 
  ForEach-Object { 
    $xl = Open-ExcelPackage $_.FullName
    $sheet1 = $xl.Workbook.Worksheets[1]
    $sheet1.Name ="Sheet1"
    Close-ExcelPackage $xl
  }

If you have a large number of files and are able to install PowerShell 7, it might be possible to use the -Parallel parameter to speed up processing.

Get-ChildItem -Path "C:\PowerShell\BA\*" -Include "*.xlsx" | 
  ForEach-Object -Parallel { 
    $xl = Open-ExcelPackage $_.FullName
    $sheet1 = $xl.Workbook.Worksheets[1]
    $sheet1.Name ="Sheet1"
    Close-ExcelPackage $xl
  } -ThrottleLimit 5

If you're not a local admin, it is possible to get PowerShell 7 from the Store.

• Get PowerShell - Microsoft Store
• ForEach-Object (Microsoft.PowerShell.Core) - PowerShell | Microsoft Docs
• What's New in PowerShell 7.0 - PowerShell | Microsoft Docs

Interpreting Error Messages

Either C:\WINDOWS\system32\ or $env:USERPROFILE is the default working directory when PowerShell is loaded.

Looking at our path:

C:\WINDOWS\system32\11.25.2020_JH_BDX.xlsx

We have one default directory and one custom file.

This is an indication that we're only providing the file name to our function. E.g. $_.Name

In our case, the root cause of this error is Get-ChildItem -Name which produces only file names as strings. It would be necessary to qualify those file names per Walter. Or to remove -Name and use the FullName property of FileInfo per Doug / Itchydon.

Open-ExcelPackage : Cannot bind argument to parameter 'Path' because it is null.
$xl= Open-ExcelPackage $i.fullname

Here there are two clues.

One, we just changed

$xl= Open-ExcelPackage $file

to

$xl= Open-ExcelPackage $i.fullname

So, $i.fullname must be the issue.

Two, we can trace the logic of the error message backward.

it is null -> parameter 'Path' -> Open-ExcelPackage Cannot bind -> Open-ExcelPackage $i.fullname

The two most likely possibilities are that $i or $i.FullName never existed in the first place.

If we check, we see that $i has no FullName property.

Debugging

Set a breakpoint for the $xl= Open-ExcelPackage $i.fullname line in your script file.

For example,

1  # C:\users\michael\desktop\vim\demo 1.ps1
2  $file = Get-ChildItem -Path "C:\users\michael\desktop\vim" -Name -Include *.out
3  
4  
5  foreach ($i in $file )
6  {
7  $xl= Open-ExcelPackage $i.fullname
8  $sheet1 = $xl.Workbook.Worksheets[1]
9  $sheet1.Name ="Sheet1"
10 Close-ExcelPackage $xl
11 }

> # from the shell
> Set-PSBreakpoint -Script '.\demo 1.ps1' -Line 7

  ID Script            Line Command           Variable         Action
  -- ------            ---- -------           --------         ------
   0 demo 1.ps1           7

> & '.\demo 1.ps1'
Entering debug mode. Use h or ? for help.

Hit Line breakpoint on 'C:\Users\Michael\desktop\vim\demo 1.ps1:7'

At C:\Users\Michael\desktop\vim\demo 1.ps1:7 char:1
+ $xl= Open-ExcelPackage $i.fullname
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> $i | gm

   TypeName: System.String

Name             MemberType            Definition
----             ----------            ----------
Clone            Method                System.Object Clone(), System.Object ...
CompareTo        Method                int CompareTo(System.Object value), i...
Contains         Method                bool Contains(string value)
...

> # We have a string. 

> $i | Get-Member | Where-Object { $_.name -match "^f" }
> # No match for properties starting with the letter 'f'. 
> # So, strings don't have a 'FullName' property. 
> q # to quit the debugger

Cannot bind argument to parameter 'Path' ...
Cannot index into a null array.
The property 'Name' cannot be found on this object...
Close-ExcelPackage : Cannot bind argument ...

Oftentimes resolving the first error will either fix the code or make the larger issue apparent.

For example, we know that $sheet1 = $xl.Workbook.Worksheets[1] (which depends on $xl being defined) won't work if $xl= Open-ExcelPackage $i.fullname didn't work.

There isn't much point in looking at the second, third, fourth, etc errors until the first one is resolved.

Pipelines

The two things to understand about pipelines are that:

1. Cmdlets are connected with pipes | (ASCII 124) and
2. An automatic variable is used to represent the current object being processed: $_

This gives us the basic tools to connect cmdlets cmdlet | cmdlet and to refer to the properties of the current object being processed ForEach-Object { $_.FullName }