locked
Powershell script to skip a password protected excel workbook from search string and relocating to server RRS feed

  • Question

  • Hi!!

    Reference to the below link, excel script to find for particular content in a excel workbook and relocate the result to server is working to its extent, during the search process few of the excel workbooks are password protected, during the search process its gets hang on Password Entry option, unless we input the password or cancel the dialog box its gets hang on the same thing, i am trying to skip the password protected file from being searching and opening instead it would be better if the script finds password protected workbook and skips to continue next process..

    Any help please...

    https://social.technet.microsoft.com/Forums/windowsserver/en-US/67aec778-737a-415f-9706-1468e951149e/ps-script-to-find-for-string-in-excelworkbook-and-move-to-other-location?forum=winserverpowershell#d6c2afc7-16d0-4aaf-9a04-c345cf901530


    Mohammed...

    Wednesday, November 22, 2017 4:36 AM

Answers

  • Hi Mohammed,

    I totally agree with jrv, he provides the best solution and I always learned a lot from him. In addition, I rewrote the script to meet your needs. For your reference:
    Stop-Process -Name 'EXCEL' -ErrorAction SilentlyContinue
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $source = 'D:\source'
    $destination = 'D:\destination'
    $files = Get-ChildItem -Path $source | Where-Object {$_.Name -like '*.xls*'}
    foreach ($file in $files) {
        $catch = $false
        try {
            $sheets = $excel.Workbooks.Open($file.FullName, 0, 0, 5, $password).sheets
        }
        catch {
            Write-Host "$file is protected by password, skipping..." -ForegroundColor Yellow
            $catch = $true
            continue
        }
        if ($catch -eq $false) {
            $result = foreach ($sheet in $sheets) {
                $sheets.Item($sheet.Name).UsedRange | Where-Object {$_.Text -eq 'OT' -or $_.Text -match '^OT[- ].*' -or $_.Text -eq 'Resource' -or $_.Text -eq 'Security' }
            }
            if ($result -ne $null) {
                $close = $excel.Application.ActiveWorkbook.Close()
                Write-Host "$file contains keywords, moving file..." -ForegroundColor Green
                Move-Item -Path $file.FullName -Destination $destination
            }
            else {
                $close = $excel.Application.ActiveWorkbook.Close()
                Write-Host "$file does not contain keywords, skipping..." -ForegroundColor Yellow
            }
        }
        else {
            $close = $excel.Application.ActiveWorkbook.Close()
        }
    }

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, November 23, 2017 8:51 AM

