Convertir tous les fichiers Excel d'un dossier en CSV

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Convertir les fichiers Excel en CSV

Pour universaliser les données des classeurs Excel, nous proposons d'engager un traitement récursif en VBA Access, capable de convertir tous les fichiers Excel d'un dossier en fichiers CSV. C'est ainsi que ces informations pourront être interprétées et importées dans n'importe quel autre logiciel.

Base de données Access à télécharger
Pour le développement de cette solution, nous suggérons d'oeuvrer à partir d'une base de données Access offrant un formulaire avec un bouton pour déclencher l'analyse d'un dossier.

• Télécharger le fichier compressé convertir-excel-en-csv.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression fournit le fichier de la base de données avec un sous dossier nommé sources. Ce dernier héberge des classeurs Excel à transformer en fichiers CSV.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet de navigation sur la gauche, double cliquer sur le formulaire fExport,

Ce formulaire est très simple. Il accueille seulement un gros bouton destiné à déclencher le code VBA de conversion.

Au clic sur le bouton
Pour débuter, nous devons créer la procédure de code VBA à associer à ce bouton.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,
• Sur le formulaire en conception, cliquer sur le bouton pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Puis, cliquer sur le petit bouton associé à son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir le générateur de code et valider par Ok,

De fait, nous basculons dans l'éditeur VBA Access entre les bornes de la procédure événementielle Parcourir_Click. C'est bien elle qui se déclenchera au clic sur le bouton nommé Parcourir.

Ajouter les références au projet
Nous allons devoir communiquer avec l'utilisateur pour qu'il puisse désigner le dossier à traiter, par le biais d'une boîte de dialogue Office. De plus et pour la conversion en CSV, nous allons exploiter des attributs spécifiques du VBA Excel. En conséquence, deux librairies externes doivent être ajoutées au projet.

• En haut de l'éditeur VBA Access, cliquer sur le menu Outils,
• Dans la liste, choisir l'option Références,
• Dans la boîte de dialogue, cocher les références à Office et à Excel,

Il s'agit respectivement des librairies Microsoft Excel 16.0 Object Library et Microsoft Office 16.0 Object Library. Encore une fois, le numéro (16.0) dépend de la version de votre Office.

La déclaration des variables
Il est maintenant temps d'implémenter la procédure. Et pour cela, nous devons commencer par déclarer les variables nécessaires. Nous avons notamment besoin d'objets pour piloter la boîte de dialogue des dossiers, pour manipuler les fichiers du système mais aussi pour piloter une instance d'Excel afin de représenter chaque classeur que nous traiterons.

• Cliquer sur le bouton Ok de la boîte de dialogue des références pour revenir sur le code,
• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

...
Dim boite As FileDialog: Dim leChemin As String
Dim objFichier As Object: Dim leDossier As Object: Dim chaqueFichier As Object
Dim instanceE As Object: Dim nomFichier As String
...

Nous déclarons les variables pour manipuler les fichiers et dossiers comme des objets au sens large (Object). Ils prendront leurs types au moment de l'instanciation de la classe représentant les fichiers. Nous faisons de même avec la variable instanceE. Elle aussi prendra son type au moment de l'instanciation de la classe Excel. La variable boite est en revanche directement typée comme un objet FileDialog. Son rôle sera de piloter la boîte de dialogue Office pour parcourir et désigner les dossiers. Plus classiquement, nous déclarons enfin les variables leChemin et nomFichier comme des textes (String). Nous les utiliserons pour recomposer les chemins d'accès à chaque classeur que nous traiterons.

La boîte de dialogue des dossiers
Puisque les variables sont toutes déclarées, nous pouvons maintenant initialiser celle devant piloter la boîte de dialogue des dossiers.

• A la suite du code, ajouter les instructions VBA suivantes :

...
Set boite = Application.FileDialog(msoFileDialogFolderPicker)
If boite.Show Then leChemin = boite.SelectedItems(1)

If leChemin <> "" Then

End If
...

C'est la méthode FileDialog de l'objet Application avec le paramètre msoFileDialogFolderPicker qui permet d'initialiser (Set) l'objet boite sur ce type de boite de dialogue. De fait, nous exploitons sa méthode héritée Show pour ouvrir l'une de ses instances. Nous encapsulons cette action dans une instruction conditionnelle car ce type de boîte de dialogue répond par le chemin d'accès au dossier désigné par l'utilisateur. Et nous pointons dessus avec la propriété héritée SelectedItems. Elle représente en réalité la collection des dossiers sélectionnés. Mais comme nous n'avons engagé aucun paramétrage pour permettre la multi-sélection, nous savons que le chemin désigné est le premier de cette collection (SelectedItems(1)).

