PowerShell : actualisation de fichiers Excel

Une liste de fichiers Excel à mettre à jour tous les matins ?
Un fichier qui met 30 minutes à se mettre à jour ?
Ce petit programme matinal peut facilement vous faire commencer votre journée de travail « intéressante » tard dans la matinée … Heureusement PowerShell est là !

Nous allons voir comment faire un script qui permet de mettre à jour une liste de fichiers Excel de manière automatique.

Pour commencer, assurez-vous d’avoir Powershell version 4.0 minimum d’installé, pour connaitre votre version suivez ce lien : IT-Connect.
Si ce n’est pas le cas, vous pourez le trouver ici : Microsoft.
Si c’est votre première aventure avec PowerShell, n’ayez crainte, c’est plutôt simple, vous pouvez suivre ce tutorial très complet en 4 parties : IT-Connect, les étapes importantes pour moi sont :

• La partie 2 étape 1 qui autorise l’exécution de scripts
• La partie 3 étape 5, pour éviter l’éditeur de texte et la ligne de commande pour débugger
• Et bien sur toute la partie syntaxe/opérateur (ça vous évitera de rester bloquer sur « Pourquoi mon signe = ne fonctionne pas ?! » … du vécu !).

J’ai choisi de stocker la liste des fichiers Excel à mettre à jour sur une base SQL, mais vous pouvez très bien le faire à partir d’un autre fichier Excel.

Voici ce que l’on veut réaliser avec notre script PowerShell :

• Récupérer la liste de fichiers à mettre à jour via une requête SQL
• Pour chaque fichier, l’ouvrir, lancer le « Actualiser tout » d’Excel, le sauvegarder et le fermer
• Récupérer la date de dernière sauvegarde des fichiers que l’on insérera dans notre base de données. A vous de mettre en place un moyen de consulter ces dates (requête tout les matins, SSRS, etc.). Si vous n’avez pas de serveur SQL sous la main et que vous êtes joueurs, vous pouvez écrire dans un fichier Excel ces dates via PowerShell 😉

Nous allons voir comment réaliser ces étapes une par une, avant de faire notre script complet.

Comment ouvrir, mettre à jour et fermer un classeur Excel avec PowerShell?

# On démarre Excel
$excel = new-object -comobject Excel.Application
# On lui laisse le temps de démarrer
Start-Sleep -s 3

# On paramètre Excel pour éviter toutes demandes à l'utilisateur
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false
$excel.Visible = $false
$excel.UserControl = $false
$excel.Interactive = $false

$missing = [System.Reflection.Missing]::Value

echo $("Traitement du fichier : C:\RefreshExcel\MonFichier.xslx")

# Les arguments passés : le fichier à la première position et à la 11ème position : Notify à faux
$excelworkbook = $excel.workbooks.Open("C:\RefreshExcel\MonFichier.xlsx",$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$false,$missing,$missing,$missing)
Start-Sleep -s 2

echo "Lancement du refresh"
$excelworkbook.RefreshAll()
Start-Sleep -s 2

echo "Sauvegarde"
$excelworkbook.Save()
Start-Sleep -s 2

echo "Fermeture du classeur"
$excelworkbook.Close()
Start-Sleep -s 2

$excel.Application.quit()
# On force manuellement la fin du processus Excel
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)){}
pause

Les points importants :

• Les « Start-Sleep –s 2 » après chaque actions Excel sont là car il arrive à Excel de s’emballer et de rendre la main un peu rapidement au script qui lance la ligne suivante alors que l’action n’est pas terminée. Surtout après le RefreshAll, car s’il est rapide, il va vouloir fermer le classeur avant d’avoir fini le rafraîchissement des données.
• L’ouverture du fichier avec le paramètre « Notify » à false : il sert à ignorer le message qui indique que le fichier est actuellement ouvert par quelqu’un, le script continuera en générant une erreur à la sauvegarde, mais sans stopper le traitement.
• La dernière ligne sert à forcer la fermeture de l’application Excel, qui a tendance à rester ouverte en tâche de fond.

Vous pouvez accéder à des fichiers sur votre disques dur, sur le réseau ou même sur SharePoint Online (en passant l’URL du fichier Excel)

Dernier point sur deux paramètres au niveau d’Excel à vérifier afin de ne pas avoir de soucis lors de l’exécution de ce script.
Si vous avez une connexion à des données externes :

Dans les propriétés de la connexion externe (onglet « Données », bouton « Connexions » puis « Propriétés » pour chaque connexion externe du fichier), s’assurer que l’option « Activer l’actualisation en arrière-plan » est décochée, sinon Excel n’attendra pas la fin de l’actualisation avant de rendre la main au script.