All replies

  • I will repost this here:

    The usual method is to wrap the open method in a try/catch block.  You have to suppress all alerts and supply a dummy password.  This will throw an exception so you know the book is protected.  Unprotected  books ignore the dummy password.

    Example:

    Try{
        $excel.Workbooks.open($path, 0, 0, 5, $password)
    }
    Catch{
        Write-Host 'Book is password protected.'
    }


    \_(ツ)_/

    Wednesday, November 22, 2017 4:46 AM
  • Hi @JRV thanks for your reply...

    I am confused the inclusion of Try and catch function in the below script, can you please help on the below script...

    Stop-Process -Name 'EXCEL' -ErrorAction SilentlyContinue $excel = New-Object -ComObject Excel.Application $excel.Visible = $false $source = 'D:\source' $destination = 'D:\destination' $files = Get-ChildItem -Path $path | Where-Object {$_.Name -like '*.xls*'} foreach ($file in $files) { $sheets = $excel.Workbooks.Open($file.FullName).Sheets $result = foreach ($sheet in $sheets) { $sheets.Item($sheet.Name).UsedRange | Where-Object {$_.Text -eq 'OT' -or

    $_.Text -match '^OT[- ].*' -or $_.Text -eq 'Resource' -or $_.Text -eq 'Security' } } if ($result -ne $null) { $close = $excel.Application.ActiveWorkbook.Close() Move-Item -Path $file.FullName -Destination $destination -Verbose } else { $close = $excel.Application.ActiveWorkbook.Close() Write-Host "$file does not contain OT, Security, Resource" -ForegroundColor Yellow } }


    Mohammed...

    Wednesday, November 22, 2017 6:13 AM
  • You need to start by learning the basics of PowerShell.  You are demanding that we write your code one line at a time.  Take the time to learn and you will understand how to use the example I posted.


    \_(ツ)_/

    Wednesday, November 22, 2017 6:21 AM
  • Yes i started to learn powershell scripting, it is my present office's yesteryear manual work, i some how perceived the possibility via powershell, thanks to @Albert Ling, his replies made our work do now in automation manner,  i requested on disarray...thanks for the reply...

    Mohammed...

    Wednesday, November 22, 2017 6:37 AM
  • Hi Mohammed,

    I totally agree with jrv, he provides the best solution and I always learned a lot from him. In addition, I rewrote the script to meet your needs. For your reference:
    Stop-Process -Name 'EXCEL' -ErrorAction SilentlyContinue
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $source = 'D:\source'
    $destination = 'D:\destination'
    $files = Get-ChildItem -Path $source | Where-Object {$_.Name -like '*.xls*'}
    foreach ($file in $files) {
        $catch = $false
        try {
            $sheets = $excel.Workbooks.Open($file.FullName, 0, 0, 5, $password).sheets
        }
        catch {
            Write-Host "$file is protected by password, skipping..." -ForegroundColor Yellow
            $catch = $true
            continue
        }
        if ($catch -eq $false) {
            $result = foreach ($sheet in $sheets) {
                $sheets.Item($sheet.Name).UsedRange | Where-Object {$_.Text -eq 'OT' -or $_.Text -match '^OT[- ].*' -or $_.Text -eq 'Resource' -or $_.Text -eq 'Security' }
            }
            if ($result -ne $null) {
                $close = $excel.Application.ActiveWorkbook.Close()
                Write-Host "$file contains keywords, moving file..." -ForegroundColor Green
                Move-Item -Path $file.FullName -Destination $destination
            }
            else {
                $close = $excel.Application.ActiveWorkbook.Close()
                Write-Host "$file does not contain keywords, skipping..." -ForegroundColor Yellow
            }
        }
        else {
            $close = $excel.Application.ActiveWorkbook.Close()
        }
    }

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, November 23, 2017 8:51 AM
  • Hi @Albert Ling,

    Sorry for delay in response, am more grateful and much more thankful for you to your grateful replies all the time, its no need to mention the script is working, its working great to extent.

    The only issue i am facing is all my 3 scripts(except the Excel com object) runs soo quickly and finishes fine, but the above script only takes more time than expected time to get execute and search for every files... Is there anything to be improvise in the same script????sorry for one more favor...!!

    Thanks as always...


    Mohammed...

    Friday, November 24, 2017 1:55 PM
  • Hi Albert,

    Any suggestion\help please, as will be more helpful, my above work going to do in production environment for all the Client users, just the Where-Object {$_.Text -eq 'OT' -or $_.Text -match '^OT[- ].*'} taking more time to find or get executed..Other than if i do search with previous option like

     $sheetName = $sheet.Name
             $filter1 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword1")
             $filter2 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword2")
             If ($filter1.Text -match $keyword1 -or $filter2.Text -match $keyword2)

    Works rapidly...


    Mohammed...

    Saturday, November 25, 2017 7:33 AM
  • Hi Albert

    Instead of where-object function i am using the previous scripting as below

    Get-Process -Name EXCEL | Stop-Process -ErrorAction SilentlyContinue
    $source = 'C:\Source'
    $destination = 'D:\Destination'
    $keyword1 = 'OT'
    $keyword2 = 'Security'
    $keyword3 = 'Resource'
    $files = Get-ChildItem -Path "$source" | Where-Object {$_.Name -like '*.xls*' -and '*.xlsx*'}
    foreach ($file in $files)
    {
        $path = $file.FullName
        $objExcel = New-Object -ComObject Excel.Application
        $objExcel.Visible = $false
        $workBook = $objExcel.Workbooks.Open($path)
        $sheets = $workBook.Sheets
        $results = @()
        foreach ($sheet in $sheets)
        {
            $sheetName = $sheet.Name
            $filter1 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword1")
            $filter2 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword2")
            $filter3 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword3")
            if ($filter1.Text -eq $keyword1 -or $filter1.Text -match '^OT[- ].*' -or $filter2.Text -match $keyword2 -or $filter3.Text -match $keyword3)
            {
                $results += $true
            }
        }
          
        if ($results -contains $true)
        {
            $close = $workBook.Close()
            Move-Item -Path $path -Destination $destination -Verbose
        }
        else
        {
            $close = $workBook.Close()
            Write-Host $path do not contain $keyword
        }
        }

    It moves file normally without lag, but here too its seems me complicated and confusing to include try and catch function...Any help please..



    Mohammed...


    • Edited by Mohammed_Jaz Monday, November 27, 2017 12:41 PM Rectification
    Monday, November 27, 2017 12:28 PM
  • If you would take the time to learn PowerShell you would avoid constantly being stuck in a corner. 

    This is all you need to do.  Just adjust the "match" pattern to detect what you want.

    Stop-Process -Name 'EXCEL' -ErrorAction SilentlyContinue
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $source = 'D:\source\*.xls'
    $destination = 'D:\destination'
    $files = Get-ChildItem -Path $source
    foreach ($file in $files) {
        try {
            $wb = $excel.Workbooks.Open($file.FullName, 0, 0, 5, $password)
            if($wb.sheets | Where-Object { $_.UsedRange.Text -match '\bOT\b|Security|Resource' }){
                Write-Host "$file contains keywords, moving file..." -ForegroundColor Green
                Move-Item -Path $file.FullName -Destination $destination
            } else {
                Write-Host "$file does not contain keywords, skipping..." -ForegroundColor Yellow
            }
            $wb.Close()
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
        } 
        catch {
            Write-Host "$file is protected by password, skipping..." -ForegroundColor Yellow
        }
    }


    \_(ツ)_/

    Monday, November 27, 2017 12:49 PM
  • @JRV thanks for your valuable reply and sorry for keep on posting the doubts, I apologize for seeking help..

    I keen and started learning Powershell, i started from basic script, but as i mentioned in this trialing post, my intention for yesteryear manual work to do in automated process got reach to the point, for some reasons we engineer search for some keyword files and relocate to server as all those file are in client PCs.

    I managed to do via GUI in windows 2012 File Classification management, but because at client side it remained impossible, somehow powershell saved the life from repeating the same manual process, but alas i got trapped in excel options, also i have left is very few days to perform in production environment.

    The above script is working fine but my bad luck most of the files are not moving eg like (28.12.16 Resource Used,Resource,OT )are not getting moved, but suffix to "OT Month of,," , "SecurityAlert", "resourcefulness" are getting moved..




    Mohammed...


    • Edited by Mohammed_Jaz Monday, November 27, 2017 2:46 PM Inclusion
    Monday, November 27, 2017 2:45 PM
  • That is likely because you are using text comparison and not using the "Find" method.  If text is wrapped or has other issues then the match may fail.

    That is something you will have to figure out.  The match pattern will find all forms of the strings listed.  If they do not exist then the search will return nothing.


    \_(ツ)_/

    Monday, November 27, 2017 3:02 PM