Importer et exporter des données en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Importation et exportation de fichiers en VBA Excel

Dans cette formation, nous allons apprendre à accéder aux fichiers externes en VBA Excel. Nous allons créer une application, dotée d'une interface graphique, permettant d'importer les informations de fichiers CSV dans les cellules d'une feuille Excel. VBA permet de charger le contenu de ces fichiers, constitués de séparateurs de listes, en mémoire pour le traiter ligne à ligne. On parle d'accès séquentiels. Une fois les données importées, le code permettra de repérer et supprimer les doublons pour purger le tableau consolidé de plusieurs fichiers externes. Puis le programme se chargera d'exporter ces données épurées au format CSV pour que n'importe quel logiciel de gestion interne puisse les intégrer. Les fichiers CSV tout comme les fichiers XML sont des fichiers dont le langage est interprété universellement. Ils servent souvent de passerelle de communication entre les logiciels.

Le formulaire d'importation
Afin d'interagir avec l'utilisateur et que ce dernier puisse désigner les fichiers à importer, nous allons construire une interface graphique. Pour permettre à l'utilisateur de choisir les fichiers à un emplacement précis, nous allons piloter les boîtes de dialogue standard de Windows, notamment la boîte de dialogue Ouvrir. Le support de formation VBA Excel sur la facturation automatisée donne les bases sur la création des formulaires en VBA.


La capture ci-dessus présente le UserForm à créer pour l'application. Trois boutons sont nécessaires. Le premier bouton Importer permet de démarrer le programme. Le bouton Exporter permet de retranscrire en CSV, sur le disque, les données importées et purgées des doublons. Le dernier boutonFermer permet tout simplement de mettre fin au programme en fermant le formulaire. La liste déroulante sur la gauche sert à offrir un résumé chronologique des fichiers qui ont été choisi pour l'importation. La zone de saisie en bas du formulaire, sert à rappeler le chemin d'accès au fichier CSV pour l'écriture des données en sortie.

Conception du formulaire

• Créer un nouveau classeur vierge dans Excel,
• Basculer dans l'éditeur de code Visual Basic par le raccourci ALT + F11 par exemple,
• Dans le menu Insertion de l'éditeur, choisir UserForm,

Le formulaire apparaît au centre de l'espace de travail. La fenêtre Propriétés en bas à gauche, liste les propriétés de ce UserForm. Nous allons en personnaliser certaines.

• Saisir formulaire dans sa propriété (Name),

Nous attribuons ainsi un nom d'objet à ce formulaire. C'est par son nom que nous allons pouvoir le piloter, pour appeler ses propriétés et méthodes.

• Régler sa propriété Width sur 360 et sa propriété Height sur 270,
• Saisir Importation & Exportation CSV dans sa propriété Caption pour modifier le texte de la barre de titre de la fenêtre du formulaire,
• Modifier sa couleur de fond à l'aide de sa propriété BackColor,

La propriété BackColor, comme son nom l'indique, permet de modifier la couleur de fond d'un objet, qu'il s'agisse d'un formulaire, d'un bouton ou encore d'une zone de saisie. Il s'agit maintenant d'ajouter les contrôles sur le Userform. Lorsque ce dernier est sélectionné, une petite boîte à outils, proposant différents contrôles, s'affiche.

• Ajouter un contrôle Zone de liste en le sélectionnant depuis la boîte à outils et en le traçant sur le formulaire,
• Saisir liste_fichiers dans sa propriété (Name),
• Modifier sa propriété BackColor, selon la capture et éventuellement sa propriété Font (Calibri, 12, Gris foncé),
• Ajouter trois boutons sur la droite de la Zone de liste,
• Nommer le premier importer, propriété (Name),
• Nommer le deuxième exporter,
• Puis nommer le dernier fermer,
• Adapter la propriété Caption pour chacun d'eux,
• Sélectionner ensemble ces trois boutons à l'aide de la touche CTRL,
• Modifier leur propriété BackColor, ForeColor et Font pour obtenir un résultat proche de celui de la capture,

La propriété ForeColor permet d'une manière générale de modifier la couleur de premier plan d'un contrôle. Donc concernant un bouton, il s'agit de la couleur du texte.

• Ajouter enfin un contrôle Zone de texte en bas du formulaire sur sa largeur,
• Saisir sortie dans sa propriété (Name),

Le formulaire est prêt. Avant de commencer le développement, nous allons faire en sorte qu'il se déclenche automatiquement à l'ouverture du classeur. La méthode Show d'un Userform permet de commander l'affichage du formulaire. Le code doit être écrit dans la procédure évènementielle Workbook_Open. L'événement Open associé à l'objet Workbook (Classeur) se génère à l'ouverture du classeur. Donc tout code écrit entre ces bornes est exécuté à cette occasion.

• Double cliquer sur ThisWorkbook dans la fenêtre Projet en haut à gauche de l'éditeur,
• Au centre de l'écran, sélectionner Workbook à l'aide de la liste déroulante de gauche, située au-dessus de la page de code,

La procédure Workbook_Open() se crée automatiquement.

• Entre les bornes de la procédure, saisir la commande formulaire.Show,

Private Sub Workbook_Open()
formulaire.Show
End Sub

