Lancer des macros avec un fichier batch (en lot) 15


Comme tout le monde le sait, les macros VBA sont habituellement lancées à partir d'un fichier Excel. Si vous avez une procédure standard qui requiert l'exécution régulière de plusieurs macros situées dans plusieurs fichiers, vous devriez songer à utiliser un "fichier de lot" (batch file). Ce fichier vous permet d'utiliser toutes les avantages et caractéristiques du code VBA sans devoir ouvrir Excel.

Fichier batch – utilisation du VBScript

Pour créer un fichier de lancement d'une procédure, rien de plus simple. On va utiliser le langage VBScript mais pas de panique, aucune connaissance en plus de VBA ni aucun outil spécial n'est nécessaire.

Le fichier de script peut être crée dans n'importe quel éditeur texte (Notepad, PSPad, Notepad ++, et autres). Il suffit d'écrire votre code et sauvegarder le fichier avec le suffixe "VBS" – ce qui donne par exemple "MonCode.vbs".

Remarque pour les utilisateurs avancés: si, lors de l'exécution, un message d'erreur concernant "un caractère invalide à la position ligne:1, colonne:1" apparait, vérifiez que votre fichier est bien encodé en ANSI et non, par exemple en UTF-8.

Le code à utiliser dans le fichier batch

Dans le batch le plus simple, les seules variables à fournis sont le(s) chemin(s) de(s) fichier(s) Excel ainsi que le(s) nom(s) de(s) macro(s) à lancer. Les noms de macros sont nécessaires au cas où vous voulez lancer une macro spécifique. Si votre code VBA se trouve dans l'événement "Workbook_Open" de votre fichier Excel, il sera exécuté d'office et vous ne devez pas le spécifier.

Dans l'exemple qui suit, on va ouvrir le classeur "C:\Test\MonFichierExcel.xlsm" et lancer la macro "MacroTest1" qui se trouve dans ce classeur.

Option Explicit
On Error Resume Next
ExempleMacroExcel

Sub ExempleMacroExcel() 

  Dim ApplicationExcel 
  Dim ClasseurExcel 

  Set ApplicationExcel = CreateObject("Excel.Application") 
  Set ClasseurExcel = ApplicationExcel.Workbooks.Open("C:\Test\MonFichierExcel.xlsm") 
  
  ApplicationExcel.Visible = True   'les actions seront visibles. Pour tout lancer en arrière-plan, remplacer True par False
  ApplicationExcel.Run "MacroTest1" 'va lancer la macro "MacroTest1"
  ApplicationExcel.Quit 

  Set ClasseurExcel = Nothing 
  Set ApplicationExcel = Nothing 

End Sub

Comme vous pouvez le constater, c'est la ligne – ApplicationExcel.Run "MacroTest1" –  qui lance la macro. Dans cet exemple, les opérations s'exécutent de manière visible (en avant-plan). Si vous voulez que l'exécution soit invisible, changez "ApplicationExcel.Visible = True" en "ApplicationExcel.Visible = False"

Pour une simple utilisation, vous pouvez juste prendre le code ci-dessus et remplacer le chemin vers le fichier et le nom de la macro à exécuter.

Pour des utilisations un peu plus sophistiquées (ouverture de plusieurs fichiers, lancement de plusieurs macros etc.) vous pouvez vous baser sur les explications qui suivent.

Exemples avancés de fichiers "batch"

Il n'est pas trop compliqué de modifier le code de base pour qu'il corresponde à vos besoins. Vous pouvez manipuler notamment les instructions suivantes:

  • ApplicationExcel.Workbooks.Open – il est possible d'ajouter plusieurs arguments optionnels comme ouverture lecture-seule, mot de passe,… Cette méthode se comporte exactement comme en VBA. Plus de détails sur la méthode Workbooks.Open.
  • ApplicationExcel.Quit – cette instruction ferme Excel une fois que la macro est exécutée. Pour garder Excel ouvert, supprimez cette instruction.

Voici donc l'exemple pour exécuter deux macros dans le même fichier:

Option Explicit
On Error Resume Next
ExempleMacroExcel

