Question Gestion date Excel avec Powershell

Ah merde je suis vraiment désolé, merci beaucoup évidemment tu m'as sauvé la mise

Je te redonne le fichier de destination bien évidemment et tous le script avec ma dernière boucle if dans la fonction que je t'ai envoyée précédemment.

En fait sur ce fichier Excel tu as une colonne \"Cumul 2018\" et une autre \"Cumul 2019\". Je voudrais que si le temps d'utilisation, soit en 2018 soit en 2019, est supérieur à 100h, la cellule ou il y a le nom du logiciel soit remplie en vert et si le temps, encore une fois soit en 2018 ou en 2019, est inférieur à 10h, la cellule ou il y a le nom du logiciel soit remplie en rouge.

Merci d'avance

[code:1]

# InputBox pour inserer le bon mois
Add-type -AssemblyName \"Microsoft.VisualBasic\"
$Mois = [Microsoft.VisualBasic.Interaction]::InputBox(\"Entrez le mois et l'année au format 'Janvier 19'\", \"Nouvel mois\"«»)

# Si le nom du mois est vide on quitte le script
if ($Mois) {

# Fonction pour récupérer les données de la première colonne de chaque fichier
Function Get-WorkbookData($Workbook, $Row) {
$Workbook.Activate()
$Logiciel = $Workbook.ActiveSheet.Range(\"A\" + $Row).Text
while ($Logiciel.Length) {
$obj = @{
\"Logiciel\" = $Logiciel;
}
New-Object –Typename PSObject –Prop $obj
$Row++
$Logiciel = $Workbook.ActiveSheet.Range(\"A\" + $Row).Text -replace '(^\s+|\s+$)','' -replace '\s+',' '
}
}

# Fonction pour colorer les différences et de faire la mise à jour du fichier Destination
Function Update-Destination($LogicielToMark, $LogicielToCopy) {
$Source.Activate()
$Row = 1
$Logiciel = $Source.ActiveSheet.Range(\"A\" + $Row).Text
while ($Logiciel.Length) {
if ($LogicielToMark -contains $Logiciel) {
$Source.Activate()
$Source.ActiveSheet.Range(\"A\" + $Row).copy()
$Destination.Activate()
$lastRow = $wsD.UsedRange.rows.count + 1
$wsD.Range(\"A\" + $lastRow).select()
$Destination.ActiveSheet.Paste()
$wsD.Range(\"D\" + ($lastRow -1) + \":E\" + ($lastRow -1)).copy() # copier les formules pour calculer le Cumul
$wsD.Range(\"D\" + $lastRow).select()
$Destination.ActiveSheet.Paste() # coller les formules pour calculer le Cumul
$Source.ActiveSheet.Range(\"B\" + $Row).copy()
$wsD.Cells($lastRow, $lastCol + 2).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"C\" + $Row).copy()
$wsD.Cells($lastRow, $lastCol +1).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"A\" + $Row).Cells.Font.ColorIndex = 3
}
Else {
if ($LogicielToCopy -contains $Logiciel) {
$Source.Activate()
$CurrentRow = $wsD.Columns.item(1).find($Logiciel).row
$Source.ActiveSheet.Range(\"B\" + $Row).copy()
$Destination.Activate()
$wsD.Cells($CurrentRow , $lastCol + 2).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"C\" + $Row).copy()
$wsD.Cells($CurrentRow , $lastCol +1).select()
$Destination.ActiveSheet.Paste()
}


}
if ($Destination.ActiveSheet.Range(\"E\" + $Row) -ge 100) {
$Destination.ActiveSheet.Range(\"E\" + $Row).Cells.Font.ColorIndex = 3
}
$Row++
$Logiciel = $Source.ActiveSheet.Range(\"A\" + $Row).Text
}
}

# Fonction pour trier les plages de façon alphabétique
Function Tri-Plage($ws, $Row, $Cell) {
$lastCol = $ws.UsedRange.columns.count
$lastrow = $ws.UsedRange.rows.count
$objRange = $ws.Range($ws.Cells($Row, 1), $ws.Cells($lastrow, $lastCol))
$objRange1 = $ws.Range($Cell)
[void] $objRange.Sort($objRange1)
}

Add-Type -AssemblyName \"Microsoft.Office.Interop.Excel\" | out-null

# Ouvrir Excel
$ExcelApplication = New-Object -ComObject \"Excel.Application\"
$ExcelApplication.Visible = $true

# Ouvrir les deux fichiers à comparer
$Destination = $ExcelApplication.Workbooks.Open(\"E:\2K19\Scriptexcel\DESTINATION3.xlsx\"«»)
$Source = $ExcelApplication.Workbooks.Open(\"E:\2K19\Scriptexcel\graph_app_ZAV.xlsm\"«»)

# Définire les feuilles à comparer de chaque fichiers
$wsD = $Destination.Worksheets.item(1)
$wsS = $Source.Worksheets.item(1)

# Définire la dernière colonne et la dernière ligne
$lastCol = $wsD.UsedRange.columns.count
$lastrow = $wsD.UsedRange.rows.count

# Copier l'entête
$rng1=\"F1:G2\"
$rng2 = $wsD.Range($wsD.Cells(1 , $lastCol + 2), $wsD.Cells(2 , $lastCol +3))
$Destination.Activate()
$wsD.Range($rng1).Copy()
$wsD.Cells(1 , $lastCol + 1).select()
$wsD.Paste()
$wsD.Cells(1 , $lastCol + 1).value = $Mois

# Trier la feuille du document Destination et la feuille du document Source
Tri-Plage $wsD 3 \"A3\"
Tri-Plage $wsS 2 \"A2\"

# Récupérer les données des colonnes à comparer
$DestinationData = Get-WorkbookData $Destination 3 # à partir de la ligne 3 à cause de l'entête
$SourceData = Get-WorkbookData $Source 2 # à partir de la ligne 2 à cause de l'entête

# Comparer les données récuperer
$Comparison = Compare-Object -ReferenceObject $SourceData -DifferenceObject $DestinationData -Property \"Logiciel\" -IncludeEqual:$true

# Table d'hachage (les résultats de recherche sont stockés dedans)
$LogicielToMark = @()
$LogicielToCopy = @()
$Comparison | %{
if ($_.SideIndicator -eq \"<=\"«») {
$LogicielToMark += $_.Logiciel
}
Else{ if ($_.SideIndicator -eq \"==\"«»){
$LogicielToCopy += $_.Logiciel
}
}
}

# Exécution opérations coloration et mise à jour
Update-Destination $LogicielToMark $LogicielToCopy

# Trier la feuille du document Destination aprés la mise à jour
Tri-Plage $wsD 3 \"A3\"

# Ajuster le format de la feuille du document Destination
$lastrow = $wsD.UsedRange.rows.count
$objRange = $wsD.Range($wsD.Cells(3, 1), $wsD.Cells($lastrow , $lastCol +2))
7..12 | ForEach {
$objRange.Borders.Item($_).LineStyle = 1
$objRange.Borders.item($_).Weight = -4138
$objRange.Borders.item($_).Color = 0
}

# Sélectionner la cellule A1, c'est pas obligatoire
$wsD.Range(\"A1\"«»).select()

#$Destination.close($true) # Sauvegarder le fichier Destination
#$Source.close($true) # Sauvegarder le fichier Source
#$ExcelApplication.Quit() # Fermer Excel
Remove-Variable * -ErrorAction SilentlyContinue # Vider les variables pour pouvoir ré-exécuter le script si plusieurs mois doivent êtres ajoutés
[gc]::collect() # Terminer les Processus Excel
}
[/code:1]

Même avec un .Value, j'ai une erreur comme quoi ce n'est pas comparable

Slt Atou,
il faut absolument que tu rectifie ton fichier Destination!
T'as plusque 1650 lignes pleines de formules inutiles!
Donc il faut que tu supprimes les lignes entières (de la ligne 181 jusqu'à 1832) et aprés tu pourras travailler avec ce script sans soucis.

Si tu veux absolument garder ces lignes, il faut adapter ton script. Mais bon c'est toi qui decide.

Si tu ajoutes des colonnes \"Cumul\" il faut adapter ton script pour copier tes formules dans les nouvelles lignes!

Bon conceranant ta demande: voir le script

[code:1]# InputBox pour inserer le bon mois
Add-type -AssemblyName \"Microsoft.VisualBasic\"
$Mois = [Microsoft.VisualBasic.Interaction]::InputBox(\"Entrez le mois et l'année au format 'Janvier 19'\", \"Nouvel mois\"«»)

# Si le nom du mois est vide on quitte le script
if ($Mois) {

# Fonction pour récupérer les données de la première colonne de chaque fichier
Function Get-WorkbookData($Workbook, $Row) {
$Workbook.Activate()
$Logiciel = $Workbook.ActiveSheet.Range(\"A\" + $Row).Text
while ($Logiciel.Length) {
$obj = @{
\"Logiciel\" = $Logiciel;
}
New-Object –Typename PSObject –Prop $obj
$Row++
$Logiciel = $Workbook.ActiveSheet.Range(\"A\" + $Row).Text -replace '(^\s+|\s+$)','' -replace '\s+',' '
}
}

# Fonction pour colorer les différences et de faire la mise à jour du fichier Destination
Function Update-Destination($LogicielToMark, $LogicielToCopy) {
$Source.Activate()
$Row = 1
$Logiciel = $Source.ActiveSheet.Range(\"A\" + $Row).Text
while ($Logiciel.Length) {
if ($LogicielToMark -contains $Logiciel) {
$Source.Activate()
$Source.ActiveSheet.Range(\"A\" + $Row).copy()
$Destination.Activate()
$lastRow = $wsD.UsedRange.rows.count + 1
$wsD.Range(\"A\" + $lastRow).select()
$Destination.ActiveSheet.Paste()
$wsD.Range(\"D\" + ($lastRow -1) + \":G\" + ($lastRow -1)).copy() # copier les formules pour calculer le Cumul
$wsD.Range(\"D\" + $lastRow).select()
$Destination.ActiveSheet.Paste() # coller les formules pour calculer le Cumul
$Source.ActiveSheet.Range(\"B\" + $Row).copy()
$wsD.Cells($lastRow, $lastCol + 2).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"C\" + $Row).copy()
$wsD.Cells($lastRow, $lastCol +1).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"A\" + $Row).Cells.Font.ColorIndex = 3
}
Else {
if ($LogicielToCopy -contains $Logiciel) {
$Source.Activate()
$CurrentRow = $wsD.Columns.item(1).find($Logiciel).row
$Source.ActiveSheet.Range(\"B\" + $Row).copy()
$Destination.Activate()
$wsD.Cells($CurrentRow , $lastCol + 2).select()
$Destination.ActiveSheet.Paste()
$Source.ActiveSheet.Range(\"C\" + $Row).copy()
$wsD.Cells($CurrentRow , $lastCol +1).select()
$Destination.ActiveSheet.Paste()
}


}
$Row++
$Logiciel = $Source.ActiveSheet.Range(\"A\" + $Row).Text
}
}

# Fonction pour trier les plages de façon alphabétique
Function Tri-Plage($ws, $Row, $Cell) {
$lastCol = $ws.UsedRange.columns.count
$lastrow = $ws.UsedRange.rows.count
$objRange = $ws.Range($ws.Cells($Row, 1), $ws.Cells($lastrow, $lastCol))
$objRange1 = $ws.Range($Cell)
[void] $objRange.Sort($objRange1)
}

Add-Type -AssemblyName \"Microsoft.Office.Interop.Excel\" | out-null

# Ouvrir Excel
$ExcelApplication = New-Object -ComObject \"Excel.Application\"
$ExcelApplication.Visible = $true

# Ouvrir les deux fichiers à comparer
$Destination = $ExcelApplication.Workbooks.Open(\"E:\2K19\Scriptexcel\DESTINATION3.xlsx\"«»)
$Source = $ExcelApplication.Workbooks.Open(\"E:\2K19\Scriptexcel\graph_app_ZAV.xlsm\"«»)
# Définire les feuilles à comparer de chaque fichiers
$wsD = $Destination.Worksheets.item(1)
$wsS = $Source.Worksheets.item(1)

# Définire la dernière colonne et la dernière ligne
$lastCol = $wsD.UsedRange.columns.count
$lastrow = $wsD.UsedRange.rows.count

# Copier l'entête
$rng1=\"F1:G2\"
$rng2 = $wsD.Range($wsD.Cells(1 , $lastCol + 2), $wsD.Cells(2 , $lastCol +3))
$Destination.Activate()
$wsD.Range($rng1).Copy()
$wsD.Cells(1 , $lastCol + 1).select()
$wsD.Paste()
$wsD.Cells(1 , $lastCol + 1).value = $Mois

# Trier la feuille du document Destination et la feuille du document Source
Tri-Plage $wsD 3 \"A3\"
Tri-Plage $wsS 2 \"A2\"

# Récupérer les données des colonnes à comparer
$DestinationData = Get-WorkbookData $Destination 3 # à partir de la ligne 3 à cause de l'entête
$SourceData = Get-WorkbookData $Source 2 # à partir de la ligne 2 à cause de l'entête

# Comparer les données récuperer
$Comparison = Compare-Object -ReferenceObject $SourceData -DifferenceObject $DestinationData -Property \"Logiciel\" -IncludeEqual:$true

# Table d'hachage (les résultats de recherche sont stockés dedans)
$LogicielToMark = @()
$LogicielToCopy = @()
$Comparison | %{
if ($_.SideIndicator -eq \"<=\"«») {
$LogicielToMark += $_.Logiciel
}
Else{ if ($_.SideIndicator -eq \"==\"«»){
$LogicielToCopy += $_.Logiciel
}
}
}

# Exécution opérations coloration et mise à jour
Update-Destination $LogicielToMark $LogicielToCopy

# Trier la feuille du document Destination aprés la mise à jour
Tri-Plage $wsD 3 \"A3\"


# Ajuster le format de la feuille du document Destination
$lastrow = $wsD.UsedRange.rows.count
$objRange = $wsD.Range($wsD.Cells(3, 1), $wsD.Cells($lastrow , $lastCol +2))
7..12 | ForEach {
$objRange.Borders.Item($_).LineStyle = 1
$objRange.Borders.item($_).Weight = -4138
$objRange.Borders.item($_).Color = 0
}

for ($i = 3; $i -le $lastrow; $i++) {

for ($j = 5 ; $j -le 7; $j += 2){
$wsD.Cells($i , $j)|%{ if($_.value2 -ge 4.16666666666667){ $_.interior.colorindex = 4 } }
$wsD.Cells($i , $j)|%{ if($_.value2 -le 0.416666666666667){ $_.interior.colorindex = 3 } }
}
}

# Sélectionner la cellule A1, c'est pas obligatoire
$wsD.Range(\"A1\"«»).select()

#$Destination.close($true) # Sauvegarder le fichier Destination
#$Source.close($true) # Sauvegarder le fichier Source
#$ExcelApplication.Quit() # Fermer Excel
Remove-Variable * -ErrorAction SilentlyContinue # Vider les variables pour pouvoir ré-exécuter le script si plusieurs mois doivent êtres ajoutés
[gc]::collect() # Terminer les Processus Excel
}[/code:1]

Bon weekend

Salut,

Effectivement les formules pour faire le cumul sont dégueulasse . J'ai rectifier le tir

Par contre j'aurais une question concernant les lignes :
$_.value2 -ge 4.16666666666667
$_.value2 -le 0.416666666666667

Tu peux m'expliquer comment tu as fait pour savoir que 4.16667 était égal à 100 et 0.416667 était égal à 10h ?

Merci d'avance<br><br>Message édité par: Atou45, à: 18/03/19 07:08

Bjr,

Atou45 écrit:

Par contre j'aurais une question concernant les lignes :
$_.value2 -ge 4.16666666666667
$_.value2 -le 0.416666666666667

Tu peux m'expliquer comment tu as fait pour savoir que 4.16667 était égal à 100 et 0.416667 était égal à 10h ?

à l'aide de la règle de trois qu'on a aprise en 3ème primaire, plus exactement, chez mademoiselle Charlotte

x

---

&gt; 100h
1jr ---&gt; 24h

==&gt; x= 100h x 1jr : 24h
==&gt; x = 4,16666666666667 jrs

À plus

Ah oui d'accord Merci à Mme Charlotte de ma part

Merci beaucoup de ton aide