formulaire est le nom que nous avons attribué à l'objet UserForm. Par son nom donc, nous appelons sa méthode Show pour commander son affichage. Ce dernier interviendra à l'ouverture du classeur puisque ce code est écrit dans l'événement correspondant. C'est ce que nous allons tester.

• Basculer sur la feuille Excel,
• Enregistrer le classeur (CTRL + S),
• Dans la boîte de dialogue, choisir le type (*.xlsm) pour la prise en charge du code VBA,
• Saisir un nom pour ce classeur puis l'enregistrer,
• Ensuite, fermer ce classeur,
• Puis, le rouvrir en passant par Fichier puis Ouvrir et en double cliquant sur son nom dans la liste des classeurs récents,

Comme vous le constatez, le formulaire apparaît automatiquement au-dessus de la feuille au moment où le classeur s'ouvre. Aucun des boutons n'est fonctionnel à ce stade puisqu'aucun code n'a encore été développé. Ces codes doivent être écrits dans l'événement associé au clic sur le bouton pour qu'ils se déclenchent à cette occasion. Pour créer ces procédures événementielles, il suffit, depuis l'éditeur Visual Basic, de double cliquer sur chacun d'entre eux.


Procédures VBA associées aux événements des boutons

• Fermer le formulaire en cliquant sur la croix de sa fenêtre en haut à droite,
• Basculer dans l'éditeur de code (ALT + F11),
• Afficher le Userform en double cliquant sur l'objet Formulaire depuis la fenêtre Projet,
• Double cliquer sur le bouton Importer,

Vous créez ainsi la procédure événementielle Private Sub importer_Click(). Tout code écrit entre ses bornes se déclenchera au clic sur le bouton Importer.

• Afficher de nouveau le formulaire et double cliquer sur le bouton Exporter,
• Faire de même pour le bouton Fermer,

Nous obtenons ainsi les trois procédures de code associées aux clics sur les boutons respectifs. Il s'agit maintenant de développer chacune d'entre elles afin d'importer les fichiers externes, d'en traiter les données depuis les cellules de la feuille, puis de retourner le résultat épuré dans un fichier d'exportation, au format CSV.


Code VBA pour importer les données de fichiers externes
Nous devons commencer par déclarer des variables globales afin de mémoriser les indices de ligne et de colonne des cellules de début et de fin de tableau, créé par l'importation des données. De même, nous devons déclarer les variables qui seront incrémentées au fur et à mesure du remplissage des cellules en ligne et en colonne. La durée de vie des variables globales n'est pas limitée au temps d'exécution d'une procédure événementielle. Une variable globale reste disponible tant que le formulaire n'est pas fermé. Leurs valeurs peuvent ainsi être transportées et exploitées par toutes les procédures du programme. Une variable globale se déclare d'une façon classique mais en tout début de code, en dehors des bornes de toute procédure.

• Ajouter les déclarations suivantes pour les variables globales :

Dim ligne_debut As Integer: Dim colonne_debut As Integer
Dim ligne_fin As Integer: Dim colonne_fin As Integer
Dim ligne_enCours As Integer: Dim colonne_enCours As Integer



Tout d'abord, nous devons proposer à l'utilisateur de sélectionner le ou les fichiers CSV à importer par le biais de la boîte de dialogue ouvrir standard de Windows. La méthode GetOpenFilename de l'objet VBA Application permet d'atteindre cette ressource. Ce code doit donc être écrit dans les bornes de la procédure importer_click(). Mais avant cela, nous avons besoin de fichiers CSV de test. Pour cela :

• Télécharger les fichiers clients-facturation.txt et clients-facturation2.txt en cliquant sur leur lien respectif,

Il s'agit donc de deux fichiers au format .csv pour lesquels le séparateur de liste utilisé est le point-virgule (;). Pour l'importation, le point-virgule permet de séparer les données afin de les intégrer dans une nouvelle cellule d'une autre colonne. Nous avons besoin d'une variable pour stocker les chemins d'accès aux fichiers sélectionnées via cette boîte de dialogue standard.

• Ajouter la déclaration suivante, cette fois dans la procédure importer_click(),

Dim fichier_choisi As String

Le String, comme vous le savez, permet de mémoriser des chaines de caractères. Donc ce type est tout à fait approprié pour stocker le chemin d'accès à un fichier.

• A la suite du code de la procédure importer_click(), ajouter les deux lignes suivantes :

fichier_choisi = Application.GetOpenFilename('Text Files (*.txt), *.txt', , 'Sélectionner le fichier CSV')
liste_fichiers.AddItem (fichier_choisi)

Une fois le fichier sélectionné, la méthode GetOpenFilename de l'objet Application retourne un String correspondant au chemin d'accès du fichier. Nous le stockons donc dans la variable fichier_choisi. Vous remarquez les arguments facultatifs que nous avons passés à la méthode GetOpenFilename. Le premier permet de filtrer la boîte de dialogue standard Ouvrir, uniquement sur les fichiers de type texte (*.txt). Le deuxième est ignoré tandis que le troisième permet de personnaliser le titre de la boîte de dialogue Ouvrir. Souvenez-vousque le fait d'enfoncer la touche F1 après avoir sélectionné la méthode dans le code, permet d'obtenir l'aide en ligne sur cette dernière. L'aide permet notamment de connaître les arguments de la méthode et la syntaxe à employer. Chaque fichier ainsi sélectionné par la boîte de dialogue Ouvrir est ensuite empilé dans la zone de liste du formulaire (liste_fichiers.AddItem(fichier_choisi)). Nous passons à la méthode AddItem de l'objet Zone de liste liste_fichiers, le chemin d'accès du fichier, en argument. Il est temps de tester ce code :

