Exécuter une macro à temps

Un cas très courant en pratique : vous devez exécuter une ou plusieurs de vos macros à un instant donné ou à une certaine fréquence. Par exemple, vous avez un rapport volumineux et lourd qui se met à jour une demi-heure et vous souhaitez exécuter la mise à jour une demi-heure avant d'arriver au travail le matin. Ou vous avez une macro qui doit envoyer automatiquement des e-mails aux employés à une fréquence spécifiée. Ou, lorsque vous travaillez avec un tableau croisé dynamique, vous souhaitez qu'il soit mis à jour à la volée toutes les 10 secondes, et ainsi de suite.

Regardons ce qu'Excel et Windows ont la capacité d'implémenter.

Exécuter une macro à une fréquence donnée

La façon la plus simple de le faire est d'utiliser la méthode VBA intégrée Application.OnTimeUn qui exécute la macro spécifiée à l'heure spécifiée. Comprenons cela avec un exemple pratique.

Ouvrez l'éditeur Visual Basic avec le bouton du même nom sur l'onglet promoteur (Développeur) ou raccourci clavier autre+F11, insérez un nouveau module via le menu Insertion – Module et copiez-y le code suivant :

Dim TimeToRun 'variable globale où la prochaine heure d'exécution est stockée 'il s'agit de la macro principale Sub MyMacro() Application.Calculate 'recalcule le livre Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cellule A1 avec une couleur aléatoire :) Appelez NextRun 'exécutez la macro NextRun pour définir la prochaine heure d'exécution End Sub 'cette macro définit l'heure de la prochaine exécution de la macro principale Sub NextRun() TimeToRun = Now + TimeValue("00 : 00:03") 'ajoute 3 secondes à l'heure actuelle Application.OnTime TimeToRun, "MyMacro" 'planifie la prochaine exécution End Sub 'macro pour démarrer la séquence de répétition Sub Start() Appel NextRun End Sub 'macro pour arrêter la séquence de répétition Sub Finish() Application.OnTime TimeToRun, "MaMacro", , False End Sub  

Voyons ce qui est quoi ici.

Tout d'abord, nous avons besoin d'une variable qui stockera l'heure de la prochaine exécution de notre macro - je l'ai appelée Temps d'exécution. Veuillez noter que le contenu de cette variable doit être disponible pour toutes nos macros suivantes, nous devons donc le rendre de défis, c'est-à-dire déclarer en tout début de module avant le premier Sol.

Vient ensuite notre macro principale MaMacro, qui effectuera la tâche principale - recalculer le livre en utilisant la méthode Application.Calculer. Pour le rendre plus clair, j'ai ajouté la formule =TDATE() à la feuille dans la cellule A1, qui affiche la date et l'heure - une fois recalculée, son contenu sera mis à jour sous nos yeux (il suffit d'activer l'affichage des secondes dans la cellule format). Pour plus de plaisir, j'ai également ajouté à la macro la commande pour remplir la cellule A1 avec une couleur sélectionnée au hasard (le code couleur est un entier compris entre 0 et 56, qui est généré par la fonction Rnd et arrondit à une fonction entière Int).

Macro Exécuter suivant s'ajoute à la valeur précédente Temps d'exécution 3 secondes de plus, puis planifie la prochaine exécution de la macro principale MaMacro pour cette nouvelle fois. Bien sûr, en pratique, vous pouvez utiliser tout autre intervalle de temps dont vous avez besoin en définissant les arguments de la fonction Valeur de temps au format hh:mm:ss.

Et enfin, juste pour plus de commodité, plus de macros de lancement de séquence ont été ajoutées. Accueil et son achèvement Finition. Le dernier utilise le quatrième argument de méthode pour rompre la séquence. À l'heure égal Faux.

Total si vous exécutez la macro Accueil, alors tout ce carrousel tournera, et nous verrons l'image suivante sur la feuille :

Vous pouvez arrêter la séquence en exécutant respectivement la macro Finition. Pour plus de commodité, vous pouvez attribuer des raccourcis clavier aux deux macros à l'aide de la commande Macros – Options languette promoteur (Développeur — Macros — Options).

Exécution d'une macro selon un calendrier

Bien sûr, tout ce qui est décrit ci-dessus n'est possible que si vous avez Microsoft Excel en cours d'exécution et que notre fichier y est ouvert. Examinons maintenant un cas plus compliqué : vous devez exécuter Excel selon un horaire donné, par exemple, tous les jours à 5h00, y ouvrir un rapport volumineux et complexe et mettre à jour toutes les connexions et requêtes afin qu'il soyez prêt au moment où nous arrivons au travail 🙂

