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

Question

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

Ken King 21

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???

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

MotoX80 37,866

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}

0 comments

1 additional answer

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more