• Enfoncer la touche F5 du clavier pour exécuter le formulaire,
• Cliquer sur le bouton Importer,

Comme l'illustre la capture ci-dessus, la boîte de dialogue standard s'affiche. Vous remarquez que la zone Type est filtrée sur les fichiers de type *.txt et que le titre de la boîte de dialogue est bien celui que nous avons codé.

• Afficher le contenu du dossier de téléchargement des fichiers précédents,
• Double cliquer sur l'un d'entre eux pour l'ajouter,
• Cliquer de nouveau sur le bouton Importer,
• Double cliquer sur le second,

Comme vous le constatez, les deux fichiers avec leur chemin, sont pris en compte et ajoutés dans la zone de liste du formulaire. Si bien qu'il s'agit maintenant, via le code, de parcourir cette zone de liste afin d'ouvrir en lecture, tour à tour, chacun des fichiers. Dès lors, il s'agira d'en traiter l'information pour la reconstituer cellule à cellule dans la feuille Excel.

• Cliquer sur la croix du formulaire pour stopper son exécution,
• De retour dans l'éditeur, enfoncer la touche F7 pour revenir dans le code,

Le travail sur la procédure importer_click() est terminé. Vous en conviendrez, son code est excessivement simple grâce aux objets Windows et contrôles de formulaire que VBA nous permet de piloter.

Private Sub importer_Click()
Dim fichier_choisi As String

fichier_choisi = Application.GetOpenFilename('Text Files (*.txt),*.txt', , 'Sélectionner le fichier CSV')
liste_fichiers.AddItem (fichier_choisi)

End Sub

Traitement séquentiel de données externes et exportation
Nous allons maintenant écrire le code du bouton Exporter afin d'initialiser le traitement. Nous devons commencer par mémoriser les positions de départ pour l'importation des données. Plutôt que de réaliser cette dernière en fonction de la cellule active, nous allons déterminer une cellule de départ fixe, B2. B2 se trouve en ligne 2 et en colonne2.

• Dans les bornes de la procédure exporter_click(), réaliser les affectations des variables globales pour la cellule de départ :

ligne_debut = 2: colonne_debut = 2

Puis nous devons initialiser les variables qui permettront de parcourir les lignes et les colonnes au fur et à mesure de l'importation, sur ces références de départ.

• Pour ce faire, à la suite du code, ajouter les affectations suivantes des variables globales :

ligne_enCours = ligne_debut: colonne_enCours = colonne_debut

Nous devons ensuite effacer les données déjà présentes sur la feuille. Il peut s'agir par exemple, d'une importation antérieure. La méthode Clear de l'objet Cells réalise cette opération car l'objet Cells sans indice de ligne ni de colonne, représente toutes les cellules de la feuille.

• A la suite du code, ajouter l'instruction suivante :

Cells.Clear

En réalité, ce bouton doit permettre d'accéder en lecture au contenu de chacun des fichiers, dont le nom est stocké dans la zone de liste du formulaire. Puis il doit permettre de traiter ces données pour en reconstituer un tableau complet sur la feuille Excel. Les cellules doivent alors être analysées à la recherche de doublons. Lorsqu'ils sont trouvés, les lignes entières correspondantes doivent être supprimées. Et c'est seulement à l'issue que l'exportation doit être réalisée dans un nouveau fichier de type CSV. Il s'agit donc d'un traitement relativement complexe. Plutôt que d'écrire l'intégralité du code dans la procédure exporter_click(), nous allons le découper en plusieurs procédures que nous appellerons au fur et à mesure.

• Pour cela, dans la page de code, en dehors de toute procédure, créer les trois procédures suivantes :

Private Sub lecture(fichier As String)

End Sub

Private Sub ecriture(fichier As String)

End Sub

Private Sub traitement()

End Sub

La procédure lecture, sera appelée autant de fois qu'il y a de fichiers sélectionnés dans la zone de liste, afin de traiter les données importées de chacun. Cet appel se fera par le biais d'une boucle parcourant tous les éléments de la zone de liste. A chaque passage dans la boucle, le nom du fichier change. Donc à chaque passage, la procédure lecture doit être appelée en lui passant le nom du fichier texte à traiter. C'est pourquoi nous déclarons cette procédure avec une variable de type String en argument. Cette variable permettra de recevoir le nom du fichier.

La procédure ecriture sera appelée en toute fin de code, une fois l'importation et le traitement des doublons terminés. L'utilisateur aura alors désigné l'emplacement et le nom du fichier sous lequel exporter les données. Ce nom de fichier sera passé à la procédure. C'est pourquoi nous la déclarons avec comme argument un String qui permettra de réceptionner ce nom de fichier pour l'exportation. Vous remarquez que cette variable porte le même nom que celle déclarée en argument pour la procédure lecture. Nous y sommes autorisés car contrairement aux variables globales, ces variables sont locales. Cela signifie que leur durée de vie ne dépasse pas les bornes de la procédure. Donc aucun risque de conflit n'est à craindre.