Dans une telle situation, il est préférable d'utiliser Planificateur Windows - un programme spécialement intégré à n'importe quelle version de Windows qui peut effectuer des actions spécifiées selon un calendrier. En fait, vous l'utilisez déjà sans le savoir, car votre PC vérifie régulièrement les mises à jour, télécharge de nouvelles bases antivirus, synchronise les dossiers cloud, etc. C'est tout le travail du Scheduler. Notre tâche consiste donc à ajouter aux tâches existantes une autre qui lancera Excel et y ouvrira le fichier spécifié. Et nous accrocherons notre macro à l'événement Classeur_Ouvrir ce fichier - et le problème est résolu.

Je tiens à vous avertir tout de suite que travailler avec le planificateur peut nécessiter des droits d'utilisateur avancés, donc si vous ne trouvez pas les commandes et les fonctions décrites ci-dessous sur votre ordinateur de travail au bureau, contactez vos spécialistes informatiques pour obtenir de l'aide.

Lancement du planificateur

Commençons donc le planificateur. Pour ce faire, vous pouvez soit :

• Clic droit sur le bouton Accueil et choisissez Gestion de l'ordinateur (Gestion d'ordinateur)
• Sélectionnez dans le Panneau de configuration : Administration – Planificateur de tâches (Panneau de configuration — Outils d'administration — Planificateur de tâches)
• Sélectionnez dans le menu principal Démarrer – Accessoires – Outils système – Planificateur de tâches
• Appuyez sur le raccourci clavier Win+R, Entrer taskschd.msc et appuyez sur Entrer

La fenêtre suivante devrait apparaître à l'écran (j'ai une version anglaise, mais vous pouvez aussi avoir une version) :

Créer une tâche

Pour créer une nouvelle tâche à l'aide d'un simple assistant étape par étape, cliquez sur le lien Créer une tâche simple (Créer une tâche de base) dans le panneau de droite.

A la première étape de l'assistant, saisissez le nom et la description de la tâche à créer :

Cliquez sur le bouton Suivant (Prochain) et à l'étape suivante, nous sélectionnons un déclencheur - la fréquence de lancement ou un événement qui lancera notre tâche (par exemple, allumer l'ordinateur) :

Si vous avez choisi Tous les jours (Daily), puis à l'étape suivante, vous devrez sélectionner une heure, une date de début de séquence et une étape spécifiques (tous les 2 jours, 5 jours, etc.) :

L'étape suivante consiste à choisir une action - Exécutez le programme (Démarrer un programme):

Et enfin, la chose la plus intéressante est ce qu'il faut exactement ouvrir :

Dans le Programme ou script (Programme/script) vous devez entrer le chemin vers Microsoft Excel en tant que programme, c'est-à-dire directement vers l'exécutable Excel. Sur différents ordinateurs avec différentes versions de Windows et d'Office, ce fichier peut se trouver dans différents dossiers, voici donc quelques façons pour vous de trouver son emplacement :

• Faites un clic droit sur l'icône (raccourci) pour lancer Excel sur le bureau ou dans la barre des tâches et sélectionnez la commande Matériaux (Propriétés), puis dans la fenêtre qui s'ouvre, copiez le chemin à partir de la ligne Target:

                       

• Ouvrez n'importe quel classeur Excel, puis ouvrez Gestionnaire des tâches (Gestionnaire des tâches) poussant Ctrl+autre+Du et en faisant un clic droit sur la ligne Microsoft Excel, choisissez une commande Matériaux (Propriétés). Dans la fenêtre qui s'ouvre, vous pouvez copier le chemin, sans oublier d'y ajouter un antislash et EXCEL.EXE à la fin:

               

• Ouvrez Excel, ouvrez l'éditeur Visual Basic avec un raccourci clavier autre+F11, panneau ouvert Immédiat une combinaison de Ctrl+G, entrez-y la commande :

  ? Application.Chemin

  … et cliquez sur Entrer

  

  Copiez le chemin résultant, sans oublier d'y ajouter un antislash et EXCEL.EXE à la fin.

Dans le Ajouter des arguments (facultatif) (Ajouter des arguments (facultatif)) vous devez insérer le chemin complet vers le livre avec la macro que nous voulons ouvrir.

Lorsque tout est entré, cliquez sur Suivant et alors Finition (Finir). La tâche doit être ajoutée à la liste générale :

