top of page

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

bottom of page