Enfin, la procédure traitement() qui sera appelée avant la procédure ecriture, permettra de trouver et supprimer les doublons des données importées dans les cellules Excel.
Pour la procédure lecture(), nous devons commencer par la déclaration des variables nécessaires. Le support de formation sur les variables en VBA enseigne les différents types de données et leur importance.

• Placer le point d'insertion entre les bornes de la procédure lecture(),
• Ajouter les déclarations suivantes:

Dim depart As Integer, position As Integer
Dim texte As String, tampon As String

Nous avions déjà vu les deux points (:) dans la syntaxe afin de réaliser la déclaration de plusieurs variables sur une même ligne. Ici nous découvrons la virgule qui permet l'énumération de plusieurs variables les unes à la suite des autres. Cette technique permet d'éviter de retaper l'instruction Dim pour la déclaration. La variable depart servira à mémoriser la position de départ dans la chaîne de texte lors de la lecture des données externes importées. La variable position permettra de mémoriser la position du point-virgule (séparateur de liste) pour chaque ligne traitée depuis le fichier CSV. Cette position est importante car chaque ligne devra être découpée sur ce point-virgule, pour récupérer les informations indépendantes à placer dans des cellules distinctes. Ce sont des fonctions VBA de traitement de chaînes qui réaliseront cette opération en exploitant la valeur de la variable position. La variable texte permettra de stocker la ligne entière réceptionnée à chaque passage dans la boucle de lecture du fichier CSV. La variable tampon quant à elle permettra d'enregistrer temporairement les chaînes découpées sur les points-virgules, grâce aux fonctions de traitement et à la variable position.

Nous devons maintenant écrire le code de la boucle dans le bouton Exporter. Cetteboucle doit parcourir tous les éléments de la zone de liste pour fournir à la procédure lecture(), tour à tour, chaque nom de fichier à traiter. Le premier élément d'une zone de liste est repéré par la position 0. C'est la position de départ qui servira à initialiser la boucle. Pour connaître la borne supérieure de la boucle, il faut connaître le nombre d'éléments contenus dans la liste, soit le nombre de tours à réaliser dans la boucle. La propriété ListCount d'un contrôle Zone de liste, renvoie ce nombre d'éléments. Si la liste contient 3 noms de fichiers, la boucle doit parcourir les éléments de la position 0 à la position 2, soit ListCount-1. Le support de formation VBA sur les boucles For Next apprend à exploiter ces traitements que nous allons mettre en oeuvre.

• Après le Cells.Clear de la procédure exporter_click(), ajouter la boucle suivante :

For i = 0 To liste_fichiers.ListCount - 1

Next i

For est le mot clé utilisé pour initialisé la borne de départ de la boucle. C'est pourquoi il est suivi d'une variable, déclarée et initialisée à la volée sur la valeur 0. To est le mot clé qui permet de définir jusqu'à quelle valeur la boucle doit tourner, sachant qu'à chaque passage la variable i sera incrémentée. Comme nous l'avons dit précédemment, le nombre d'éléments dans la liste (liste_fichiers.ListCount) auquel nous retranchons 1, définit cette borne supérieure. L'instruction d'une boucle doit toujours se terminer par le mot clé Next suivi de la variable utilisée pour boucler (Next i).

A l'intérieur de cette boucle, nous devons écrire le code qui permet d'importer les données de chaque fichier ainsi listé. Comme nous l'avons dit, la procédure lecture() doit se charger de ce traitement. Donc nous devons effectuer l'appel de cette dernière.

• Dans la boucle, ajouter l'appel suivant :

lecture (liste_fichiers.List(i))

La propriété List de l'objet zone de liste, avec en argument la position (valeur de i dans la boucle), retourne la valeur contenue dans la liste à cette position. Il s'agit du chemin d'accès au fichier que nous transmettons directement à la procédure lecture(). Pour vérifier que les informations sont bien réceptionnées, nous allons afficher le nom de la variable fichier dans la procédure lecture().

• Ajouter le test suivant entre les bornes de la procédure lecture() après la déclaration de variables,

MsgBox fichier

• Exécuter l'application en enfonçant la touche F5 du clavier,
• Ajouter les fichiers texte à l'aide du bouton Importer,
• Puis, cliquer sur le bouton Exporter,

Comme vous le remarquez, les deux noms de fichier s'affichent tour à tour dans le MsgBox. La boucle passe en revue chaque élément de la liste. A chaque passage, elle transfère ce nom de fichier à la procédure lecture(). Cette dernière se contente pour l'instant de restituer l'information réceptionnée, à l'écran. Maintenant elle doit être capable d'ouvrir chacun des fichiers afin d'en décortiquer l'information.

Accès séquentiel aux fichiers - Traitement en mémoire
Tout fichier ouvert en mémoire doit être fermé, une fois le traitement terminé, sinon la mémoire n'est pas libérée. En Visual Basic, l'accès à un fichier se fait par l'instruction Open suivi du nom du fichier. Mais ce n'est pas tout, il faut indiquer si l'accès se fait en lecture ou en écriture. Ici nous récupérons les données pour les lire. Nous devons donc l'indiquer par la suite de l'instruction For Input. Pour être lues, ces données sont temporairement stockées en mémoire. La suite de l'instruction As #1 permet d'allouer une case en mémoire vive de l'ordinateur pour ce traitement. Une fois le traitement terminé, cette case mémoire doit donc être vidée par l'instruction Close #1 afin de pointer à la même adresse mémoire.

