I. Introduction▲
Cet article fait suite à l'article sur le passage de paramètres depuis Batch vers Accesspassage de paramètres depuis Batch vers Access. Pour aller plus loin dans la récupération de paramètres depuis batch, voici une suite proposée pour l'application Excel. Le but de cet article est de partir des exemples donnés avec la fonction /cmd pour multiplier les comportements à l'ouverture du classeur selon les paramètres qu'on lui passera. Elle permettra aux utilisateurs de gérer eux-mêmes des lancements automatiques totalement autonomes, sans une présence obligatoire devant la machine.
II. Avertissements▲
Pour cet article il est bon de définir une convention d'écriture. Le caractère " doit être scrupuleusement reproduit. L'utilisation de la touche F1 est vivement conseillée à tous les stades de l'utilisation d'EXCEL. L'amélioration constante de l'aide en fait un partenaire de choix dans l'apprentissage permanent d'EXCEL. Personnellement je ne peux m'en passer, ne serait-ce que pour mémoire.
III. Pré-requis▲
Bien qu'elle ne soit pas obligatoire, je recommande la lecture de l'article précédemment cité. Les fonctions VBA évoquées sont documentées dans l'aide en ligne ainsi que dans la FAQ à votre disposition ici : https://excel.developpez.com/faqDVP FAQ Excel.
IV. Exemple d'un besoin utilisateur▲
Plaçons-nous dans le cas d'un utilisateur travaillant dans la finance.
Il travaille avec des plates-formes boursières, qui lui délivrent des données tout au long de la journée.
Une journée type de ses activités avec une application Excel pourrait se dérouler de la façon suivante :
Il souhaite effectuer un traitement à 9h00, une récupération des prix de la veille, à la fermeture de la bourse de Paris. Il effectue sur des données des calculs puis les met à disposition des autres équipes.
A 11h00, il doit lancer d'autres traitements, pour la vérification des notations des entreprises, avec pour conséquence d'imposer des suspensions de validations, si certains critères ne sont pas respectés.
A 14h00, un export sous Outlook doit être généré avec les données de la matinée.
A 17h30, la valorisation des portefeuilles des gérants avant la fermeture de la bourse.
A 18h00, les données doivent être stockées sur le serveur des équipes risques pour une contre valorisation.
Bref, plutôt que d'imposer une présence continue et un lancement manuel de chacune des procédures en cours de journée, à heures précises, on veut pouvoir automatiser ces lancements avec des outils simples à mettre en place.
V. Utilisation des tâches planifiées▲
Les exemples de créations de tâches planifiées sont déjà expliqués dans cette partie d'articleV. Utilisation des tâches planifiées
VI. Utilisation des fichiers batch▲
La fonction de base d'un fichier batch (qui s'exécute manuellement ou depuis un planificateur de tâches) est d'exécuter une suite de commandes DOS. L'article de LoufabPage perso de Fabrice CONSTANS indique les principales syntaxes auxquelles les utilisateurs ont affaire. Une ligne DOS se décompose basiquement en 2 parties :
- le logiciel A avec lequel on souhaite ouvrir un fichier B
- le répertoire du fichier B à exécuter avec le logiciel A
Le fichier batch porte l'extension .bat et s'exécute avec un double-clique sur le fichier.
VI-A. Comment créer un fichier batch▲
Un fichier batch est un simple fichier texte. Il peut être créé et rempli depuis n'importe quel éditeur de texte. Le cours de Victor Laurie à ce sujet https://windows.developpez.com/cours/ligne-commande/ est très complet.
VI-B. Outils de développement▲
Plusieurs outils sont possibles. Ma préférence va à NotePad++ : http://notepad-plus.sourceforge.net/fr/site.htm D'autres outils sont téléchargeables à l'adresse suivante : http://astase.com/produits/
VII. Ouverture d'Excel par un batch▲
Il est possible de lancer Excel en exécutant un fichier batch. La ligne de commande DOS basique est la suivante (ici pour Office 2003 - version 11):
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" "C:\temp\classeur1.xls"
Cela lance Excel, et Excel se charge d'ouvrir le fichier classeur1.xls, situé dans le répertoire C:\temp de la machine.
Il est possible d'ouvrir Excel en spécifiant de nombreux éléments, nommés commutateurs. La liste de ces commutateurs est disponible iciDescription des commutateurs de démarrage d'Excel
Exemple : si l'on souhaite ouvrir le fichier Classeur1 en lecture seule la ligne de commande sera la suivante :
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /r "C:\temp\classeur1.xls"
NB : Dans le cas où les noms de fichiers ou macros sont composés (existence d'espaces entre les mots), il est obligatoire de mettre les noms entre guillemets.
De plus, on constatera que les commutateurs sont placés avant le path du fichier que l'on souhaite ouvrir.
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" "C:\temp\classeur1.xls" /e
Cette ligne permettant de lancer le fichier Classeur1 sans afficher l'écran d'affichage Excel. C'est la ligne de commande par défaut qui est lancée lorsqu'on double clique sur un fichier Excel.
Il n'existe pas de commutateur dédié au passage de paramètres. Toutefois, nous utiliserons un de ces commutateurs valides pour faire passer les informations que l'on souhaite à Excel.
VIII. Exécution automatique dans Excel▲
Il est possible de lancer automatiquement une fonction, procédure ou encore un formulaire à l'ouverture, en utilisant l'évènement Open du classeur.
Si l'on souhaite ouvrir un formulaire, il suffit de rédiger la ligne suivante.
Private
Sub
Workbook_Open
(
)
NomduFormulaire.Show
End
Sub
De la même façon, il est possible de lancer des macros à l'ouverture, en les lançant depuis l'évènement Workbook_Open. Tout un processus alliant code VBA/génération de graphique pourra être possible. L'avantage de ce système est de pouvoir effectuer à chaque ouverture la même série de processus sans qu'une intervention de l'utilisateur soit forcément nécessaire. L'inconvénient d'une manipulation automatique à l'ouverture est qu'un processus peut avoir lieu à un moment critique, modifiant des données qui ne devraient pas l'être, à cause d'un lancement trop hâtif ou trop tardif.
IX. Passage de paramètres▲
On cherche donc à faire passer des informations à Excel afin de paramétrer son comportement. On verra dans le chapitre 8 les codes batch et VBA à mettre en œuvre pour influer sur le comportement du fichier à l'ouverture. En dehors d'une information stockée dans un fichier, une feuille ou dans le système, il est possible de faire passer directement les informations depuis la ligne de commande batch.
Cette information peut donc se résumer à une chaîne de caractères.
La fonction qui permet de récupérer le contenu de la ligne de commande Batch est basée sur la fonction GetCommandLine. Les détails à propos de cette fonction sont disponibles iciGetCommandLine Function.
Private
Declare
Function
GetCommandLine Lib
"kernel32"
Alias "GetCommandLineA"
(
) As
Long
Il existe deux versions principales de la fonction, une version Unicode et une version ANSI (principes de codage des caractères différents). Ici il s'agit de la version ANSI.
Cette fonction ne retourne qu'une variable de type Long. On passe donc par une fonction qui convertit cette variable en tableau de paramètres :
Private
Declare
Function
lstrlen Lib
"kernel32"
Alias "lstrlenA"
(
lpString As
Any) As
Long
Private
Declare
Function
lstrcpy Lib
"kernel32"
Alias "lstrcpyA"
(
lpString1 As
Any, lpString2 As
Any) As
Long
'fonction proposée par Tony Proctor sur le forum public de Microsoft : microsoft.public.vb.winapi
Private
Function
GetCmd
(
) As
String
Dim
lpCmd As
Long
lpCmd =
GetCommandLine
(
)
GetCmd =
Space
$(
lstrlen
(
ByVal
lpCmd))
lstrcpy ByVal
GetCmd, ByVal
lpCmd
End
Function
La fonction de départ comportera donc le code suivant
Private
Sub
Workbook_Open
(
)
Dim
monparam As
Variant
'déclare une variable
monparam =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
monparam) Then
'si la variable est nulle
If
Len
(
monparam) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
'code de traitement
End
If
End
If
End
Sub
La nouvelle étape est donc pour le développeur de mettre en place une nomenclature d'écriture, qui soit efficace et logique pour être naturelle, mais suffisamment complexe pour passer un grand nombre d'informations si nécessaire. L'information peut se limiter à un nom de formulaire à ouvrir, à une macro à lancer, à une procédure à exécuter… mais si d'un coup on veut passer les trois à la suite ? Il faut donc mettre en place un séparateur, qui délimitera chaque élément qu'on souhaitera faire passer en tant que paramètre.
X. Plusieurs comportements ?▲
Le comportement d'un fichier, quel drôle d'idée ! Et pourtant on peut trouver de bons exemples de ce qu'on pourrait définir comme comportement d'un fichier. Son autonomie, par exemple, où un fichier effectue une série de processus seul, sans autre intervention de l'utilisateur que son ouverture initiale. Des menus qui apparaissent ou non selon le compte utilisateur, selon son niveau d'accréditation. De la même façon, on peut imaginer le grossissement des polices d'écriture pour un utilisateur qu'on aura identifié comme déficient visuel.
Il est possible de paramétrer toutes ces propriétés dans Excel. Le développeur doit donc préparer la gestion en amont. Cette gestion des individus passe souvent par une feuille des utilisateurs. Mais il serait pratique qu'en cas d'absence d'administrateur, un simple fichier puisse contenir toutes ces informations et qu'Excel puisse y lire ces informations (qui je suis ? ce que je vais faire ? quels sont mes droits ?).
Bien que non présent nativement dans les fonctions Excel, l'auteur se propose d'utiliser les propriétés de traitement de chaînes de caractères pour imposer un comportement au fichier Excel dès son ouverture par le fichier batch.
XI. Codes VBA et Batch▲
XI-A. Idée générale▲
Il est possible de passer des paramètres depuis le fichier batch à la suite de la fonction /cmd.
Option | /cmd |
Ligne de commande | "C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd/parametres "C:\temp\classeur1.xls" |
Description | Ouvre le fichier en passant le paramètre spécifié. |
Ces paramètres sont ensuite récupérés par la fonction GetCmd() dans une fonction VBA, lancée à l'ouverture du fichier Excel.
XI-B. Fonction initiale▲
La fonction qui permet de récupérer les paramètres passés dans le batch est de la forme :
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
Debug.Print
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)
End
If
End
If
End
If
End
Sub
Les chapitres suivants montreront les exemples d'exécution de différents objets dans Excel. Y seront successivement indiqués les cas d'utilisation de ces codes, leur traitement dans l'évènement WorkBook_Open() et les résultats attendus.
XI-C. Exemples▲
XI-C-1. Activation d'une feuille▲
Ici, on souhaite activer une feuille du classeur, en passant le nom de la feuille dans le fichier batch.
La ligne de commande batch serait du type
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/feuil3 "C:\temp\classeur1.xls"
Le code VBA sera donc
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
ThisWorkbook.Worksheets
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)).Activate
End
If
End
If
End
If
End
Sub
On estimera que le nom de la feuille est bon. Une gestion d'erreur peut être intégrée dans l'évènement Workbook_Open().
XI-C-2. Lancement d'une macro▲
Ici, on souhaite lancer une macro dont on passera le nom en paramètre. La ligne de commande batch serait du type
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/MaFonction "C:\temp\classeur1.xls"
Le code VBA sera donc :
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
Application.Run
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)
End
If
End
If
End
If
End
Sub
A noter qu'il est nécessaire que la fonction soit dans un module et pas dans une page de code liée à une feuille ou au classeur.
XI-C-3. Lancement d'un formulaire▲
Ici, on souhaite ouvrir un formulaire dont on passera le nom en paramètre. La ligne de commande batch sera
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/MonForm "C:\temp\classeur1.xls"
Le code VBA sera donc :
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
VBA.UserForms.Add
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)).Show
End
If
End
If
End
If
End
Sub
A noter qu'une autre manière d'ouvrir un formulaire au démarrage est de le spécifier.
On a donc pu voir les principales exécutions issues des paramètres.
Le paragraphe suivant a pour but de complexifier le raisonnement en passant plusieurs paramètres.
XI-D. Combinaison de plusieurs paramètres de type d'exécution identique▲
L'idée est de séparer chacun des paramètres par un slash " / ", et de traiter tous les paramètres les uns à la suite des autres. La méthode employée par l'auteur ici n'est pas la plus usitée, le lecteur pourra bien évidemment trouver d'autres façons d'aborder le sujet.
XI-D-1. Exemple : Affichage des paramètres passés à l'utilisateur▲
Ligne de commande
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/A1/Jpcheck "C:\temp\classeur1.xls"
On utilise la fonction Split() pour récupérer les différents paramètres.
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
Dim
ListeParam As
Variant
Dim
i As
Integer
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
ListeParam =
Split
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
),"/"
)
For
i =
0
to
UBound
(
ListeParam)
MsgBox
"Paramètre n°"
&
i+
1
&
" : "
&
ListeParam
(
i)
Next
i
End
If
End
If
End
If
End
Sub
XI-D-2. Exemple : exécution de plusieurs macros▲
Ligne de commande
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/Macro1/Macro2 "C:\temp\classeur1.xls"
On utilise la fonction Split() pour récupérer les différents paramètres.
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
Dim
ListeParam As
Variant
Dim
i As
Integer
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
ListeParam =
Split
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
),"/"
)
For
i =
0
to
UBound
(
ListeParam)
Application.Run
ListeParam
(
i)
Next
i
End
If
End
If
End
If
End
Sub
XI-E. Plus loin dans la combinaison des paramètres ▲
XI-E-1. Paramètres de types d'exécution différents ▲
En partant de l'exemple donné plus haut, on peut pousser le raisonnement de l'exécution des paramètres selon leur type. On appliquera donc une nomenclature pour se retrouver.
Chaque paramètre sera précédé d'un identifiant mono caractère alphanumérique.
Par souci de compréhension, on prendra comme exemples :
Identifiant alphanumérique | Objet associé | Fonction de lancement VBA | Exemple |
---|---|---|---|
F | Formulaire (Form) | VBA.UserForms.Add().Show | FMonForm |
M | Macro | Application.Run | MMaMacro |
S | Feuille (Sheet) | Worksheets().Activate | SMaFeuille |
Etc. |
On lancera donc la ligne de commande suivante :
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/mMacro1 "C:\temp\classeur1.xls"
Le code VBA sera le suivant :
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
Dim
ListeParam As
Variant
Dim
i As
Integer
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
Select
Case
UCase
(
Left
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
),1
))
Case
"F"
:
VBA.UserForms.Add
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)).Show
Case
"M"
:
Application.Run
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)
Case
"S"
:
Worksheets
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
)).Activate
End
Select
End
If
End
If
End
If
End
Sub
XI-E-2. Exécution de plusieurs paramètres de types différents▲
De la même façon que pour la gestion de plusieurs paramètres de même type, on adapte les derniers exemples pour obtenir cette ligne de commande :
"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" /cmd
/mMacro1/fMonForm "C:\temp\classeur1.xls"
On souhaite donc d'abord lancer la macro MaMacro puis ouvrir le formulaire MonForm. Le code VBA sera donc le suivant :
Private
Sub
Workbook_Open
(
)
Dim
macmdline As
Variant
Dim
monparam As
Variant
'déclare une variable
Dim
ListeParam As
Variant
Dim
i As
Integer
macmdline =
GetCmd 'affecte la valeur de la ligne de commande
If
Not
IsNull
(
macmdline) Then
'si la variable est nulle
If
Len
(
macmdline) >
0
Then
'on s'assure qu'il y a eu une ligne de commande passée
If
InStr
(
macmdline, "/cmd"
) >
0
Then
macmdline =
Replace
(
macmdline, ThisWorkbook.FullName
, ""
, , , vbTextCompare)
monparam =
Split
(
macmdline, "/cmd"
)
ListeParam =
Split
(
Mid
(
monparam
(
1
), 2
, Len
(
monparam
(
1
)) -
3
),"/"
)
For
i =
0
to
UBound
(
ListeParam)
Select
Case
UCase
(
Left
(
ListeParam
(
i),1
))
Case
"F"
:
VBA.UserForms.Add
(
Mid
(
ListeParam
(
i),2
)).Show
Case
"M"
:
Application.Run
Mid
(
ListeParam
(
i),2
)
Case
"S"
:
Worksheets
(
Mid
(
ListeParam
(
i),2
)).Activate
End
Select
Next
i
End
If
End
If
End
If
End
Sub
XII. Conclusion▲
Il est donc possible pour un utilisateur de générer des fichiers batch qui passent des paramètres dans Excel, et que ceux-ci soient lus et compris par l'application. La nomenclature de programmation proposée dans cet article n'est qu'un exemple, libre à chacun d'adapter les idées de traitement des paramètres passés par le fichier batch.
XIII. Remerciements▲
Je tiens à remercier l'équipe de Developpez.com pour la qualité du site, Jean BALLAT, Morgan BILLY et Pierre FAUCONNIER pour la relecture de cet article, et de tous ceux qui contribuent à l'entraide autour du développement dans le cadre personnel et professionnel.