Sub ExempleMacroExcel() 

  Dim ApplicationExcel 
  Dim ClasseurExcel 

  Set ApplicationExcel = CreateObject("Excel.Application") 
  Set ClasseurExcel = ApplicationExcel.Workbooks.Open("C:\Test\MonFichierExcel.xlsm") 
  
  ApplicationExcel.Visible = True   'les actions seront visibles. Pour tout lancer en arrière-plan, remplacer True par False
  ApplicationExcel.Run "MacroTest1" 'va lancer la macro "MacroTest1"
  ApplicationExcel.Run "MacroTest2" 'va lancer la macro "MacroTest2"
  'ApplicationExcel.Quit 

  Set ClasseurExcel = Nothing 
  Set ApplicationExcel = Nothing 

End Sub

L'exemple suivant ouvre deux classeurs Excel différents et lance une macro spécifiée dans chacun.

Option Explicit
On Error Resume Next
ExempleMacroExcel2

Sub ExempleMacroExcel2() 

  Dim ApplicationExcel 
  Dim ClasseurExcel 

  Set ApplicationExcel = CreateObject("Excel.Application") 
  
  'ouverture du premier classeur + lancement d'une macrolancement 
  Set ClasseurExcel = ApplicationExcel.Workbooks.Open("C:\Test\MonFichierExcel.xlsm") 
  ApplicationExcel.Visible = True  
  ApplicationExcel.Run "MacroTest1"
  ClasseurExcel.Close 'fermeture du premier classeur
  
  'ouverture de deuxième classeur
  Set ClasseurExcel = ApplicationExcel.Workbooks.Open("C:\Test\MonFichierExcel_2.xlsm") 
  ApplicationExcel.Visible = True  
  ApplicationExcel.Run "MacroTest2"
  
  'fermeture de Excel
  ApplicationExcel.Quit 

  Set ClasseurExcel = Nothing 
  Set ApplicationExcel = Nothing 

End Sub

Des avantages intéressants de ce procédé (utilisation de fichier .vbs) par rapport à l'utilisation d'un fichier Excel qui contient une macro dans l'événement Workbook_Open:

  • si vous devez modifier le code de la procédure, vous ne devez pas modifier vos fichiers Excel
  • le fichier .vbs ne contient que le code – il est indépendant des données qui pourraient se trouver dans le Classeur Excel
  • avec l'instruction ApplicationExcel.Quit, vous pouvez fermer l'Excel sans que la procédure soit interrompue – il est possible de continuer par exemple par une ouverture de fichier(s) Word, tout cela dans le même fichier batch. Vous pouvez même ré-ouvrir Excel plus loin dans le code et continuer d'autres manipulations de fichiers Excel. Contrairement à cela, si vous exécutez le code à partir d'un classeur, une fois Excel fermé, vous ne pourrez plus continuer.
  • il est assez simple de lancer une procédure via la ligne de commande

Si vous avez des questions, n'hésitez pas à les poser dans les commentaires.

 

57 0




Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.