• A la place du MsgBox dans la procédure lecture(), ajouter ainsi les bornes de l'instruction permettant d'initialiser l'accès en mémoire au fichier passé en paramètre :

Open fichier For Input As #1

Close #1

Entre les bornes de cette instruction doit être effectuée la lecture du fichier jusqu'à la fin. C'est une boucle qui permet ce traitement.

• Ajouter la boucle permettant de parcourir le fichier dans les bornes de l'instruction précédente,

Do While Not EOF(1)
Loop

La boucle Do While se termine par le mot clé Loop. Elle signifie littéralement : Faire tant que. Donc cette instruction doit être suivie du critère qui permet de définir cette condition. La fonction EOF() signifie End Of File (Fin du fichier). Le fichier en question est désigné en argument par l'allocation mémoire que nous avons initialisée pour accéder au fichier, soit le chiffre 1. L'instruction Do While Not EOF(1) signifie donc : Tant que la fin du fichier ouvert en mémoire, n'est pas atteinte.

Dans cette boucle qui permet de parcourir le fichier, nous allons donc maintenant devoir récupérer les informations contenues. Et nous allons le faire ligne à ligne.

• Dans le Do While, ajouter l'instruction suivante :

Line Input #1, texte

L'instruction Line Input effectue la lecture de la ligne suivante dans le fichier. Le fichier est encore une fois désigné par son adressage mémoire (#1). Cette ligne récupérée est stockée dans la variable texte (, texte). Dans le même temps, le pointeur est placé sur la ligne suivante de ce fichier. Ainsi la lecture progresse par une incrémentation naturelle. Nous ne risquons pas une boucle infinie.

Nous devons désormais traiter cette variable texte pour y extraire chaque information séparée d'un point-virgule, indépendamment. Pour ce faire, nous avons besoin d'initialiser les variables numériques qui permettront, à l'aide de fonctions VBA, de découper l'information.

• A la suite de l'instruction précédente, ajouter les affectations suivantes :

depart = 1: position = 1

La variable position doit servir à identifier l'emplacement du point-virgule dans le texte. Chaque fois qu'il est trouvé, l'information doit être découpée jusqu'à cette position. Une nouvelle boucle doit être imbriquée pour parcourir toutes les occurrences de points-virgules à l'intérieur de chaque ligne en cours de lecture.

• A la suite du code, ajouter la boucle suivante :

Do While (position <> 0)
position = InStr(depart, texte, ';', 1)
Loop

La boucle tourne tant que la variable position est supérieure à 0. C'est pourquoi la variable position enregistre l'emplacement du point-virgule dans le texte à l'intérieur de cette boucle. La fonction InStr(), qui demande quatre arguments, renvoie cette valeur. Le premier argument est la position à partir de laquelle la chaîne de texte doit être analysée pour trouver l'occurrence (depart). Le deuxième argument (texte) est la chaîne de caractères à analyser, soit la ligne en cours de lecture dans le fichier texte. Le troisième argument est l'occurrence (';') dont on souhaite prélever la position. Enfin, le quatrième argument est le mode de comparaison utilisé pour effectuer cette recherche. Ce quatrième argument est facultatif. Réglé à 1, le mode de comparaison est textuel.

Si le point-virgule n'est pas trouvé (position=0), cela signifie que nous arrivons au dernier texte de la ligne. Il doit donc être prélevé sur la longueur des caractères restants. C'est la fonction Mid() abordée dans le support de formation sur le traitement de la casse en VBA qui permet de découper un texte. Puis, il faut sortir de la boucle imbriquée afin de poursuivre la lecture des lignes du fichier texte. De plus l'indice de la colonne en cours doit être réinitialisé pour inscrire les données sur la ligne suivante, qui elle donc, doit être incrémentée.

• Pour traduire tout cela, ajouter le code suivant dans la boucle imbriquée :

If position = 0 Then
tampon = Mid(texte, depart)
Sheets('Import').Cells(ligne_enCours, colonne_enCours).Value = tampon
Exit Do
End If

• Après le Loop de la boucle imbriquée mais avant le Loop de la première boucle, réinitialiser les variables de ligne et de colonne comme suit :

colonne_enCours = colonne_debut
ligne_enCours = ligne_enCours + 1

Si le point-virgule n'est pas trouvé, nous prélevons le texte restant jusqu'à la fin des caractères de la ligne (tampon=Mid(texte, depart)). La fonction Mid() prélève une partie de la chaîne (texte) en partant d'une position donnée (depart) sur une longueur définie. Cette longueur est définie par le troisième argument de la fonction Mid(). Lorsqu'il n'est pas renseigné, le texte est prélevé jusqu'à la fin de la chaîne. Concernant l'instruction If le support de formation VBA sur les critères apprend à poser des conditions dans de nombreux contextes.

Nous inscrivons alors le texte prélevé (tampon) dans la cellule de la feuille que nous avons nommée Import (Sheets('Import').Cells(ligne_enCours,colonne_enCours).Value = tampon). Cette inscription se fait en fonction des indices de lignes et de colonnes qui sont actualisés à chaque fois qu'un texte est prélevé ou que la lecture se fait sur une nouvelle ligne du fichier d'importation.