Si vous avez des requêtes Power Query, il y a une option à cocher dans l’onglet « Power Query », sur le bouton « Options » puis dans « Déclaration de confidentialité », cocher « Ignorer les niveaux de confidentialité et potentiellement améliorer les performances », cela est à faire pour chaque fichier.

Comment récupérer notre liste de fichiers grâce à une requête SQL en PowerShell ?

[string] $Server= "MonServeurSQL"
[string] $Database = "MaBase"
# La requête pour avoir la liste des fichiers à actualiser
[string] $SQLQuery= $("SELECT [fileName]
 FROM [Mabase].[refresh].[File]")

# On ouvre la connexion SQL
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery

$Reader = $Command.ExecuteReader()

 while ($Reader.Read()) {
 echo $("Traitement du fichier : " + $Reader.GetValue($1))
}

$Connection.Close()

Rien de bien compliqué pour cette partie, on pense bien à fermer la connexion en fin de script.

Petite variante si vous voulez exécuter une requête d’instruction :

[string] $Server= "MonServeur"
[string] $Database = "Mabase"

$ConnectionCmd = New-Object System.Data.SQLClient.SQLConnection
$ConnectionCmd.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$ConnectionCmd.Open()

$sqlUpdate = New-Object System.Data.SQLClient.SQLCommand
$sqlUpdate.Connection = $ConnectionCmd
$sqlUpdate.CommandText = "INSERT INTO dbo.[FileList] VALUES ('C:\RefreshExcel\MonFichier2.xslx')"

$sqlUpdate.ExecuteNonQuery()

$Connection.Close()

Comment récupérer la date de dernière sauvegarde d’un fichier Excel via PowerShell ?

# On démarre Excel
$excel = new-object -comobject Excel.Application
# On lui laisse le temps de démarrer
Start-Sleep -s 3

# On paramètre Excel pour activer toutes demandes à l'utilisateur
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false
$excel.Visible = $false
$excel.UserControl = $false
$excel.Interactive = $false

$missing = [System.Reflection.Missing]::Value

echo $("Traitement du fichier : C:\RefreshExcel\MonFichier.xslx")

# Les arguments passés : le fichier, à  la 3ème position : readOnly à faux et à la 11ème position : Notify à faux
$excelworkbook = $excel.workbooks.Open("C:\RefreshExcel\MonFichier.xlsx",$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$false,$missing,$missing,$missing)
Start-Sleep -s 2

# On va parcourir les propriétés du document Excel pour prendre la date de dernière sauvegarde et la rentrer dans notre table
$binding = "System.Reflection.BindingFlags" -as [type]
Foreach($property in $excelworkbook.BuiltInDocumentProperties)
{
 $pn = [System.__ComObject].invokemember("name",$binding::GetProperty,$null,$property,$null)

 if ($pn -eq 'Last save time'){
 $LastRefresh = [System.__ComObject].invokemember("value",$binding::GetProperty,$null,$property,$null)
 Echo ("Date de dernière sauvegarde : " + $LastRefresh)
 }
}
echo "Fermeture du classeur"
$excelworkbook.Close()

$excel.Application.quit()
# On force manuellement la fin du processus Excel
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)){}

On ouvre le fichier Excel de la même manière que précédemment.

Ensuite, on parcourt l’ensemble des propriétés du document et quand on passe sur la dernière date de mise à jour on l’affiche. Vous pouvez donc accéder à toutes les propriétés du document si vous connaissez leurs noms.

Nous avons toutes les étapes, passons au script final !

# On démarre Excel
$excel = new-object -comobject Excel.Application
# On lui laisse le temps de démarrer, sinon il peut faire des erreurs
Start-Sleep -s 3

#On paramètre Excel pour éviter toutes demandes à l'utilisateur
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false
$excel.Visible = $false
$excel.UserControl = $false
$excel.Interactive = $false

