Comment convertir des CSV en fichier Excel en masse (tous les fichiers d’un répertoire) avec VBA

Dans un précédent tutoriel, que je vous avais proposé l’année dernière, nous avions découvert comment travailler avec des fichiers CSV sur Excel. Nous avions alors manipulé des fichiers CSV sur Excel, mais également procédé à l’opération inverse, c’est-à-dire transformer un fichier Excel en CSV. Aujourd’hui, je vous propose de répondre à la question posée par « Flo Chass » dans les commentaires de la vidéo sur Youtube, qui s’interroge sur la possibilité de réaliser une conversion de masse de fichier CSV en fichier Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce cours, nous allons partir de la série de fichiers CSV suivante :

Chacun de ces fichiers reprend les informations concernant les clients des différentes agences d’un groupe de magasin.

Il suffit d’ouvrir l’un d’entre eux pour constater qu’il s’agit de fichiers CSV classiques dans lequel chaque information est séparée par une simple virgule.

L’objectif étant alors de convertir chacun de ces fichiers en un fichier Excel facilement exploitable.

2. Convertir un fichier CSV en 1 clic

Tout d’abord, voyons comment faciliter la conversion d’un unique fichier en utilisant une macro commande VBA que nous pourrons lancer d’un simple clic !

Bien entendu, dans une seconde partie, nous verrons comment modifier cette macro commande pour automatiser la conversion de tous les fichiers présents dans un répertoire.

Et pour nous simplifier encore la tâche, nous n’allons pas saisir cette macro à la main, mais nous allons préférer utiliser l’enregistreur de macro.

Cette outil, que nous avons déjà eu l’occasion de découvrir dans le chapitre « Créer une macro VBA AUTOMATIQUEMENT sur EXCEL avec l’enregistreur » de la formation sur l’apprentissage de VBA pour les débutants.

Ici, nous allons découvrir une méthode alternative, qui va permettre d’affecter directement le lancement de la macro à un bouton !

Pour cela, nous commençons par créer un objet Zone de texte (menu Insertion > Zone de texte) :

Puis nous dessinons cet objet sur la feuille de calcul :

Ensuite, nous saisissons un texte au bouton et nous le mettons en forme :

Une fois ces opérations terminées, nous pouvons simplement effectuer un clic-droit sur le bouton, pour choisir l’option Affecter une macro.

Nous saisissons un nom que nous souhaitons donner à la macro-commande, puis nous cliquons sur le bouton Enregistrer pour démarrer l’enregistrement de toutes les actions que nous allons réaliser au sein d’Excel :

Nous validons simplement la fenêtre suivante, qui contient déjà le nom de la macro.

À partir de maintenant, tout ce que nous allons faire sera interprété par Excel en ligne de code.

Nous allons ici réaliser les opérations suivantes :

• Ouvrir l’un des fichiers CSV,
• Le convertir le fichier CSV en fichier Excel,
• L’enregistrer le fichier CSV en tant que fichier Excel (format *.xlsx)
• Fermer le fichier enregistré

Pour ouvrir le fichier, rendez-vous dans le menu Fichier > Ouvrir > Parcourir, puis dans la fenêtre d’ouverture, sélectionnez « Tous les fichiers (*.*) :

Choisissez l’un des fichiers puis cliquez sur ouvrir :

Le fichier maintenant ouvert, mais dans un format brut !

Pour le convertir le fichier CSV en un tableau Excel, sélectionnez toutes la colonne A dans laquelle se trouvent les données, puis rendez-vous dans le menu Insertion > Convertir :

Depuis l’assistant de conversion, choisissez le type de fichier Délimité :

Dans la fenêtre suivante, choisissez le séparateur Virgule, puis validez la conversion en appuyant sur le bouton Terminer :

Les données sont maintenant converties, nous pouvons enregistrer le fichier sous un format Excel (Fichier > Enregistrer sous…) :

Dans le menu Type, choisissez le format « Classeur Excel (*.xslx) » puis validez en appuyant sur la touche Enregistrer.

Et voilà, la conversion d’un fichier unique est maintenant terminée !

Nous pouvons valider la création de la macro commande en cliquant sur le carrée de fin d’enregistrement situé sur la barre d’état d’Excel :

3. Import de fichiers en masse !

La première chose que nous allons faire avant de modifier le fonctionnement de la macro va être de simplifier celle-ci !