Et nous n'oublions pas de sortir de la boucle imbriquée (Exit Do), pour reprendre la lecture du fichier CSV à l'aide de la première boucle. Comme nous passons sur une nouvelle ligne, pour la feuille Excel, nous réinitialisons la colonne à celle de départ (colonne_enCours =colonne_debut) et passons à la ligne suivante (ligne_enCours = ligne_enCours + 1).

Dans la boucle imbriquée, nous avons traité le cas où le point-virgule n'était plus trouvé mais nous devons aussi traiter le cas où il est trouvé, signifiant que nous ne sommes pas encore à la fin de la chaîne de caractères.

• Dans le Ifde la boucle imbriquée, après le Exit Do, ajouter le traitement suivant :

Else
tampon = Mid(texte, depart, position - depart)

La variable position ne valant pas 0, le point-virgule est trouvé. Le texte doit donc cette fois être prélevé entre la position de départ (depart) et sur une longueur qui permet de rejoindre la position du point-virgule (position - depart). C'est la raison pour laquelle, cette fois-ci, nous fournissons le troisième argument à la fonction Mid(). D'une part ce texte prélevé doit être inscrit dans une cellule de la feuille et d'autre part, les variables doivent être incrémentées.

• Après le End If mais toujours dans la boucle imbriquée, soit avant le Loop, ajouter les affectations suivantes :

Sheets('Import').Cells(ligne_enCours,colonne_enCours).Value = tampon
depart = position + 1
colonne_enCours = colonne_enCours + 1

Tout d'abord, le texte prélevé (tampon) est inscrit dans une nouvelle cellule de la feuille, en fonction de son indice de ligne (ligne_enCours) et de colonne (colonne_enCours). Puis, la valeur de la variable depart est déplacée à la position trouvée pour le point-virgule (depart = position + 1). Ainsi le traitement suivant pour la découpe du texte, dans la boucle imbriquée, repartira après la dernière occurrence trouvée pour le point-virgule. Comme il s'agit de la même ligne, nous devons nous déplacer en colonne pour la prochaine insertion (colonne_enCours = colonne_enCours + 1). Dans le cas où position valait 0, l'instruction Exit Do permettait de sortir de la boucle imbriquée. Ces lignes ne concernent donc que le cas où le point-virgule est trouvé et que le traitement se poursuit sur la même ligne. Il s'agit maintenant de tester le code.

• Enfoncer la touche F5 du clavier pour lancer le formulaire,
• A l'aide du bouton Importer, ajouter les deux fichiers textes,
• Cliquer sur le bouton Exporter,

Vous remarquez que le traitement s'exécute à la perfection. Toutes les informations sont bien découpées et inscrites dans des cellules distinctes, si bien qu'à l'issue, le tableau est reconstitué. Il s'agit maintenant de repérer les doublons dans un premier temps pour les supprimer. Dans un deuxième temps, nous pourrons réaliser l'exportation, en CSV, des données ainsi traitées. Au final, le code complet pour l'accès séquentiel au fichier et la récupération d'information, est le suivant :

Private Sub lecture(fichier As String)
Dim depart As Integer, position As Integer
Dim texte As String, tampon As String

Open fichier For Input As #1
Do While Not EOF(1)
Line Input #1, texte
depart = 1: position = 1

Do While (position <> 0)
position = InStr(depart, texte, ';', 1)
If position = 0 Then
tampon = Mid(texte, depart)
Sheets('Import').Cells(ligne_enCours, colonne_enCours).Value = tampon
Exit Do
Else
tampon = Mid(texte, depart, position - depart)
End If

Sheets('Import').Cells(ligne_enCours, colonne_enCours).Value = tampon
depart = position + 1
colonne_enCours = colonne_enCours + 1
Loop

colonne_enCours = colonne_debut
ligne_enCours = ligne_enCours + 1
Loop
Close #1

End Sub

Traitement des doublons en VBA Excel
La première chose à faire, une fois la lecture des fichiers et l'importation des données terminées, est d'appeler la procédure traitement(). C'est le code de cette dernière, pour l'instant vide, qui permettra de supprimer les doublons. Pour ce faire :

• Ajouter l'appel suivant, dans la procédure exporter_click(), après le traitement de la boucle :

traitement

Comme cette procédure ne requiert aucun argument, son appel se fait simplement par son nom, sans les parenthèses.

Nous allons nous inspirer d'un code que nous avons déjà produit, au travers du support de formation pour nettoyer les tableaux Excel en VBA.

• Recopier le code suivant dans la procédure traitement() :

Dim ligne As Integer: Dim colonne As Integer
ligne = ligne_debut: colonne = colonne_debut

Cells(ligne, colonne).Sort Cells(ligne, colonne), xlAscending, Header:=xlNo

While Cells(ligne, colonne).Value <> ''
If (Cells(ligne, colonne).Value = Cells(ligne - 1, colonne).Value) Then
Cells(ligne, colonne).EntireRow.Delete
ligne = ligne - 1
End If

ligne = ligne + 1
Wend

