CRAZYTRADER Forex : MT4 Indicators

#######################################################
#    Create a Quick Reporting - PART 2
#    ***   Search specific text into column  ***
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Reporting\FindingTextOnFiles.ps1
#######################################################



# Changing File extension ?
# $Pathfiles_ChangeExtension = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
# Dir $Pathfiles_ChangeExtension *.xlsm | rename-item -newname { [io.path]::ChangeExtension($_.name, ".xls") }

# Write-Host "/// --- \\\"
# Write-Host "Start processing files..."


# Input by User 1 :
Write-Host "What is the 'TEXT' are we looking for?" -BackgroundColor DarkMagenta
Write-Host "Exemple UT Code : UT2XJJ" -ForegroundColor Cyan
$SearchText = Read-Host "Search Text"


# Input by User 2 :
Write-Host "What is the SheetName?" -BackgroundColor DarkMagenta
Write-Host "Exemple : Feuil1" -ForegroundColor Cyan
$SheetName = Read-Host "SheetName"


Write-Host "Query is running..." -ForegroundColor Cyan

# Global Scope variable : (For memory)
# $DestinationFiles0 = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\03 - Reporting\"
# $DestinationFiles = "$DestinationFiles0" + "Files\"

# Déplace les fichiers .xlsx qui dont Search Text = "Input by user" dans la colonne D de l'onglet 1
$Pathfiles = $DestinationFiles # "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\03 - Reporting\Files\"
$SelectedFiles = "$DestinationFiles0" + "Selected Files\"

# Déplacer les fichiers en erreur :
$RejectedFiles = "$DestinationFiles0" + "Rejected Files\"
# Write-Host $folderDestination


$Excel = New-Object -ComObject Excel.Application

$Files = Get-ChildItem $Pathfiles"*.xlsx" | Select -Expand FullName
$counter = 1

$counterFileMoved = 0
ForEach($File in $Files){
    Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
    $Workbook = $Excel.Workbooks.Open($File)


        $WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "$SheetName"}
    # On traite les fichiers seulement si les fichiers contiennent la feuille en question
     if($WorkSheets) {


    $WorkSheet = $WorkBook.Sheets.Item("$SheetName")
    $Rows = ($WorkSheet.UsedRange.Rows).count
    $Range = "D1:D" + "$Rows"
    # Write-Host $Rows

    # If($Workbook.Sheets.Item(1).Range("E:E").Find($SearchText)){
    If($Workbook.Sheets.Item("$SheetName").Range($Range).Find($SearchText)){

    $counterFileMoved++
        # Pas de text trouvé, on ferme le fichier
        $Workbook.Close($false)
        # Si le texte recherché est trouvé alors on déplace les fichiers dans un nouveau dossier : )
        # Moved $file to $destination"
        Move-Item -Path $File -Destination $SelectedFiles -Force
        Write-Host $File -BackgroundColor Blue
        Write-Host " --> File has been moved to 'Selected Files' Folder" -BackgroundColor Green
        # break
        
    }

    else {
    $workbook.close($false)
    }



    # End $Worksheet
} Else { Write-Host "Warning :" -BackgroundColor Red
         Write-Host $File' --> This file has been rejected!'  -BackgroundColor Red
                # Déplacer les fichiers :
                  $Workbook.close($false)
                  Move-Item -Path $File -Destination $RejectedFiles -Force}


    $counter++
}


# Synthèse du traitement
Write-Host "Result :" -ForegroundColor Cyan
If( $counterFileMoved -eq 0 ){Write-Host $SearchText "has never been found !!!" -BackgroundColor Green} else

    {  
    $Resultat = $counterFileMoved
    Write-Host "Result :" -BackgroundColor Blue
    Write-Host "$Resultat" "files were moved to 'Selected Files' Folder !!!" -BackgroundColor Green
    }


    Write-Host "End Selecting Files" -ForegroundColor Cyan
    Write-Host "\\\ --- ///" -ForegroundColor Cyan




# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()


# Count Files:
$CountSelectedFiles = (Get-ChildItem -Path $SelectedFiles -Filter "*.xlsx" -File -Recurse).Count

If ( $CountSelectedFiles -eq 0 ) {Break} # Pas la peine de Merging si Zero file selected



####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Merging Files Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start Merging files..." -ForegroundColor Cyan

$ScriptRoute = "P:\Test\Powershell\Script\Reporting\"
$ScriptName = "MergingSelectedFiles.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process by user!" -ForegroundColor Cyan
    Break
    }
}
####################################################### END PROMPT