Pour visionner cette macro VBA, effectuez un nouveau clic-droit sur le bouton inséré dans la feuille de calcul > Affecter une macro.

Ensuite, sélectionnez la macro, puis cliquez sur le bouton Modifier :

Nous pouvons ainsi consulter le fonctionnement de cette macro :

Nous voyons ainsi que conformément à ce que nous avons réalisé, Excel a notamment sélectionné la colonne A en entière, puis converti celle-ci dans un second temps.

Pour optimiser très légèrement le processus, nous allons fusionner ces deux lignes pour réaliser la conversion, non plus sur la sélection, mais directement sur la colonne A :

    Columns("A:A").TextToColumns  Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote,  ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False,  Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1),  Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True

Ensuite, pour simplifier encore un peu cette ligne, nous allons supprimer les arguments inutiles, ce qui permettra ensuite de pouvoir utiliser la commande sur tous les fichiers CSV (qui auraient même le même type de délimiteur, c’est-à-dire la virgule) :

    Columns("A:A").TextToColumns  Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

Maintenant que notre macro est simplifiée au possible, nous allons créer une nouvelle procédure, dans laquelle nous allons déplacer le contenu de cette macro !

Pour cela, nous tapons simplement le nom de la macro après avoir saisi le mot-clé Sub (convertirUnCSV) :

Sub convertirUnCSV()
 
End Sub

Nous sélectionnons ensuite, le contenu de la macro précédente pour le faire glisser dans la nouvelle macro :

De cette manière, nous allons pouvoir faire passer le nom du fichier à convertir en tant qu’argument de cette nouvelle procédure.

Pour cela, nous saisissons le nom de cet argument entre les parenthèses insérées par Excel sur la ligne de déclaration :

Sub convertirUnCSV(fichier as String) 

Nous appelons cet argument « fichier » et le typons en tant que string, c’est-à-dire une chaîne de caractère qui va contenir le chemin d’accès complet au fichier.

De cette manière, nous allons pouvoir utiliser cet argument comme nom de fichier à ouvrir :

    Workbooks.Open Filename:= fichier

Mais nous allons également en déduire le nom du fichier Excel généré et pour cela, nous allons faire appel à la fonction Replace, qui permet de remplacer une série de caractère par une autre, au sein d’une série principale.

Nous pouvons donc très facilement remplacer l’extension *.csv par *.xlsx :

ActiveWorkbook.SaveAs Filename:=Replace(fichier,  ".csv", ".xlsx"),  FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Cela étant fait, nous allons maintenant revenir dans la macro principale, afin de passer en revue tous les fichiers présents dans un dossier en particulier pour les convertir !

Pour commencer, nous allons demander Excel de sélectionner le premier fichier à convertir :

    Dim dossier As Variant
    dossier = Application.GetOpenFilename

Cette commande va lui présenter une fenêtre de sélection de fichier :

Le chemin d’accès complet au fichier va alors être enregistré dans la variable dossier.

Un Debug.Print de cette variable permet alors de connaître sa valeur réelle :

Nous allons vouloir simplement supprimer la partie qui se trouve sur la partie droite, après le dernier antislash (« \ »).

Pour cela, nous allons utiliser deux fonctions VBA :

• La fonction InStrRev qui va permettre de connaître la position de ce caractère,
• La fonction Left qui va permettre de ne conserver qu’un nombre de donnés de caractères de cette chaîne

Ensuite, nous allons pouvoir passer en revue tous les fichiers CSV de ce dossier pour lancer la macro de conversion.

Pour cela, nous allons créer une nouvelle variable que nous allons appeler « fichier », à laquelle nous allons donner pour valeur le chemin du dossier, suivi du symbole joker étoile (le « * », qui permet de remplacer n’importe quelle série de caractère) et de l’extension csv :

    Dim fichier As String
    fichier = Dir(dossier & "*.csv")

Ensuite, il ne reste plus qu’à boucler sur tous les fichiers csv présent dans le dossier en utilisant une boucle Do While qui va se répéter tant que VBA identifie des fichiers dans le dossier.

    Do While Len(fichier) > 0
    Loop

Dans cette boucle, nous allons simplement appeler la procédure de conversion (convertirUnCSV avec pour argument le contenu de la variable « fichier »), puis passer au fichier suivant :

Maintenant pour tester la fonction, il suffit de revenir sur la feuille de calcul et de cliquer sur le bouton de conversion (pensez à supprimer les éventuels fichiers déjà convertis pour éviter l’apparition d’un message) :