Nous déclarons tout d'abord les variables ligne et colonne afin de parcourir le tableau à l'aide d'une boucle While. Nous affectons chacune de ces variables aux indices de la première cellule du tableau à traiter (ligne = ligne_debut:colonne = colonne_debut). Pour faciliter la suppression des doublons, nous les regroupons à l'aide d'un tri sur le tableau (Cells(ligne, colonne).Sort Cells(ligne, colonne), xlAscending,Header:=xlNo). Ce tri utilise comme référence, la première cellule du tableau (Cells(ligne, colonne)). C'est elle qui désigne la première colonne dans laquelle sont inscrits les identifiants. Lorsque des identifiants identiques seront trouvés, ils seront supprimés. La méthode Sort de l'objet Cells réalise ce tri selon deux paramètres. Tout d'abord il s'agit d'un tri croissant (xlAscending), soit du plus petit identifiant au plus grand. Ensuite, nous indiquons que ce tableau ne possède pas de ligne d'entête et que tout doit donc être trié (Header:=xlNo). Une fois le tableau trié et les doublons regroupés, nous le parcourons à l'aide d'une boucle While tant que la cellule n'est pas vide, soit tant que la fin du tableau n'est pas atteinte (While Cells(ligne, colonne).Value <> '').

Si l'identifiant en cours de lecture est identique à celui de la ligne précédente (If (Cells(ligne,colonne).Value = Cells(ligne - 1, colonne).Value) Then), nous en déduisons qu'il s'agit d'un doublon. Donc nous supprimons toutes les informations de la ligne de cet identifiant (Cells(ligne, colonne).EntireRow.Delete). La propriété EntireRow de l'objet Cells désigne donc la ligne entière d'une cellule en particulier. De fait, la méthode Delete associée permet de supprimer cette ligne redondante.

Lorsque vous supprimez une ligne entière dans Excel, la sélection active passe à la ligne suivante. La boucle doit incrémenter la variable ligne à chaque passage (ligne = ligne + 1), afin de passer en revue tout le tableau. Dans le cas d'une suppression donc, la variable ligne doit d'abord être décrémentée (ligne = ligne – 1), afin de ne sauter aucune ligne du tableau.

• Enfoncer la touche F5 pour exécuter le formulaire,
• Ajouter les deux fichiers texte à l'aide du bouton Importer,
• Lancer le traitement en cliquant sur le bouton Exécuter,

En même temps que le tableau est importé et se construit, vous remarquez que des lignes disparaissent. A l'issue, nous comptons moins de lignes que précédemment. Les doublons ont été repérés et supprimés. Le résultat livre un tableau purgé que nous pouvons désormais exporter en CSV afin que des logiciels externes puissent récupérer les données propres, par ce type de fichier universel.

Exporter les données d'un tableau Excel au format CSV
Maintenant il s'agit de faire le chemin inverse. Nous devons parcourir le tableau de la première cellule à la dernière, en nous déplaçant ligne à ligne. Il s'agit de récupérer l'information de chaque cellule et de recomposer une ligne entière où chaque information est séparée par un point-virgule. Une fois la ligne reconstituée, elle doit être écrite dans le fichier dont le chemin aura préalablement été indiqué par l'utilisateur via une boîte de dialogue Enregistrer sous standard. Nous devons donc cette fois accéder à ce fichier en écriture (Output) et non en lecture (Input) comme précédemment. Chaque ligne ainsi reconstruite sera ajoutée à la suite dans le fichier. A l'issue, nous obtiendrons une exportation CSV en bonne et due forme exploitable par n'importe quel autre logiciel. Mais pour appeler la procédure ecriture(), nous devons être en mesure de lui passer le chemin d'accès au fichier dans lequel écrire.

• Dans la procédure exporter_click(), ajouter la déclaration suivante :

Dim nom_fichier As String

• Après l'appel de la procédure traitement, ajouter le code suivant :

nom_fichier = Application.GetSaveAsFilename(fileFilter:='TextFiles (*.txt), *.txt')
sortie.Value = nom_fichier
ecriture(nom_fichier)

La méthode GetSaveAsFilename de l'objet Application permet d'appeler la boîte de dialogue standard Enregistrer sous. Nous la filtrons comme précédemment sur les fichiers de type texte (*.txt). Cette méthode renvoie le chemin d'accès au fichier choisi que nous stockons dans la variable nom_fichier. Nous l'inscrivons aussitôt dans la zone de texte du formulaire (sortie.Value = nom_fichier). Puis nous appelons la procédure ecriture(), qui doit se charger de l'export, en lui passant le chemin complet du fichier choisi pour l'enregistrement (ecriture(nom_fichier)).

• Dans la procédure ecriture(), ajouter les déclarations et affectations suivantes :

Dim ligne As Integer, colonne As Integer
Dim texte As String
ligne = ligne_debut: colonne = colonne_debut

Les variables ligne et colonne vont servir à parcourir les cellules du tableau en faisant varier les indices respectifs des cellules. C'est pourquoi nous les initialisons tout de suite aux références de la première cellule (ligne = ligne_debut: colonne = colonne_debut). La variable texte sera utilisée pour recomposer, par concaténation et avec séparateur de liste, une ligne entière du fichier CSV, correspondant à une ligne entière du tableau.

