3 trucs indispensables pour gérer des fichiers avec une macro VBA

Publié le 24 novembre 2014
par Francis Paquet M.Sc., EEE
Macro

Le maniement de fichiers avec des macros VBA dans Excel est la base de l’extraction et de la manipulation de données dans Excel. En effet, avant de transformer, normaliser et manipuler les données, on doit d’abord y accéder. Cet article présente donc 3 trucs indispensables pour gérer vos fichiers à l’aide de macros VBA.

 

TRUC No. 1 pour gérer des fichiers avec une macro

Ouvrir un fichier dont on connaît l’emplacement et le nom

Dans ce cas le plus simple, on connaît déjà où se trouve le fichier convoité, ainsi que son nom, il devient alors facile de l’ouvrir, de faire les manipulations et, finalement, de le fermer.

Voici l’exemple en VBA :

Public Sub Ouvrir_Fichier_Connu()

Dim wbSource, wbFichierUsager As Workbook   ‘Déclarer les variables de base

Set wbFichierUsager = ThisWorkbook

‘ ThisWorkbook est toujours le fichier duquel la macro VBA s’exécute. C’est bon de l’assigner à une variable afin de passer d’un fichier à l’autre facilement lors des traitements requis

Workbooks.Open “C:\Directory\Subdirectory\File_Name.xlsm”   ‘Évidemment, vous devrez d’abord savoir où se trouve votre fichier dans votre environnement

‘  Il s’agit d’une procédure toute simple qui permet d’ouvrir un fichier dont on connaît l’emplacement et le nom

Set wbSource = ActiveWorkbook

‘On assigne une variable à ce fichier, dès l’ouverture, il devient le fichier actif

‘   Ici, on insère le code qui applique les changements voulus au fichier ouvert, qui sera ensuite refermé

wbSource.Close SaveChanges :=False  ‘On ferme le fichier sans le sauver

End Sub

Attention: Si vous copiez collez simplement le code ci-dessus dans un module VBA, vous obtiendrez des erreurs. C’est que WordPress et Excel n’affichent pas les apostrophes et les guillemets de la même façon. Vous devrez donc modifier les apostrophes et les guillemets manuellement. Aussi, pour insérer le code VBA dans votre fichier Excel, vous n’avez qu’à appuyer sur ALT+F11 et ensuite insérer un nouveau module via le menu “Insert”.

Ouvrir fichier avec VBA

 

TRUC No. 2 pour gérer des fichiers avec une macro

Ouvrir n’importe quel fichier

Le meilleur moyen d’accéder à un quelconque fichier est d’utiliser l’interface fourni par MS Office afin de nous faciliter la tâche. Il y a sans doute, à ce sujet, de nombreuses variantes, je vous montre celle que j’utilise de mon côté. Cette technique est utile quand le nom du fichier convoité change couramment de nom et peut même changer d’emplacement. Cette méthode est donc très flexible.

Voici donc le code VBA à cette fin :

Public Sub Ouvrir_Fichier_Quelconque()

Dim wbSource, wbFichierUsager As Workbook

Dim strFileName As String

Dim intChoice As Integer   ‘Déclarer les variables de base

Set wbFichierUsager = ThisWorkbook

‘ On va appeler une application de MS Office afin de chercher et d’ouvrir le bon fichier

‘  Avec la commande qui suit, on indique que nous ne voulons qu’un seul fichier

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False

‘  On affiche l’écran de dialogue de MS Office

intChoice = Application.FileDialog(msoFileDialogOpen).Show

‘  On s’assure que l’usager a fait un choix

If intChoice <> 0 Then

‘   On récupère le nom complet du fichier

strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

Workbooks.Open strFileName

Set wbSource = ActiveWorkbook

‘   Sinon, on arrête tout en notifiant l’usager

Else

‘ S’il n’y a pas de fichier, on quitte sans rien faire

MsgBox “La procédure est annulée car aucun fichier n’a été entré.”

Exit Sub

End If

‘ Ici, on insère le code qui applique les changements voulus au fichier ouvert, qui sera ensuite refermé

wbSource.Close SaveChanges :=False  ‘On ferme le fichier sans le sauver

