#######################################################
# Create a Quick Reporting - PART 1
# *** Collecting files for the need ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\CollectingData.ps1
#######################################################
# UT2T75
# CA-CIB France - EQS.xlsx
# CA-CIB France - EQS.xlsx
# VDI.xlsx
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "COLLECTING EXCEL FILES --> SEARCH FOR SPECIFIC TEXT --> MERGE FILES --> CREATE FAST & FURIOUS REPORTING !!!" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
Write-Host "/// --- \\\" -ForegroundColor Cyan
# Input by User 1 :
Write-Host "What is the path folder please?" -BackgroundColor DarkMagenta
Write-Host "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\01 - Non Business hours"
$InputSourceFiles = Read-Host "Folder"
# Input by User 2 :
Write-Host "What is the extension file?" -BackgroundColor DarkMagenta
Write-Host "Start by '*' and end by '*' For example *.xlsx*" -ForegroundColor Cyan
$Extension = Read-Host "Extension file" #"*CA-CIB France - ISS.xlsx*"
Write-Host "Scanning Folders :" -ForegroundColor Cyan
# $SourceFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\01 - Non Business hours\"
$SourceFiles = "$InputSourceFiles" + "\"
$DestinationFiles0 = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\Remote access\03 - Reporting\"
$DestinationFiles = "$DestinationFiles0" + "Files\"
$SelectedFiles = "$DestinationFiles0" + "Selected Files\"
$RejectedFiles = "$DestinationFiles0" + "Rejected Files\"
# Cleaning 'Files' Folder if needed:
$CountFilesDest = (Get-ChildItem -Path $DestinationFiles -File -Recurse).Count # $DestinationFiles -Filter "*.xlsx"
If ( $CountFilesDest -eq 0 ) {Write-Host "No need to clean 'Files' Folder" -ForegroundColor Green}
else {
Write-Host "Start cleaning" $CountFilesDest "Files on 'Files' Folder" -ForegroundColor Green
Get-ChildItem $DestinationFiles -File | Remove-Item -Force
}
# Cleaning 'Selected Files' Folder if needed:
$CountFilesDest = (Get-ChildItem -Path $SelectedFiles -File -Recurse).Count # $SelectedFiles -Filter "*.xlsx"
If ( $CountFiles -eq 0 ) {Write-Host "No need to clean 'Selected Files' Folder" -ForegroundColor Green}
else {
Write-Host "Start cleaning" $CountFiles "Files on 'Selected Files' Folder" -ForegroundColor Green
Get-ChildItem $SelectedFiles -File | Remove-Item -Force
}
# Cleaning 'Rejected Files' Folder if needed:
$CountFiles = (Get-ChildItem -Path $RejectedFiles -File -Recurse).Count # $RejectedFiles -Filter "*.xlsx"
If ( $CountFiles -eq 0 ) {Write-Host "No need to clean 'Rejected Files' Folder" -ForegroundColor Green}
else {
Write-Host "Start cleaning" $CountFiles "Files on 'Rejected Files' Folder" -ForegroundColor Green
Get-ChildItem $RejectedFiles -File | Remove-Item -Force
}
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan
# Count Files:
$CountFilesSource = (Get-ChildItem -Path $SourceFiles -Filter "$Extension" -File -Recurse).Count
Write-Host "Start copying" $CountFilesSource "Files to 'Files' Folder" -ForegroundColor Green
# Copy files from multiples folder to one Location :
# Check L'existence d'un dossier... Sinon, Création du dossier
if (!(test-path -path $DestinationFiles)) {new-item -path $DestinationFiles -itemtype directory}
Get-ChildItem -path $SourceFiles -Filter "$Extension" -File -Recurse | Copy-Item -Destination $DestinationFiles -Force
Write-Host "End processing 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()
####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Finding Text On Files"
$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 processing files..." -ForegroundColor Cyan
$ScriptRoute = "P:\Test\Powershell\Script\Reporting\"
$ScriptName = "FindingTextOnFiles.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 Green
Break
}
}
####################################################### END PROMPT
#######################################################
# 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
#######################################################
# Create a Quick Reporting - PART 3
# *** Merging Selected Files ***
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Reporting\MergingSelectedFiles.ps1
#######################################################
# Write-Host "/// --- \\\"
# Write-Host "Start Merging Files"
# Write-Host "Minimize Windows and fill the prompt" -BackgroundColor Green
# Step 01 : Call Excel File
$excel= new-object -comobject excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$PathXlFile = $DestinationFiles0 + "_Fusion_Fichier_Excel.xlsm"
$classeur=$excel.workbooks.open($PathXlFile)
# Test pouir lancer une macro
$excel.Run('Fusion_classeur_Excel')
# get-process *excel* | stop-process -force
Write-Host "End Merging process" -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()
# END
Write-Host ""
Write-Host "Thank you for watching... All jobs have been performed successfully" -ForegroundColor Green