Analysez vos données avec VBA

Dans cette première partie, nous allons voir comment nous pouvons utiliser le VBA associé à Excel pour manipuler les fichiers, puis nous nous attarderons plus spécifiquement sur l’automatisation d’actions comme l'exécution des mises à jour ou encore des envois d’e-mails. Nous terminerons cette partie avec un focus sur la sécurité dans l’environnement et dans le code.

Interagissez avec les fichiers grâce à VBA

Vous avez raison, le VBA est majoritairement utilisé dans Excel. Cependant, vous pouvez aussi l’utiliser pour manipuler des fichiers. Vous vous demandez peut-être à quel moment cela peut arriver.

Imaginons un instant que vous deviez concaténer cinq fichiers dans un même fichier Excel. Il y a plusieurs options possibles pour faire cette action plus ou moins difficile :

• la version manuelle : ouvrir les cinq fichiers et faire un copier-coller de chaque fichier dans un nouveau fichier ;

• la version semi-automatique : ouvrir chaque fichier et exécuter une macro qui copie les données et les colle dans un nouveau fichier (automatisation simple en VBA) ;

• la version automatique : créer un programme qui vient lister les fichiers dans le dossier, qui ouvre tout seul les fichiers, qui copie et colle les données, qui ferme les fichiers et qui enregistre automatiquement le nouveau fichier contenant les données des cinq autres fichiers.

Eh bien non justement, nous allons voir que c’est même plutôt facile. 😉

Partons du principe que nous ne connaissons pas le nombre de fichiers que nous devons concaténer.

Prenons un peu de recul et décortiquons les différentes actions que nous devons réaliser :

1. Aller dans le dossier avec le VBA ;

2. Lister tous les fichiers que nous avons dans le dossier ;

3. Écrire un sous-programme qui va copier les données.

Listez, créez et déplacez des fichiers avec VBA

Commençons par voir comment nous pouvons lister les fichiers qui sont présents dans un dossier.

Pour ce faire, vous allez utiliser une fonction du VBA qui s’appelleDir().

Cette fonction permet de lister les différents fichiers d’un dossier automatiquement. Le seul argument dont la fonction a besoin, c’est le chemin du dossier dans lequel se trouvent vos fichiers.

Pour trouver le chemin de votre dossier sur votre ordinateur, allez dans le dossier qui contient les fichiers, puis cliquez dans la barre d’adresse.

Maintenant que nous avons le chemin du dossier, nous n’avons plus qu'à écrire le code qui va nous permettre de faire la liste.

Voyons cela dans ce screencast :

Sub Liste_fichier()
    Dim Chemin As String
    Dim Fichier As String
    Dim i As Integer

    'Initialisation de la variable
    i = 1

    'Choix du dossier à lister
    Chemin = "D:\Extraction\Data"
    Fichier = Dir(Chemin)

    'Boucle sur les fichiers xls du répertoire
    Do While Len(Fichier) > 0
        Range("A" & i).Value = Chemin & Fichier
        i = i + 1
        Fichier = Dir()
    Loop
End Sub

Dans le code ci-dessus, nous avons commencé par déclarer trois variables :

• "Chemin" : va contenir le chemin vers le dossier ;

• "Fichier" : va contenir le chemin vers le dossier et le nom du fichier ;

• "I" : pour créer notre boucle.

Avec cette fonction, nous n’avons pas besoin de connaître le nombre de fichiers qu’il y a dans le dossier. La particularité de la fonction Dir, c’est que, chaque fois qu’on l’appelle, elle s’incrémente automatiquement sur le fichier d'après.

Quand elle a fini, elle renvoie juste le chiffre 0. C’est pourquoi nous utilisons une boucle  Do While, car nous souhaitons boucler sur la fonction tant qu’elle n’est pas égale à 0.

Pour aller un peu plus loin, nous souhaitons maintenant déplacer les fichiers que nous venons de lister dans un dossier qui s’appellera “Fichiers traités”.

Pour cela, nous utilisons la commande  FileSystemObject  pour déplacer des fichiers et la commande  MkDir  pour créer un dossier.

La commandeMkDir est assez similaire à la commandeDir. Elle ne peut avoir qu’un argument, qui est le chemin que vous souhaitez créer.

Dans notre cas, nous allons utiliser ce code :

"I:\P2C1\Data\Fichiers_traités”

La fonction  MkDir  va alors simplement créer le dossier “Fichiers_traités”.