15 commentaires sur “Lancer des macros avec un fichier batch (en lot)

  • Daniel

    Bonjour,
    Avec "ApplicationExcel.Quit", l'alerte pour sauvegarder les modifications apparait.
    Comment sauvegarder automatiquement les modifications ?

    Merci d'avance

      • excel-malin.com Auteur du billet

        En effet, si vous voulez modifier le fichier Excel même qui lance la macro, il faut le sauvegarder:
        1) soit comme vous l'avez trouvé – dans le fichier "batch"
        2) soit dans le fichier Excel même (soit à la fin de votre macro, soit avec l'event "Workbook_BeforeClose")

  • seb

    Bonjour,
    bon tuto,
    en avez-vous d'autres traitant du même sujet?
    avez-vous des sites de références parlant de ce langage?
    Merci et bonne journée
    Seb

    • excel-malin.com Auteur du billet

      Merci,
      pour le moment, il n'y a pas encore d'autres tutoriels concernant les batch et le VBS.

      Vous pouvez trouver d'autres infos (livres, sites,…) sur VBA dans la section Ressources. Si vous parlez de VBS, vous pouvez regarder ici

      Bonne journée à vous aussi.
      Martin

  • Boris

    Bonjour et merci pour le tuto

    Question subsidiaire : la macro lancée peut-elle résider ailleurs que dans le classeur ouvert ? idéalement dans le fichier .vbs ou un fichier .vba adjacent.

    Explication : je souhaite lancer une même macro sur plusieurs classeurs différents. Pour le moment la macro est copiée dans chaque classeur mais j'aimerais plus maintenable…

    • excel-malin.com Auteur du billet

      Bonjour Boris,
      désolé pour la réponse tardive, j'étais en congé…

      Oui, c'est possible. Vous pouvez stocker un macro dans un autre fichier .vbs. Après, il faut l'appeler via Shell plutôt que via ApplicationExcel.Run

      Il suffit d'ajouter ceci:
      Shell "wscript C:\MonDossier\MonScript.vbs", vbNormalFocus

      Deux remarques:
      – attention, cela ne marche pas si le chemin du fichier contient des espaces (" ")
      – si vous utilisez ce code dans un fichier Excel, il faut activer la référence "Microsoft Script Control"

      J'espère que cela répond à votre question.
      Bien à vous, Martin

      PS: j'ai trouvé encore cette approche: intéressant!
      http://stackoverflow.com/a/14235931/2646902

      • Lor

        Bonjour,

        Je voudrais exécuter une macro sur un autre fichier que celui dans lequel elle se trouve.
        Fichier1 avec macro
        Fichier2 sans macro
        Que faut-il que j'écrive dans le fichier vbs pour effectuer cela ?

        Merci pour votre site si bien expliqué !

        Cordialement,
        Lor

        • excel-malin.com Auteur du billet

          Bonjour Lor,
          cela dépend de la manière dont le Fichier1 (avec macro) reçoit habituellement (= en dehors VBS) l'information sur le Fichier2 (sans macro).

          Simplifions la chose en laissant tomber le VBS. On n'a que les deux fichiers (1 & 2). Si vous lancez le Fichier1, comment il sait qu'il doit traiter le Fichier2?
          – C'est dans son code VBA? A ce moment-là, le Fichier2 ne doit pas être mentionné dans le VBS.
          – La macro VBA dans le Fichier1 contient un dialogue qui demande à l'utilisateur de choisir Fichier2? Ici idem, pas besoin de mentionner le Fichier2 dans le code VBS
          – le nom de Fichier2 est un paramètre de la macro dans Fichier1? -> A ce moment-là, il faut, dans VBS, lancer le Fichier1 en indiquant le nom de la macro à lancer (dans le Fichier1) et le paramètre de ce macro (le nom du Fichier2).

          Le code serait alors quelque chose comme ceci:
          ApplicationExcel.Run "MacroTest1", "C:\MonDossier\Fichier2.xlsx"

          J'espère que cela vous aide. Bàv, Martin

  • yannick

    Bonjour,
    Je souhaiterais lancer un programme sas à partir d'un bouton d'un formulaire Excel.
    En fouillant les forums et le net j'ai pu voir que la syntaxe ressemblait a ca:
    shell ("chemin_du_logiciel_sas.exe" -sysin "chemin_du_programme_sas.sas" -log "je_ne_sais_pas_ce_que_c'est.log")
    Cependant je ne suis ni programmeur vba et encore moins sas j'ai uniquement des notions dans ces deux langages de programmation.
    Saurais-tu ce que signifie le -sysin et le -log ? J'ai pu lire que c'étais des paramètres, mais des paramètres de quoi? sas ? Excel ?
    Saurais-tu ou trouver de la documentation sur le sujet (tuto, livre,… peu importe) ? J'en est bien chercher mais en vain.
    Merci par avance
    Yannick

    • excel-malin.com Auteur du billet

      Bonjour Yannick,
      les "-sysin" et "-log" sont des paramètres de SAS.
      En fait, pour Excel, ce qui compte c'est la function Shell – ce qui est "dedans" n'a pas vraiment d'importance.
      Pour ces deux paramètres, le -log signifie l'emplacement du fichier log et le -sysin est le chemin vers le programme SAS à executer.

      Exemple du code VBA:

      Dim Commande as string
      Commande = """D:\Program Files\SAS\SASFoundation\9.2\sas.exe"" -sysin ""D:\TMP\PROG\test.sas"" -log ""D:\TMP\LOG\"""
      Call Shell(Commande)

      J'espère que cela t'aidera.
      Pour les tutoriels etc., cela depend fortement du fait si cela peut être en anglais ou pas. Essaie de googler "vba shell", "sas command line parameters" etc.

  • Jean Pierre

    Bonjour, Jusqu'ici j'étais resté sur 2003 car j'ai de nombreux petits programmes. Je viens d'installer 2016 tout en gardant 2003. Le problème c'est que le vbs d'ouverture de mon programme ne fonctionne qu'en xls et ne trouve pas xlsm. Depuis des heures de recherches impossible. Que ce soit le votre ci dessus ou le miens :
    Set Xl = CreateObject("Excel.application")
    Xl.Visible = True
    Set oWb = Xl.Workbooks.Open("c:\top000100.xlsm")
    Un immense merci si vous voulez bien m'aider et dans tous les cas pour votre travail

    • excel-malin.com Auteur du billet

      Bonjour Jean Pierre,
      premièrement, avoir deux version d'Excel/d'Office simultanément sur une machine mène obligatoirement, tôt ou tard, à des problèmes.
      A votre place, je songerais à désinstaller l'Office 2003. Normalement, tous vos programmes de 2003 devraient fonctionner sous 2016.

      Le problème de votre VBS est qu'il lance la version Excel qui est considéré par le système comme celle "par défaut". Ici, c'est visiblement l'Excel 2003 (c'est logique – il a été installé le premier sur votre PC).
      Et comme c'est Excel 2003 qui est lancé, c'est normal qu'il ne peut pas ouvrir un fichier .xlsm car pour Excel 2003, le format XLSM ne veut rien dire.

      Il n'y a pas vraiment de solution facile – la seule que je vois serait de modifier les Registres de votre machine. Mais ce n'est pas quelque chose de recommandable pour un utilisateur "lambda".

      Le code suivant lancé à partir de la ligne de commande devrait faire en sorte que l'Excel par défaut sur votre machine soit l'Excel 2016:
      "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE" /regserver

      Mais attention, ceci est sans garantie! A utiliser à vos risques et périls.

      Franchement, testez vos programmes 2003 dans Excel 2016 et si tout marche, désinstallez le 2003, vous vous éviterez des problèmes et des maux de tête!

      Cordialement, Martin

  • Iannace

    Bonjour,

    Merci pour votre tutoriel qui a pu m'éclaircir sur plusieurs points.
    Seulement, je suis toujours à la recherche d'une solution à mon problème.
    En fait je voudrais créer une vérification qui, comme son nom l'indique, vérifie l'existence d'un fichier. Cette vérification devra s'effectuer toutes les X secondes. Et si cette vérification s'avère positive, lancer une macro de mon fichier excel.

    J'espère avoir été clair et que vous avez une réponse à m'apporter car mes recherches ne sont pas très fructueuses..

    Merci d'avance,

    Nicolas

    • excel-malin.com Auteur du billet

      Bonjour Nicolas,
      ceci devrait faire l'affare…

      Option Explicit
      On Error Resume Next
      
      Dim Counter
      For Counter = 1 to 3
      	
      
      If FichierExiste("H:\TEMP\MonFichierTest.txt")  Then
       	Dim ApplicationExcel 
        	Dim ClasseurExcel 
      
      	Set ApplicationExcel = CreateObject("Excel.Application") 
        	Set ClasseurExcel = ApplicationExcel.Workbooks.Open("H:\TEMP\MonExcel.xlsm") 
        
        	ApplicationExcel.Visible = True   
        	ApplicationExcel.Run "MaMacro" 'va lancer la macro "MaMacro" dans le fichier Excel
        	ApplicationExcel.Quit 
      
      	Set ClasseurExcel = Nothing 
        	Set ApplicationExcel = Nothing 
      	WScript.Quit() 'mettre en commentaire si le script doit continuer à tourner
      
      Else
       
      End If
      
      wScript.Sleep 5000 '>>> A modifier: 1 seconde = 1000
      Next 
      
      
      Function FichierExiste(FilePath)
        Dim fso
        Set fso = CreateObject("Scripting.FileSystemObject")
        If fso.FileExists(FilePath) Then
          FichierExiste=CBool(1)
        Else
          FichierExiste=CBool(0)
        End If
      End Function
      

      A copier/coller dans un fichier ".vbs"
      Dans le code, il vous reste à adapter: les noms de vos fichiers et de la macro, le délai en secondes entre les tests, la durée pendant laquelle le script va tourner (= variable "Counter" 1 to Y, où "Y" est à multiplier par le nombre de secondes d'attente entre chaque test, dans mon exemple, cela fait 3 fois (Counter 1 To 3) 5 secondes (Sleep 5000) )…
      Voilà, j'espère que cela marche.
      Cordialement, Martin