Il est pratique de gérer la tâche créée à l'aide des boutons à droite. Ici, vous pouvez tester la tâche en l'exécutant immédiatement (Cours)sans attendre le temps spécifié. Vous pouvez désactiver temporairement une tâche (Désactiver)afin qu'il cesse de fonctionner pendant un certain temps, comme vos vacances. Eh bien, vous pouvez toujours modifier les paramètres (dates, heure, nom de fichier) via le bouton Matériaux (Propriétés).

Ajouter une macro pour ouvrir un fichier

Maintenant, il reste à accrocher dans notre livre le lancement de la macro dont nous avons besoin sur l'événement d'ouverture de fichier. Pour cela, ouvrez le livre et rendez-vous dans l'éditeur Visual Basic à l'aide du raccourci clavier autre+F11 ou des boutons Visual Basic languette promoteur (Développeur). Dans la fenêtre qui s'ouvre dans le coin supérieur gauche, vous devez trouver notre fichier sur l'arborescence et double-cliquer pour ouvrir le module Ce livre (Ce cahier de travail).

Si vous ne voyez pas cette fenêtre dans l'éditeur Visual Basic, vous pouvez l'ouvrir via le menu Affichage — Explorateur de projet.

Dans la fenêtre du module qui s'ouvre, ajoutez un gestionnaire d'événements d'ouverture de livre en le sélectionnant dans les listes déroulantes en haut Classeur и Ouvert, respectivement:

Un modèle de procédure devrait apparaître à l'écran. Classeur_Ouvrir, où entre les lignes Sous-Privé и End Sub et vous devez insérer les commandes VBA qui doivent être exécutées automatiquement lorsque ce classeur Excel est ouvert, lorsque le planificateur l'ouvre conformément au calendrier. Voici quelques options utiles pour l'overclocking :

• ThisWorkbook.RefreshAll – Actualise toutes les requêtes de données externes, les requêtes Power Query et les tableaux croisés dynamiques. L'option la plus polyvalente. N'oubliez pas d'autoriser les connexions aux données externes par défaut et de mettre à jour les liens via Fichier – Options – Centre de gestion de la confidentialité – Options du centre de gestion de la confidentialité – Contenu externe, sinon, lorsque vous ouvrirez le livre, un avertissement standard apparaîtra et Excel, sans rien mettre à jour, attendra votre bénédiction sous la forme d'un clic sur le bouton Autoriser le contenu (Autoriser le contenu):

  

• ActiveWorkbook.Connections("Nom_Connexion").Actualiser — mise à jour des données sur la connexion Connection_Name.
• Feuilles("Feuille5").PivotTables("PivotTable1«).PivotCache.Actualiser – mise à jour d'un seul tableau croisé dynamique nommé Tableau croisé dynamique1 sur la feuille Sheet5.
• Application.Calculer – recalcul de tous les classeurs Excel ouverts.
• Application.CalculateFullRebuild – recalcul forcé de toutes les formules et reconstruction de toutes les dépendances entre les cellules de tous les classeurs ouverts (équivalent à ressaisir toutes les formules).
• Feuilles de calcul ("Rapport").PrintOut - feuille d'impression Photos.
• Appeler MaMacro – exécuter une macro nommée MaMacro.
• Ce classeur.Enregistrer – enregistrer le livre en cours
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace(Now, ":", "-") & ".xlsx" – enregistrer le livre dans un dossier D : Archiver sous le nom Photos avec la date et l'heure ajoutées au nom.

Si vous souhaitez que la macro soit exécutée uniquement lorsque le fichier est ouvert par le planificateur à 5h00, et non à chaque fois que l'utilisateur ouvre le classeur pendant la journée de travail, il est judicieux d'ajouter un contrôle de l'heure, par exemple :

Si Format(Maintenant, "hh:mm") = "05:00" Alors ThisWorkbook.RefreshAll  

C'est tout. N'oubliez pas d'enregistrer votre classeur dans un format prenant en charge les macros (xlsm ou xlsb) et vous pourrez fermer Excel en toute sécurité et rentrer chez vous, en laissant votre ordinateur allumé. A un moment donné (même si le PC est verrouillé), le Scheduler lancera Excel et y ouvrira le fichier spécifié, et notre macro effectuera les actions programmées. Et vous vous prélasserez au lit pendant que votre rapport lourd sera automatiquement recalculé – beauté ! 🙂

• Que sont les macros, comment les utiliser, où insérer du code Visual Basic dans Excel
• Comment créer votre propre macro complémentaire pour Excel
• Comment utiliser le classeur de macros personnelles comme bibliothèque pour vos macros dans Excel