Ensuite, nous acceptons de poursuivre le traitement seulement si l'utilisateur n'a pas cliqué sur le bouton Annuler de la boîte de dialogue Standard, donc seulement si le chemin d'accès existe bien (If leChemin <> "" Then).

Objets de fichiers et Instance Excel
Maintenant, il est temps d'initialiser (Set) les objets devant permettre de manipuler les fichiers et dossiers du disque ainsi que l'objet devant représenter une instance d'Excel.

• Dans l'instruction conditionnelle, réaliser les initialisations suivantes :

...
Set objFichier = CreateObject("scripting.filesystemobject")
Set leDossier = objFichier.GetFolder(leChemin)
Set instanceE = CreateObject("excel.application")
instanceE.Visible = False
...

Grâce à la fonction VBA CreateObject et au paramètre scripting.filesystemobject, l'objet objFichier hérite des propriétés et méthodes pour manipuler les fichiers et dossiers. Et c'est ainsi que nous exploitons sa méthode GetFolder avec le chemin au dossier désigné en paramètre (leChemin). De fait, l'objet leDossier représente désormais le répertoire spécifié par l'utilisateur. C'est grâce à l'une de ses collections que nous pourrons entreprendre une boucle destinée à parcourir tous les fichiers qu'il contient. Ensuite, nous réalisons une nouvelle instanciation (CreateObject) mais cette fois de la classe Excel (excel.application). Donc, la variable instanceE dispose maintenant des propriétés et méthodes pour gérer les classeurs Excel. D'ailleurs dans l'enchaînement, nous réglons sa propriété Visible à False. Nous souhaitons en effet que ce processus de conversion de masse se réalise en tâche de fond. Il n'est donc pas question d'afficher des fenêtres d'Excel à l'écran.

Parcourir chaque classeur du dossier
Tous les objets étant réglés, il est temps de parcourir chaque fichier du dossier désigné pour traiter les classeurs Excel qu'il renferme.

• A la suite du code de l'instruction conditionnelle, ajouter la boucle For Each suivante :

...
For Each chaqueFichier In leDossier.Files
If (Right(chaqueFichier.Name, 5) = ".xlsx" Or Right(chaqueFichier.Name, 5) = ".xlsm") Then

End If
Next chaqueFichier

MsgBox "La conversion de masse est terminée.", vbInformation
...

Nous exploitons la collection Files de l'objet leDossier pour que l'objet chaqueFichier engagé dans cette boucle For Each soit en mesure de parcourir chaque fichier du dossier. Avant d'enclencher le traitement de conversion, nous vérifions un double critère grâce à une instruction conditionnelle. Nous nous assurons que le fichier en cours d'analyse est bien un classeur. Pour cela, nous testons son extension (.xlsx ou .xlsm) sur les cinq derniers caractères grâce à la fonction VBA Right. Ensuite et par anticipation, nous programmons un message de confirmation (MsgBox) en dehors des bornes de la boucle, soit lorsque le traitement récursif sera complètement achevé.

Ouvrir chaque classeur à convertir
A chaque passage dans cette boucle, nous devons transmettre le chemin d'accès au fichier en cours d'analyse à l'instance d'Excel. C'est ainsi qu'elle pourra l'ouvrir en mémoire pour traiter le classeur correspondant et entreprendre sa conversion au format CSV.

• Dans l'instruction conditionnelle de la boucle, ajouter les deux lignes VBA suivantes :

...
nomFichier = leChemin & "\" & chaqueFichier.Name
instanceE.Workbooks.Open (nomFichier)
...

