Analysez vos données avec VBA

Vous venez de voir comment commencer à automatiser un reporting.

Pour le moment, cette automatisation nécessite toujours l’intervention d’une personne physique qui va lancer la macro. Dans ce nouveau chapitre, nous allons voir comment tout cela peut se faire de façon automatique pendant que vous êtes en train de prendre votre café ! 😉

Automatisez l'exécution de vos scripts

Pour continuer à pousser l’automatisation plus loin, nous allons utiliser des fonctions qui permettent de s'exécuter en fonction de certains évènements.

Jusqu’à maintenant, vos macros étaient dans les modules, mais vous pouvez également les insérer dans des feuilles ou dans “ThisWorkbook”.

Les événements sont définis dans le VBE et nous ne pouvons pas en créer. Nous allons donc utiliser les événements qui sont mis à votre disposition dans le VBE. Certains sont associés à la feuille (événement Worksheet) comme :

• BeforeDoubleClick ;

• Calculate ;

• Activate ;

• SelectionChange ;

• FollowHyperlink.

 D’autres sont associés au classeur (événement Workbook), par exemple :

• NewSheet ;

• Open ;

• BeforeClose ;

• BeforePrint ;

• BeforeSave ;

• SheetActivate ;

• WindowsActivate.

Pour voir les différents événements, il faut déjà choisir si ce sera un événement lié à une feuille ou au classeur. Dans notre cas, il sera lié à l’ouverture du classeur Excel.

Vous allez donc :

• aller dans “ThisWorkBook” ;

• cliquer sur “Général” ;

• choisir “Workbook” dans la liste ;

• aller encore à droite pour choisir “Open”.

Vous devez normalement obtenir un résultat similaire :

Le VBE vous a automatiquement créé une nouvelle fonction avec différentes variables.

Tout à fait ! Cela va donner un code très simple :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

‘code à mettre ici

End Sub

Automatisez la mise à jour de vos données

Nous allons maintenant modifier notre code du chapitre précédent pour qu’il se lance automatiquement à l’ouverture. Nous allons également en profiter pour ajouter un petit reporting. Cela va nous permettre de connaître le statut de la mise à jour (vu qu’elle va se faire toute seule, il nous faut bien un indicateur), en regardant la colonne “État” qui pourra prendre la valeur “Fait”, si le reporting a déjà été fait.

Avant de lancer le reporting, j’ai rajouté une table de paramétrage sur le fichier Excel pour le mois de mai 2023 :

Cette table va permettre au code de venir chercher quel jour nous sommes et de voir si, ce jour-là, nous souhaitons mettre à jour le reporting en utilisant la colonne E “Statut du reporting”. Si c’est “Oui”, alors nous lançons le reporting, sinon nous ne faisons rien.

Nous allons également utiliser la colonne G pour donner l’état de la mise à jour. Le programme écrit “Fait” dans cette colonne les jours où il a été lancé.

Je vous montre tout cela avec un screencast :

Effectivement, si nous nous arrêtons là, nous devons le lancer nous-mêmes et nous n’allons pas gagner tant de temps que cela.

Pour terminer cette automatisation, nous allons utiliser le planificateur de tâches de Windows. Cet outil Windows permet de paramétrer une tâche à une heure bien précise de la journée et d'y associer une action. Dans notre cas, cela sera l’ouverture de notre fichier de reporting.

Je vous montre comment faire avec ce screencast :

Et voilà ! Vous pouvez maintenant aller boire votre café directement en arrivant au bureau. Votre reporting se fera tout seul à 9 h !

Automatisez l’envoi de rapports par e-mail

Finalement, devant la machine à café à 9 h, vous vous demandez si votre script a vraiment fonctionné. Et s’il y avait une erreur, un problème ? Vous ne pourrez le savoir qu’au moment où vous allez retourner devant votre ordinateur et consulter le statut de la mise à jour dans le fichier reporting.

C’est pourquoi nous allons pousser l’automatisation encore un peu plus loin.

Nous allons maintenant nous envoyer un e-mail automatiquement afin d’avoir le statut de cette mise à jour directement sur notre téléphone.

Pour cela, il vous suffit d’instancier un nouvel objet "Outlook" avec le code :

Dim outlook As Object
Set outlook = CreateObject("Outlook.Application"

Paramétrez ensuite l’objet "Outlook" avec la méthode "CreateItem" qui a besoin de différents paramètres :

• Subject : le sujet de l’e-mail ;

• To : l’adresse ou les adresses e-mail auxquelles envoyer le fichier ;

• Body : le corps du message (on peut utiliser également HTMLBody pour pouvoir personnaliser un peu le texte) ;

• Display : si vous souhaitez voir l’e-mail avant de l’envoyer ;

• Send : qui permet d’envoyer l’e-mail ;

• Add (chemin de la pièce jointe) : pour ajouter une pièce jointe à l’e-mail.

Comme vous pouvez le voir, je n’ai pas fait de changement dans les autres sous-programmes. Nous modifions seulement le programme principal pour ajouter des fonctionnalités : au départ, lancer une mise à jour, puis faire un petit reporting et, pour finir, envoyer ce reporting par e-mail.

Bien sûr, nous pouvons ajouter encore des options et des fonctionnalités en utilisant les différentes méthodes et propriétés des objets. Dans notre cas, nous allons nous arrêter à un simple e-mail. Voici un lien vers le site de Microsoft pour connaître les autres possibilités.

À vous de jouer !

De retour après quelques jours de vacances, votre collègue de la Supply Chain revient vous voir. Votre macro lui a fait gagner un temps précieux !

De son côté, il se demande si vous pouvez l’aider afin que son fichier puisse envoyer automatiquement un e-mail avec le reporting, ainsi que les informations importantes (le CA, les quantités vendues, etc.). Il souhaite également qu’il y ait une petite message box (après l’enregistrement du fichier) qui demande s'il faut envoyer l’e-mail ou non.

Je vous laisse répondre aux demandes de votre collègue. Puis regardez le corrigé dans ce screencast :

En résumé

• Certains événements liés à une feuille ou au classeur peuvent déclencher un sous-programme et faire des actions spécifiques.

• On peut ajouter des paramètres afin que le programme sache lui-même s'il doit lancer les sous-programmes.

• Pour automatiser le lancement du programme, on peut utiliser le planificateur de tâches de Windows.

• Il est possible d’utiliser les autres logiciels de la suite Office, comme Outlook pour envoyer des e-mails à partir d'informations d’Excel.

Nous avons vu dans ce chapitre comment pousser l’automatisation de vos reportings en utilisant des événements, puis en lançant les fichiers Excel avec le planificateur de tâches Windows et, pour finir, nous avons appris à envoyer des e-mails en VBA.

Dans le prochain chapitre, nous allons nous concentrer sur la sécurité de notre VBE.