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”.
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 :
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,
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.
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
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à!
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
Bonjour Veryan,
C’est parce que ce n’est pas strFileName mais FileName 🙂
C’est un peu tard mais sait on jamais …
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
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
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
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
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.
Roxelin,
La syntaxe de la ligne de commande VBA doit être
IF intChoice <> 0 then
Êtes-vous certain que cette ligne est OK?
Francis
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.
trés interresant merci 🙂
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