A chaque passage dans cette boucle, nous recomposons le chemin d'accès complet au fichier en cours d'analyse en associant (& "\" &) son nom (chaqueFichier.Name) au chemin du dossier désigné (leChemin). Ensuite, nous exploitons la méthode Open de la collection Workbooks de l'instance d'Excel. Grâce à elle et au chemin du fichier passé en paramètre, nous sommes désormais en mesure d'intervenir sur ce classeur.

Convertir chaque classeur en fichier CSV
C'est maintenant une méthode de sauvegarde avec un attribut spécifique qui permet d'exercer la conversion de chaque classeur ouvert en mémoire tour à tour. Cet attribut en question est interprété grâce à la référence à Excel ajoutée au projet VBA Access.

• A la suite, dans l'instruction conditionnelle de la boucle, ajouter les deux lignes VBA suivantes :

...
instanceE.ActiveWorkbook.SaveAs Left(nomFichier, InStrRev(nomFichier, ".")) & "csv", xlCSVWindows, Local:=True
instanceE.ActiveWorkbook.Close False
...

C'est bien sûr la méthode SaveAs sur le classeur en cours (ActiveWorkbook) qui permet d'organiser la sauvegarde, mais pas n'importe comment puisqu'il s'agit d'une conversion au format CSV. Tout d'abord, nous lui passons le nom du fichier en premier paramètre. Pour cela, grâce à la fonction VBA Left, nous prélevons le nom de l'actuel classeur jusqu'au point, grâce à la fonction InStrRev en second paramètre. Elle décèle la position du dernier point. Nous ajoutons l'extension CSV. Le deuxième argument (xlCSVWindows) de la méthode SaveAs est fondamental. C'est lui qui détermine le format CSV de sortie. Et c'est lui qui requiert l'ajout de la référence à Excel au projet. Le troisième argument n'est pas anodin non plus (Local:=True). Par défaut, le délimiteur utilisé est la virgule. Or dans notre système, la virgule est déjà utilisée pour séparer les décimales des nombres. Avec ce réglage, nous exploitons le point-virgule comme séparateur. Ensuite, nous fermons le classeur en cours pour pouvoir traiter le suivant dans l'enchaînement.

Conversion Excel en CSV par VBA Access
Il ne nous reste plus qu'à libérer la mémoire. Pour cela, nous devons fermer l'instance d'Excel et la détruire. Il peut aussi paraître opportun de détruire les autres variables objets.

• A la fin du code, avant le End Sub, ajouter les deux instructions VBA suivantes :

...
instanceE.Quit
Set instanceE = Nothing
...

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• L'exécuter en enfonçant par exemple la touche F5 du clavier,
• Cliquer sur le bouton du formulaire pour afficher la boîte de dialogue d'Office,
• Désigner le sous dossier nommé sources dans le dossier de décompression,
• Puis, cliquer sur le bouton Ok pour lancer la conversion récursive,

Comme vous pouvez le voir, le message de confirmation apparaît bien à l'issue.

• Dans l'explorateur Windows, ouvrir le sous dossier sources,

Comme vous pouvez le constater, chaque classeur Excel propose désormais son jumeau au format CSV. Et si vous ouvrez l'un d'entre eux dans un éditeur de texte, vous remarquez que les données des colonnes sont parfaitement séparées par des points-virgules, comme nous l'avons souhaité.



Nous avons donc construit des versions universelles sur la base de classeurs Excel pour que ces informations puissent désormais être interprétées par n'importe quel autre logiciel.

Le code VBA Access complet que nous avons confectionné pour convertir tous les classeurs Excel en fichiers CSV est le suivant :

Private Sub Parcourir_Click()
Dim boite As FileDialog: Dim leChemin As String
Dim objFichier As Object: Dim leDossier As Object: Dim chaqueFichier As Object
Dim instanceE As Object: Dim nomFichier As String

Set boite = Application.FileDialog(msoFileDialogFolderPicker)
If boite.Show Then leChemin = boite.SelectedItems(1)

If leChemin <> "" Then
Set objFichier = CreateObject("scripting.filesystemobject")
Set leDossier = objFichier.GetFolder(leChemin)
Set instanceE = CreateObject("excel.application")
instanceE.Visible = False

For Each chaqueFichier In leDossier.Files
If (Right(chaqueFichier.Name, 5) = ".xlsx" Or Right(chaqueFichier.Name, 5) = ".xlsm") Then
nomFichier = leChemin & "\" & chaqueFichier.Name
instanceE.Workbooks.Open (nomFichier)

instanceE.ActiveWorkbook.SaveAs Left(nomFichier, InStrRev(nomFichier, ".")) & "csv", xlCSVWindows, Local:=True
instanceE.ActiveWorkbook.Close False
End If
Next chaqueFichier

MsgBox "La conversion de masse est terminée.", vbInformation
End If

instanceE.Quit
Set instanceE = Nothing

End Sub