L'écriture dans le fichier désigné par l'utilisateur doit se faire si ce dernier n'a pas cliqué sur le bouton Annuler depuis la boîte de dialogue Enregistrer sous. Pour cela, nous devons ajouter un test :

• Ajouter la vérification suivante de critère à la suite du code :

If LCase(fichier) <> 'faux' Then

End If

Dans les bornes de l'instruction If, significant que le fichier est connu, nous devons accéder à ce dernier en écriture.

• Ajouter l'accès au fichier comme suit :

Open fichier For Output As #1

Close #1

Comme toute à l'heure, nous utilisons l'instruction Open suivi du chemin du fichier pour accéder à ce dernier en mémoire (Open fichier). Mais cette fois-ci, il s'agit d'un accès séquentiel en écriture, ce que nous écrivons (For Output). Et comme précédemment, nous devons lui réserver une allocation mémoire, c'est-à-dire un adressage qui permettra de le désigner pour travailler dessus (As #1). Bien sûr, à l'issue de l'exportation, la mémoire doit être libérée, donc nous fermons tout de suite l'instruction en faisant référence à son adresse en mémoire (Close #1).

Il s'agit maintenant de parcourir tout le tableau ligne à ligne. Pour chaque ligne, nous devons passer en revue toutes les colonnes et à l'issue passer à la ligne suivante. Nous avons donc besoin d'une double boucle :

• Entre les bornes de l'instruction Open, écrire le code des boucles imbriquées :

While Cells(ligne, colonne).Value <> ''
While Cells(ligne, colonne).Value <> ''

colonne = colonne + 1
Wend

ligne = ligne + 1
Wend

La première boucle fait varier l'indice de ligne (ligne = ligne + 1). La seconde fait ainsi varier tous les indices de colonne pour un indice de ligne (colonne = colonne + 1). Le critère Cells(ligne,colonne).Value <> '' consiste à faire varier tous les indices de colonne tant que le bord droit n'est pas atteint pour la deuxième boucle. Pour la première, il s'agit de faire varier tous les indices de ligne, tant que le bord inférieur du tableau n'est pas atteint.

• Dans la boucle imbriquée, ajouter le code permettant de reconstruire chaque ligne du fichier, avant l'instruction colonne = colonne + 1 :

texte = texte & Cells(ligne, colonne).Value & ';'

Tant que nous sommes sur la même ligne, nous ajoutons au texte précédemment récupéré (texte = texte &), le contenu de la cellule en cours de lecture avec un point-virgule pour le séparateur de liste du CSV (Cells(ligne,colonne).Value & ';'). Une fois que la ligne entière est reconstituée, soit à la fin de la boucle imbriquée, nous devons écrire cette ligne dans le fichier texte et réinitialiser les variables.

• Dans la première boucle, après la seconde et avant l'instruction ligne = ligne + 1, ajouter le code suivant :

Print #1, texte
texte = ''
colonne = colonne_debut

L'instruction Print permet d'écrire dans le fichier texte repéré par son adresse mémoire (#1), la ligne reconstituée et stockée dans la variable texte. Comme nous attaquons une nouvelle ligne, nous devons purger la variable texte (texte = '') et réinitialiser l'indice à la première colonne (colonne = colonne_debut) pour pouvoir parcourir toutes les cellules de la ligne suivante. Le code est terminé, il ne reste plus qu'à le tester :

• Enfoncer F5 pour exécuter le programme,
• Ajouter les deux fichiers texte à l'aide du bouton Importer,
• Cliquer sur le bouton Exporter,
• A l'invite de la boîte de dialogue standard Enregistrer sous, désigner un dossier et saisir un nom de fichier sans extension,
• Cliquer sur Enregistrer,

L'exécution se termine. Si vous ouvrez le fichier texte ainsi créé, vous remarquez que son contenu est strictement identique à celui du tableau Excel mais au format CSV. Chaque cellule du tableau est séparée d'une autre par un point-virgule. Et chaque ligne du tableau est située sur une nouvelle ligne du fichier CSV. L'exportation des données par le biais de l'écriture séquentielle en mémoire a donc parfaitement réussi.


Le code complet de la procédure ecriture() pour l'exportation des données au format CSV, est le suivant :

Private Sub ecriture(fichier As String)
Dim ligne As Integer, colonne As Integer
Dim texte As String

ligne = ligne_debut: colonne = colonne_debut

If LCase(fichier) <> 'faux' Then
Open fichier For Output As #1
While Cells(ligne, colonne).Value <> ''
While Cells(ligne, colonne).Value <> ''
texte = texte & Cells(ligne, colonne).Value & ';'
colonne = colonne + 1
Wend

Print #1, texte
texte = ''
colonne = colonne_debut
ligne = ligne + 1
Wend

Close #1
End If
End Sub

Il ne reste plus qu'à coder le bouton Fermer.

• Stopper l'exécution du programme,
• Double cliquer sur le bouton Fermer depuis le formulaire pour créer sa procédure événementielle,
• Saisir le code suivant :

liste_fichiers.Clear
formulaire.Hide

La méthode Clear d'un contrôle Zone de liste permet d'effacer tout son contenu. La méthode Hide d'un objet Formulaire permet de masquer ce dernier. Si bien que désormais, si vous cliquez sur le bouton Fermer et que vous relancez le formulaire, vous redémarrez d'une interface vierge.