End Sub

 

TRUC No. 3 pour gérer des fichiers avec une macro

Ouvrir, successivement, tous les fichiers contenus dans un répertoire

Il arrive souvent qu’on puisse déposer, au besoin, les fichiers qui demandent un traitement ou une extraction dans un répertoire défini d’avance. À ce moment, on ne connaît ni les noms exacts, ni le nombre de fichiers à traiter.

Une procédure VBA peut effectuer le travail d’ouvrir chacun des fichiers, de faire le traitement voulu, de le fermer, puis de passer au suivant. Encore une fois, il existe plusieurs méthodes pour un tel travail, je propose l’approche que j’utilise personnellement.

Voici donc ce code VBA :

Public Sub Ouvrir_Fichiers()

Dim wbSource, wbFichierUsager As Workbook

Dim strFileName, strPath, strSpec As String

Dim strFileList() As String

Dim i, FoundFiles As Integer   ‘Déclarer les variables de base

Set wbFichierUsager = ThisWorkbook

‘ On commence par identifier le chemin où les fichiers se trouvent

strPath =   “C:\Directory\Subdirectory\”  ‘ Évidemment, ce chemin sera différent dans chaque cas

strSpec = strPath & “*.xlsx”   ‘ Il faut spécifier l’extension des fichiers convoités

‘   On extrait le contenu du répertoire

strFileName = Dir(strSpec)

‘   Avons-nous des fichiers?

If strFileName <> “” Then

FoundFiles = 1

ReDim Preserve strFileList(1 To FoundFiles)

strFileList(FoundFiles) = strPath & strFileName

Else   ‘   Le repertoire est vide, donc on annule tout!

MsgBox “Aucun fichier trouvé”

Exit Sub

End If

‘   Trouver tous les autres noms de fichiers

Do

strFileName = Dir

If strFileName = “” Then Exit Do

FoundFiles = FoundFiles + 1

ReDim Preserve strFileList(1 To FoundFiles)

strFileList(FoundFiles) = strPath & strFileName

Loop

‘   On fait les traitements requis pour chaque fichier

For i = 1 To FoundFiles

Workbooks.Open strFileName:=strFileList(i)

Set wbSource = ActiveWorkbook

‘  Ici, on retrouve le code VBA afin de faire les traitements de ce fichier. Ensuite, on le ferme, sans le sauvegarder

wbSource.Close SaveChanges := False

Next i

End Sub

 

Comme vous pouvez le voir, les macros VBA peuvent être très puissantes afin de faciliter l’extraction de données provenant de fichiers externes à votre fichier principal, ce qui constitue souvent la première étape essentielle à l’automatisation de tout processus.


Formation complémentaire