Pour finir, déplaçons les fichiers dans ce dossier avec l’objet "FileSystemObject".

Pour cela, commençons par déclarer cet objet avec les deux lignes ci-dessous :

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

Nous allons pouvoir maintenant utiliser plusieurs méthodes et propriétés sur cet objet.

Dans les méthodes intéressantes, nous avons par exemple :

• CopyFile ou CopyFolder : copier un fichier ou un dossier à un emplacement ;

• DeleteFile ou DeleteFolder : supprimer un fichier ou un dossier ;

• FileExists ou FolderExists : tester l’existence d’un fichier ou d’un dossier ;

• MoveFile ou MoveFolder : déplacer un fichier ou un dossier.

Nous n’avons plus qu'à utiliser cet objet pour déplacer nos fichiers avec la ligne de code :

FSO.MoveFile "I:\P2C1\Data\Reporting 2023S01.xlsx", "I:\P2C1\Data\Fichiers_traités\Reporting 2023S01.xlsx"

Nous utilisons la fonction  FSO.MoveFile. Nous donnons comme premier argument le chemin du fichier à déplacer, puis en second argument, le nouvel emplacement.

Dans ce cas, nous avons déplacé les fichiers un par un, mais nous pourrions être moins restrictifs en lui demandant de déplacer tous les fichiers avec un nom similaire, avec la même extension ou encore l’intégralité d’un dossier.

Pour aller plus loin, voici le code pour déplacer tous les fichiers avec un nom similaire :

FSO.MoveFile "I:\P2C1\Data\Reporting*.xlsx", "I:\P2C1\Data\Fichiers_traités\"

Nous avons utilisé l’étoile “*” pour lui spécifier que nous souhaitons qu’il déplace tous les fichiers du dossier qui commencent par “Reporting”.

Automatisez le traitement des données de plusieurs fichiers

Maintenant, vous savez lister des fichiers, créer des dossiers et déplacer des fichiers. Il ne reste plus qu’à rajouter le traitement à appliquer.

Nous allons donc modifier notre programme pour :

• ouvrir les fichiers ;

• faire le nettoyage des données dans un sous-programme ;

• copier les données ;

• coller les données ;

• fermer le fichier.

Je vous montre tout cela dans un screencast :

Comme vous avez pu le voir dans le screencast, nous avons dû faire des modifications supplémentaires dans le code initial.

Le fait d'ajouter de l’automatisation nous oblige par exemple à trouver la dernière ligne qui est remplie.

Vous avez pu également voir que j’utilise beaucoup le code avec des "Sheets(“name”).Select" ou encore "Range(“A1”).Select". Ce code n’est pas obligatoire du tout et vous verrez dans les prochains chapitres comment nous pouvons nous en passer.

Je trouve qu’utiliser ce type de code au départ permet d'être plus visuel dans le mode pas à pas du VBE. Le fait de réaliser des "Select" de fichier, de feuille ou encore de cellule nous permet de voir ce que notre code va faire. Il est ainsi plus facile de suivre le déroulé de notre code.

Dans un second temps, ou dès que vous serez à l’aise, vous pourrez optimiser votre code en supprimant ce genre d’étape qui ralentit le code.

À vous de jouer !

Un collègue de l’équipe Supply Chain vous a demandé de l’aider dans le développement d’un petit outil qui lui permettra de faire son reporting plus rapidement. Tous les matins, il doit compiler des données de différents fichiers pour préparer l’analyse des ventes.

C’est pourquoi il vous a demandé de :

• créer un fichier de reporting à la date du jour ;

• compiler les six fichiers ;

• effectuer quelques traitements esthétiques :

  • mettre en gras les ventes ;

  • calculer le CA ;

• faire un petit sommaire pour savoir si les fichiers ont bien été importés ;

• déplacer les fichiers dans un dossier “Traité” ;

• enregistrer ce fichier avec la date du jour.

En résumé

• La fonction  Dir  permet de lister les différents fichiers dans un dossier.

• La fonction  MkDir  permet de créer des dossiers.

• Il est possible de déplacer, supprimer, tester ou encore copier des fichiers avec l’objet "FileSystemObject".

• Pour automatiser un reporting, il faut commencer par lister les différentes actions et tâches que nous souhaitons faire.

Nous avons vu ensemble dans ce deuxième chapitre comment faire un reporting simple avec un prétraitement des données avant de les copier. Nous allons voir dans le prochain chapitre comment nous pouvons programmer le lancement de ce sous-programme pour automatiser encore plus notre code.