[string] $Server= "MonServeur"
[string] $Database = "MaBase"
# La requête pour avoir la liste des fichiers à actualiser
[string] $SQLQuery= $("SELECT [filePath]
 FROM dbo.[FileList]")

# On prépare une connexion pour faire nos insert de date de mise à jour
$ConnectionCmd = New-Object System.Data.SQLClient.SQLConnection
$ConnectionCmd.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$ConnectionCmd.Open()

# On ouvre une deuxième connexion pour aller lire notre liste de fichiers à mettre à jour
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()

# On ouvre notre Reader pour lire la liste des fichiers Excel
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()

$missing = [System.Reflection.Missing]::Value

 while ($Reader.Read()) {
 echo $("Traitement du fichier : " + $Reader.GetValue($1))
 # Les arguments passés : le fichier et à la 11ème position : Notify à faux
 $excelworkbook = $excel.workbooks.Open($Reader.GetValue($1),$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$false,$missing,$missing,$missing)
 Start-Sleep -s 2

 echo "Lancement du refresh"
 $excelworkbook.RefreshAll()
 Start-Sleep -s 2

 echo "Sauvegarde"
 $excelworkbook.Save()
 Start-Sleep -s 2

 echo "Fermeture du classeur"
 $excelworkbook.Close()
 Start-Sleep -s 2
}

# On refait un passage sur notre liste pour récupérer la date de dernière sauvegarde.
# On ne peut pas le faire dans la boucle précédente car si un fichier est bloqué par un utilisateur, il prendra la date de sauvegarde du fichier ouvert en local
$Reader.Close()
$Reader = $Command.ExecuteReader()

while ($Reader.Read()) {
 echo $("Traitement du fichier : " + $Reader.GetValue($1))
 # Les arguments passés : le fichier, à la 11ème position : Notify à faux
 $excelworkbook = $excel.workbooks.Open($Reader.GetValue($1),$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$false,$missing,$missing,$missing)
 Start-Sleep -s 2

 # On va parcourir les propriétés du document Excel pour prendre la date de dernière sauvegarde et la rentrer dans notre table
 $binding = "System.Reflection.BindingFlags" -as [type]
 Foreach($property in $excelworkbook.BuiltInDocumentProperties)
 {
 $pn = [System.__ComObject].invokemember("name",$binding::GetProperty,$null,$property,$null)

 if ($pn -eq 'Last save time'){
 $LastRefresh = [System.__ComObject].invokemember("value",$binding::GetProperty,$null,$property,$null)

 # On insère la date de dernière sauvegarde dans notre table
 $sqlUpdate.CommandText = $("INSERT INTO dbo.[FileUpdateStatus] ([filePath],[LastRefreshDate]) VALUES ('"+$Reader.GetValue($1)+"','"+$LastRefresh+"')")
 $sqlUpdate.ExecuteNonQuery()
 }
 }

 echo "Fermeture du classeur"
 $excelworkbook.Close()
}

Echo "Fin des mises à jour, fermeture Excel et de la connection SQL"
$Connection.Close()
$excel.Application.quit()

# On force manuellement la fin du processus Excel
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)){}

Si vous rencontrez un problème récurrent sur un fichier, lancez le script en désactivant la partie qui masque entièrement Excel, comme ça vous aurez en affichage l’alerte que le script n’arrive pas à gérer automatiquement.

Si vous souhaitez recevoir une alerte mail dès qu’une erreur est rencontrée dans le script, tout en continuant l’exécution, vous pouvez mettre cette partie de code en haut de votre script :

Trap {
    $ErrorMessage = $_
    Send-MailMessage -From "RefreshAutoExcel@MyCompany.com" -To "VotreMail@MyCompany.com" -Subject "Erreur lors du RefreshAuto Excel :(" -SmtpServer MonServeurSMTP -Body "Erreur rencontrée : $ErrorMessage"
    Continue
  }

Et la vous allez me dire : « C’est bien beau tous ça, mais je suis obligé de cliquer tout les matins sur mon fichier PowerShell pour le lancer ?! »
Et bien même pas, un petit Tutorial rapide pour planifier notre script dans le planificateur de tâches Windows : Blog Développez.

Le mot de la fin

Rien ne vous empêche de vouloir créer un programme dans un langage plus robuste qu’un script PowerShell, une meilleure gestion des différents états d’Excel lors de l’ouverture d’un fichier, un gestionnaire d’erreur plus complet, etc.
Cependant, ce langage dispose pour moi de deux avantages majeurs : il est facile à mettre en place, livré avec Windows ou accessible via une simple installation et il ne nécessite pas de connaissances poussées en programmation, le script est facile à lire et permet de réaliser un grand nombre de tâches récurrentes dès les début de son apprentissage.
Si vous n’êtes pas développeur c’est quand même très pratique !

Je vous laisse trouver de nouvelles fonctionnalités pour enrichir ce script, gestion de version des fichiers, archivage automatique suivant les métadonnées du document … à vous de jouer !

Un petit lien vers l’article qui a été à l’origine de la création de ce script : NetNerds