PowerSheel. Watch window, open Excel, run macro, close Excel. Caught in Loop

Question

I am a complete beginner with PowerShell and have put together something that works for me but seems to get caught in a loop.

I want to monitor a folder and when a new file is placed in the folder I want to open an Excel file and run a macro then save the file and close Excel. The macro opens the new file and copies records into the Excel file then kills the new file.

Here is what I have put together for PowerShell.

$watcher = New-Object System.IO.FileSystemWatcher
$watcher.Path = "\172.31.112.30\Shared Mailing\Scorecard\IMB Review file\Report"
$watcher.EnableRaisingEvents = $true

$action = {
$excel = new-object -comobject excel.application
$filePath = "C:\IMB Review file\Report\imb report V01.xlsm"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
$worksheet = $workbook.worksheets.item(1)

$excel.Run("gettext")
$workbook.save()
$workbook.close()
$excel.quit()

}
Register-ObjectEvent $watcher 'Changed' -Action $action

When the new file is placed in the watched folder Excel file is opened and the macro is run successfully. The records are copied and then the new file is deleted from the folder and the Excel file is closed. At this point the Excel file is opened again and since the macro has already gotten rid of the new file the Excel file is closed. This continues opening and closing the Excel file until I close the PowerShell session.

What do I do to avoid looping???

Answer 1

It would appear that you are getting multiple events. Add a test-path and only call Excel if the file exists.

### If testing in ISE, unregister prior events 
Get-EventSubscriber |Unregister-Event

### SET FOLDER TO WATCH + FILES TO WATCH + SUBFOLDERS YES/NO
    $watcher = New-Object System.IO.FileSystemWatcher
    $watcher.Path = "C:\temp"
    $watcher.Filter = "*.txt"
    $watcher.IncludeSubdirectories = $false
    $watcher.EnableRaisingEvents = $true  

    ### DEFINE ACTIONS AFTER AN EVENT IS DETECTED
    $action = { 
        $path = $Event.SourceEventArgs.FullPath
        "$(Get-Date), Event triggered for {0}" -f $Event.SourceEventArgs.FullPath | write-host 
        $changeType = $Event.SourceEventArgs.ChangeType
        "$(Get-Date), $changeType, $path" | Write-Host
        if (test-path -Path $path) {
            Write-Host "The file exists, here are it's contents." 
            $data = Get-Content $path
            Write-Host $data
            Write-Host "You could call excel here to process the file."
            Write-Host "I am just going to delete it to indicate that it was processed."
            Remove-Item $path 
        } else {
            Write-Host "File not found. We already processed it." 
        }
        Write-Host "Event complete." 
     }    
### DECIDE WHICH EVENTS SHOULD BE WATCHED 
###  Register-ObjectEvent $watcher "Created" -Action $action
  Register-ObjectEvent $watcher "Changed" -Action $action
###   Register-ObjectEvent $watcher "Deleted" -Action $action
### Register-ObjectEvent $watcher "Renamed" -Action $action
   while ($true) {sleep 5}