Powershell File Lock function is not opening excel File

Question

I've written a function to check if an excel file is being used/locked by another process/user in a shared network drive, and if used, to pause the script and keep checking till it's available as the next action is to move it out of it's folder. However when I'm using System.IO to read the file, it does not open the file. I've tested on my local drive and this does open the file, but does this not work in Network Drives?

$IsLocked = $True

Function Test-IsFileLocked {
    [cmdletbinding()]
    Param (
        [parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        [Alias('FullName','PSPath')]
        [string[]]$Path
    )
    Process {      
           while($isLocked -eq $True){
            If ([System.IO.File]::Exists($Path)) {
                Try {
                    $FileStream = [System.IO.File]::Open($Path,'Open','Write')
                    $FileStream.Close()
                    $FileStream.Dispose()
                    $IsLocked = $False
                }  Catch {
                    $IsLocked = $True
                    echo "file in use, trying again in 10 secs.."
                    Start-Sleep -s 10

                }           
            }
          }     
     }
}

This is where the code does not pick up/open the excel file in my function

$FileStream = [System.IO.File]::Open($Path,'Open','Write')

This is where the program calls the function.Loop through a folder of items in the network drive and if the item matches the pattern, then the function will be called to check if the file is in use:

$DirectoryWithExcelFile = Get-ChildItem -Path "Z:\NetworkDriveFolder\"

$DestinationFolder = "Z:\DestinationFolder\"

$pattern = "abc"
foreach($file in $DirectoryWithExcelFile){

if($file.Name -match $pattern){
 Test-IsFileLocked -Path $file
 $destFolder = $DestinationFolder+$file.Name
 Move-item $file.FullName -destination $destFolder
     break
  }
}

You are not handling the lock correctly – lloyd May 29 '19 at 03:44 — lloyd, May 29 '19 at 03:44

lloyd · Answer 1 · 2019-05-29T07:04:37.790

You have to place the close and dispose in the finally part of the try so if it throws an exception it disposes of the lock. There's no guarantee it's a file lock exception either so you are better to throw the exception, catch the exception you're expecting or write it out

$IsLocked = $True

Function Test-IsFileLocked {
    [cmdletbinding()]
    Param (
        [parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        [Alias('FullName','PSPath')]
        [string]$Path
    )
    Process {      
           while($isLocked -eq $True){
            If ([System.IO.File]::Exists($Path)) {
                Try {
                    $FileStream = [System.IO.File]::Open($Path,'Open','Write')
                    $IsLocked = $False
                }  Catch [System.IO.IOException] {
                    $IsLocked = $True
                    echo "file in use, trying again in 10 secs.."
                    Start-Sleep -s 10

                } Catch {
                    # source https://stackoverflow.com/questions/38419325/catching-full-exception-message
                    $formatstring = "{0} : {1}`n{2}`n" +
                                    "    + CategoryInfo          : {3}`n" +
                                    "    + FullyQualifiedErrorId : {4}`n"
                    $fields = $_.InvocationInfo.MyCommand.Name,
                              $_.ErrorDetails.Message,
                              $_.InvocationInfo.PositionMessage,
                              $_.CategoryInfo.ToString(),
                              $_.FullyQualifiedErrorId

                    $formatstring -f $fields
                    write-output $formatstring

                } finally {
                    if($FileStream) {
                        $FileStream.Close()
                        $FileStream.Dispose()
                    }
                }           
            }
          }     
     }
}

Edit: Path should be a string, not a string array unless you have multiple paths in which case rename to $Paths and loop through them $Paths| % { $Path = $_; #do stuff here }

Hi Lloyd thanks for proper try/catch structure, however the issue is the file $Path is not even being opened in this line " $FileStream = [System.IO.File]::Open($Path,'Open','Write')". It jumps straight to Catch[System.IO.IOException] — Jeevan Daniel Mahtani, May 29 '19 at 06:26
Why is path a string array? Shouldn't it look through $Path to get out each separate path? — lloyd, May 29 '19 at 07:02

Powershell File Lock function is not opening excel File

1 Answers1