Pour une introduction à la programmation avec le langage VBA, qui permet de sauver du temps et de réduire les risques d’erreurs en automatisant certaines tâches et certains processus, afin de développer des solutions robustes dans Excel, suivez la formation Excel – VBA (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

15 réflexions sur “3 trucs indispensables pour gérer des fichiers avec une macro VBA”

  1. Bonjour,
    il semble y avoir un problème au niveau du truc # 3, après le “ReDim Preserve”. S’agit-il vraiment la variable “FileList” (qui aurait été oublié dans la déclaration de variables) ou on devrait lire “strFileList” ?
    Aussi, j’ai essayé de la faire fonctionner mais il semble y avoir d’autres petits problèmes… (aucun End If au 2e If ?).
    Merci,

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour Patrick,

      En effet, il y avait une erreur dans FileList. Il faut utiliser la variable strFileList là où approprié.

      Par contre, le 2ème IF, comme l’expression est toute sur la même ligne, le END IF est superflu.

  2. Bonjour Francis,

    Au début je vous remercie pour cet article très utile pour les débutant comme moi .

    j’ai trois questions :

    1 – l’expression (strFileName = Dir) me semble incomplète je pense qu’il faut changer Dir par Dir(strSpec). si non, le programme ce bloque à ce niveau .
    2 – la macro tourne en boucle ici en mode débogage ( j’ai voulu essayer avant de passer à l’utilisation ) :
    If strFileName = ” ” Then Exit Do
    FoundFiles = FoundFiles + 1
    ReDim Preserve strFileList(1 To FoundFiles)
    strFileList(FoundFiles) = strPath & strFileName
    Loop
    3 – comment je peux introduire une variable dans le nom du fichier lors du sauvgarde .
    Exemple, à la fin de chaque traitement du fichier je l’enregistre avec le suffixe _AAAAMMJJ. est ce possible ?

    Merci infiniment pour votre aide

    PS : j’utilise Excel 2013 64bits

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour,

      Merci pour ce commentaire. Et voici mes réponses.
      1- Dans la macro, avant la boucle Do, vous remarquerez que la commande strFileName = Dir(strSpec) est belle et bien présente. Dans la boucle Do, on boucle afin d’extraire tous les noms de fichiers inclus dans le répertoire;
      2- Ici, nous avons de la difficulté à inclure la syntaxe exacte dans WordPress… Donc, la déclaration If strFileName = « » Then Exit Do doit considérer que « » est en fait un double guillemet;
      3- Si vous voulez sauvegarder le fichier, remplacez wbSource.Close SaveChanges := False par
      strFileName = strFileName & strDate ‘Vous devrez définir la date voulue dans la variable strDate
      wbSource.SaveAs Filename:=strFileName
      wbSource.Close

      Voilà!

  3. Bonjour,

    Quand j’essaie le Truc n°3, Excel me dit “Erreur de compilation Argument nommé introuvable” et me surligne la ligne suivante : Workbooks.Open strFileName:=strFileList(i) et plu précisment le strFileName:=

    Que dois-je faire pour résoudre ce problème

    Je débute dans les macros, c’est peut-être une simple erreur de débutant

  4. Bonjour Francis,

    Merci pour ces précieuses astuces.

    Le fichier macro doit être enregistrer à quel endroit? Pour lancer la macro, il suffit de double cliquer sur le fichier macro ou faut-il ouvrir le fichier macro et faire F8 ou “play” ?

    Merci pour votre réponse,
    LH

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour Laura,

      Ces macros doivent être enregistrées dans un module. Les modules font partie de l’environnement VBA. Afin d’ouvrir cet environnement, vous devez appuyer simultanément sur ALT+F11 quand vous êtes dans votre fichier Excel.

      Cela devrait ouvrir l’environnement VBA. Ensuite, vous devez insérer un module (Menu Insertion et choisissez Module). Cela devrait ajouter le Module1 dans la fenêtre de Projet VBA de votre fichier. Si vous double-cliquez sur Module1, vous avez maintenant accès à ce module. Copiez alors le code du billet de blogue dans la fenêtre de développement du module.

      Une fois créé, n’oubliez pas de sauvegarder le fichier comme un fichier Excel qui supporte les macros VBA. Pour accéder aux macros à partior du fichier Excel, allez au menu Affichage, choisissez Macros. Dans la fenêtre de dialogue qui va s’ouvrir, choisissez la macro désirée et appuyez sur Exécuter la macro.

      Voilà!

      Je vais vous envoyer quelques diapositives de mon cours de VBA afin de mieux illustrer ce processus.

      Bonne journée,

      Francis

      1. Bonjour Francis,

        Merci pour votre réponse, finalement j’ai trouvé une méthode qui m’a énormément simplifiée l’exécution de la macro. En effet, j’ai enregistré ma macro en “macro personnelle” ce qui fait qu’elle est toujours présente lorsque j’ouvre Excel puis je clique sur macro et elle est listée. Je n’ai plus qu’à ouvrir le fichier concerné et l’exécuter par ce biais.
        CDT
        LH

        1. Francis Paquet, M.Sc., ing., EEE

          Laura,

          Effectivement, les macros personnelles créent un fichier de procédures VBA qui est chargé à chaque fois que vous ouvrez EXcel et qui rend donc toutes ces macros disponibles.

          Francis

  5. Bonjour Francis et pour commencer merci pour l’aide que vous fournissez.
    J’ai un petit soucis avec le truc N°2.
    Je vous met ma macro qui s’est appuyé sur la votre:
    Sub traitementMES()

    Dim wbSource, wbFichierUsager As Workbook

    Dim strFileName As String

    Dim intChoice As Integer ‘Déclarer les variables de base

    Dim i As Integer
    Dim j As Integer
    Dim a As Integer
    Dim o As Integer
    Dim m As Integer

    a = 17
    i = 16
    j = 1

    ‘Ca c’est pour de la mise en forme par la suite

    Set wbFichierUsager = ThisWorkbook

    ‘On va appeler une application de MS Office afin de chercher et d’ouvrir le bon fichier

    ‘Avec la commande qui suit, on indique que nous ne voulons qu’un seul fichier

    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False

    ‘‘ On affiche l’écran de dialogue de MS Office
    MsgBox (“Choissisez le suivi des délais MES à traiter”)
    ‘j’ai mis ca car int choice ne foncionne plus MsgBox (“Attention!-Suite à un problème inconnu, un message d’erreur VBA risque de s’afficher par deux fois. Cliquez sur continuer pour passer outre les messages d’erreur.”)
    intChoice = Application.FileDialog(msoFileDialogOpen).Show

    ‘‘ On s’assure que l’usager a fait un choix

    If intChoice 0 Then

    ‘‘ On récupère le nom complet du fichier

    strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

    Workbooks.Open strFileName

    Set wbSource = ActiveWorkbook

    ‘‘ Sinon, on arrête tout en notifiant l’usager

    Else

    ‘‘ S’il n’y a pas de fichier, on quitte sans rien faire

    MsgBox (“La procédure est annulée car aucun fichier n’a été entré.”)

    Exit Sub

    End If
    ‘ Ici, on insère le code qui applique les changements voulus au fichier ouvert, qui sera ensuite refermé
    ‘La suite c’est donc pour de la mise en forme mais la macro est bloquée plus haut.
    Sheets(5).Select
    If Cells(i, j) “” Then
    While Cells(i, j) “”
    j = j + 1
    Wend
    Cells(i, j).Select
    ActiveCell.FormulaR1C1 = “Origine”
    j = j + 1
    Cells(i, j).Select
    ActiveCell.FormulaR1C1 = “Motif”
    End If
    ActiveSheet.Buttons.Add(745.2, 30.6, 170.4, 72).Select
    Application.CutCopyMode = False
    Selection.OnAction = “‘Traitement de l”EDC098.xlsm’!analysis”
    Selection.Characters.Text = “Traiter les Hors-délais Transport”
    With Selection.Characters(Start:=1, Length:=33).Font
    .Name = “Tahoma”
    .FontStyle = “Normal”
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 1
    End With
    Range(“A1”).Select
    MsgBox (“Commencer une série de HD ne permet pas de pause avec reprise par la suite; L’ensemble des HD devra donc être traité pour compléter les hors-délais”)
    End Sub

    La macro s’arrête sur intchoice identifiée comme -1 si je choisis un fichier excel et 0 si j’annule mais le message d’erreur VBA apparaît quoi qu’il advienne.
    Si vous avez une idée ca m’aiderait grandement.
    Merci d’avance.

  6. Francis Paquet, M.Sc., ing., EEE

    Roxelin,

    La syntaxe de la ligne de commande VBA doit être
    IF intChoice <> 0 then

    Êtes-vous certain que cette ligne est OK?

    Francis

  7. Oui c’était bien écris ainsi met après un redémarrage de l’ordinateur. Le problème a visiblement disparut de son propre chef. Je vous remercie en tout pour la réponse et tout le tuto de macro qui est fort utile.

  8. Bonjour Francis

    désolé de revenir sur ce post mais j’ai le même soucis que abdesslem sur le truc 3

    2 – la macro tourne en boucle ici en mode débogage ( j’ai voulu essayer avant de passer à l’utilisation ) :
    If strFileName = » » Then Exit Do
    FoundFiles = FoundFiles + 1
    ReDim Preserve strFileList(1 To FoundFiles)
    strFileList(FoundFiles) = strPath & strFileName
    Loop

    J’ai lu votre explication mais je n’arrive toujours pas régler le problème. Merci de votre aide

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut