Comment lire un fichier externe (txt, csv,…) avec une fonction Excel VBA ?

Dans ce tutoriel, je vais vous montrer comment créer une petite fonction personnalisée en VBA, qui vous nous permettre de récupérer très facilement le contenu d’un fichier externe, comme un fichier texte *.txt ou *.csv.

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. Comment lire un fichier texte avec une fonction Excel

Dans un tutoriel précédent, nous avions vu qu’Excel propose des outils pour pouvoir importer des données depuis un fichier externe, comme un fichier texte.

Pour cette nouvelle partie, nous allons mettre en place une fonction personnalisée, qui va ainsi nous permettre d’exploiter les données de ce fichier directement à l’endroit souhaité.

Cela nous permettra également de l’exploiter très simplement soit dans la feuille de calcul, soit au sein d’une macro VBA.

2. Création de la fonction personnalisée LIRE_TXT()

Pour créer cette fonction personnalisée, nous allons tout d’abord devoir nous rendre dans VBE, qui est l’outil de développement des applications VBA.

Pour cela, il existe plusieurs méthodes, la plus simple consistant à utiliser le raccourci clavier [Alt]+[F11].

Une fois dans VBE, nous allons pouvoir créer un nouveau module, qui est une feuille de saisie de code dans laquelle nous allons venir enregistrer notre fonction personnalisée.

Pour cela, nous nous rendons simplement dans le menu Insertion afin de cliquer sur Module :

Pour créer la fonction personnalisée, il suffit de taper le mot-clé « Function », suivi du nom de la fonction :

Lorsque nous validons la création de la fonction en appuyant sur la touche [Entrée] du clavier, VBE ajoute la ligne End Function pour fermer cette dernière.

Ainsi, tout ce que nous saisirons entre ces deux lignes sera exécuté à chaque fois que nous appellerons la fonction LIRE_TXT().

Nous allons maintenant revenir sur la première ligne, afin de renseigner un argument pour pouvoir faire fonctionner la fonction.

Cet argument, que nous appelons « chemin » est la variable qui va permettre d’obtenir le chemin permettant d’accéder au fichier texte que nous souhaitons lire.

Pour initialiser un argument, il suffit de saisir son nom et son type entre les parenthèses :

Function LIRE_TXT(chemin As String)
 
End Function 

Ici, chemin est typé en tant que string, étant donné qu’il s’agit d’une chaîne de caractères.

3. Comment lire un fichier avec VBA ?

Voyons maintenant comment ouvrir le fichier.

Pour commencer, nous déclarons deux variables qui vont nous être utiles juste après :

• Ligne : cette variable va nous permettre d’enregistrer le contenu de chaque ligne
• Texte : cette variable va nous permettre d’enregistrer le contenu de tout le fichier par concaténation des variables « ligne »

Dim ligne As String, texte As String

Ici ces deux variables sont typées en tant que String, c’est-à-dire en tant que variables destinées à enregistrer des chaînes de caractères.

Ensuite, pour ouvrir le fichier texte dans la mémoire d’Excel, nous allons devoir utiliser la méthode Open de la manière suivante :

    Open chemin For Input As #1

Ici, nous ouvrons le fichier correspondant à la variable chemin en lecture seule, son contenu étant indexé par « #1 ».

Nous pourrons ainsi nous référer à ce fichier en utilisant simplement le « #1 » pendant le reste du code de la fonction.

Maintenant que le fichier texte est utilisable, nous allons lire chacune des lignes les unes à la suite des autres.

Et pour cela, nous allons mettre en place une boucle Do Until, qui permet d’exécuter une suite d’instruction jusqu’à ce qu’une condition soit remplie (ici, jusqu’à atteindre la fin du document).

Nous ajoutons donc la ligne suivante :

Do Until EOF(1)
Loop

Note : L’instruction EOF() signifie « End Of File », ce qui se traduit par « fin de fichier ».

Puis, à l’intérieur de cette boucle, nous pourrons obtenir le contenu de la ligne en cours d’analyse par l’instruction Line Input, laquelle va enregistrer la ligne dans la variable ligne :

    Do Until EOF(1)
        Line Input #1, ligne
    Loop

Pour stocker l’intégralité du contenu du fichier texte dans la variable texte, ligne après ligne, nous allons simplement ajouter le contenu de la variable ligne à la suite de son propre contenu, en utilisant l’esperluette pour effectuer une concaténation.

    Do Until EOF(1)
        Line Input #1, ligne
        texte = texte & ligne
    Loop

Et si nous souhaitons conserver le retour à la ligne, nous pouvons encore ajouter ce dernier en ajoutant « Chr(13) & Chr(10) à la fin :

    Do Until EOF(1)
        Line Input #1, ligne
        texte = texte & ligne & Chr(13) &  Chr(10)
    Loop

Maintenant que nous avons extrait le contenu du fichier texte pour l’enregistrer dans la variable texte, nous pouvons libérer la mémoire de la machine en fermant ce dernier :

    Close #1

Il ne reste plus qu’à retourner le contenu de la variable texte en tant que résultat de la fonction :

    LIRE_TXT = texte

Et voilà, notre macro est maintenant terminée, nous pouvons la tester avec une macro VBA :

Lorsque nous lançons la macro test, Excel nous affiche un message avec le contenu du fichier texte passé en argument :

Nous pouvons également récupérer ce contenu dans une cellule Excel en appelant la fonction comme nous le ferions pour n’importe quelle fonction Excel :