Sauts de ligne en retours chariot 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 :


Sauts de ligne en retours chariots

Nous poursuivons ici le développement de notre application consistant à analyser les numéros des tirages du loto. Dans les deux précédents volets, nous avons tout d'abord réussi à importer automatiquement le fichier compressé de l'historique de tous ces tirages depuis le site de la française des jeux. Dans un deuxième temps, nous avons réussi à décompresser automatiquement ce fichier pour livrer le document Csv prêt à l'emploi. A priori, il ne nous reste plus qu'à importer de façon sélective les données de ce fichier Csv dans la grille prévue à cet effet sur la feuille Excel. Cependant, il n'est pas rare que ces importations livrent des surprises. C'est le cas ici. Chaque ligne du fichier Csv est séparée d'une autre, non pas par un retour à la ligne (Chr(13)) mais par un saut de ligne ((Chr(10))). En conséquence, les méthodes classiques d'importation ligne à ligne ne fonctionnent pas et nous allons le démontrer.

Classeur Excel à télécharger
Pour poursuivre l'étude, nous devons premièrement récupérer les précédents travaux au dernier indice.

• Télécharger le fichier compressé sauts-ligne-retours-chariots en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous le constatez, la décompression livre le classeur Excel accompagné d'un fichier texte et d'un sous dossier vide, nommé tirages. Il est destiné à accueillir le téléchargement et sa décompression. Le fichier texte embarque les procédures VBA des développements précédents. Nous avons choisi de les héberger ainsi pour passer sous les radars des antivirus. En effet, nous y exploitons des objets et méthodes particuliers jugés malveillants par ces derniers, à tort cependant.

• Double cliquer sur le classeur pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons la grille destinée à accueillir les tirages des numéros du loto. En haut à droite, le bouton Actualiser doit déclencher tout le processus en commençant par le téléchargement et la décompression du fichier distant.

Procédures de téléchargement et décompression
Avant de poursuivre le développement et avant de constater les soucis qui se dressent, nous devons réintégrer les deux procédures VBA destinées à télécharger et à décompresser le fichier émanant de la française des jeux.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1 (tirages),

Nous affichons ainsi sa feuille de code au centre de l'écran. Et nous constatons en effet qu'elle est désolément vide.

• Y coller tout le code contenu dans le fichier Code.txt,

Il s'agit de deux procédures nommées respectivement actualiser_Click et decompresser. La première est associée au bouton de la feuille Excel. Elle s'occupe du téléchargement. A la fin de son traitement, elle appelle la seconde pour organiser la décompression du fichier téléchargé.

De plus, une référence doit être ajoutée au projet. Il s'agit de la référence destinée à exploiter les lignes de commandes issues du Dos. Elle a disparu du fait nous ayons été obligés de retirer le code VBA, pour ne pas alerter les antivirus au téléchargement des sources.

• En haut de l'éditeur VBA Excel, cliquer sur le menu Outils,
• Dans les propositions, choisir l'option Références,
• Dans la boîte de dialogue, cocher la case Microsoft Shell Controls And Automation,
• Puis, valider cet ajout en cliquant sur le bouton Ok de la boîte de dialogue,

Désormais, si vous revenez sur la feuille et que vous cliquez sur le bouton Actualiser, en apparence rien ne se produit, et pourtant ! Si vous ouvrez le sous dossier tirages issu de la décompression, vous notez la présence d'un fichier compressé et de son extraction au format Csv.

Les sauts de ligne du Csv
C'est ce fichier Csv que nous allons devoir analyser. En l'absence de retours à la ligne, remplacés par des sauts de ligne, l'accès séquentiel par le code VBA ne peut se faire. Pourtant, à la lecture dans un éditeur par l'utilisateur, le rendu est le même mais pas pour le code VBA.

• Revenir dans l'éditeur VBA Excel,
• Sous la procédure decompresser, créer la procédure importer comme suit :

Sub importer(Fichier As String)
On Error Resume Next 'Cas dépassement fin fichier
Dim ligne As Integer: Dim compteur As Integer: Dim i As Byte
Dim texte As String: Dim elements As Variant

End Sub

Nous la signons avec un paramètre en attente. Il s'agit du chemin du fichier Csv à atteindre. En effet, ce code VBA est destiné à être appelé par la procédure actualiser_Click après le téléchargement et la décompression. Nous déclarons tout d'abord trois variables numériques. La première (ligne) doit suivre la phase d'importation pour écrire les numéros des tirages sur les lignes consécutives de la feuille Excel. La deuxième (compteur) sera utilisée à titre de test en suivant l'évolution de la lecture du fichier Csv. Grâce à elle, nous saurons que la ligne d'entête est dépassée et nous pourrons commencer l'importation des numéros sans les titres. La dernière (i) permettra de passer en revue les colonnes de la feuille pour une importation optimisée, par le biais d'une boucle. Ensuite, nous déclarons une variable de type texte (As String) pour réceptionner tour à tour chaque ligne du fichier Csv. Comme nous les découperons sur le délimiteur qui est le point-virgule (;) ici, nous aurons besoin de ranger indépendamment chaque information (numéro) dans un tableau de variables. C'est la raison pour laquelle nous déclarons la variable elements comme un Variant.

Chaque ligne du fichier Csv
Les méthodes d'accès séquentiels aux fichiers externes, nous les connaissons bien désormais avec tous les travaux que nous avons aboutis ensemble. Donc, nous proposons de les restituer dans un premier temps pour la lecture.

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

...
ligne = 4: compteur = 1

Open Fichier For Input As #1
Do While Not EOF(1)
Line Input #1, texte

MsgBox texte
compteur = compteur + 1
Loop
Close #1

...

Nous accédons (Open) en lecture (For Input) au fichier passé en paramètre de la procédure. Pour cela, nous définissons un adressage mémoire libre. Le numéro 1 (#1) fait parfaitement l'affaire dans la mesure où nous n'avons pas entrepris d'autres accès dans le laps de temps. Nous parcourons chaque ligne de ce fichier Csv tant que la dernière ligne n'est pas atteinte (Do While Not EOF(1)). Pour cela et comme vous le remarquez, nous rappelons le numéro d'allocation (1), puisque nous travaillons sur ce fichier dans la mémoire de l'ordinateur. A chaque passage, nous récoltons la ligne en cours d'analyse dans la variable texte (Line Input #1, texte). A chaque passage de même (MsgBox texte), nous choisissons, à titre de vérification, d'afficher le contenu ainsi récolté à l'écran, par le biais de la fonction VBA MsgBox.

Mais pour que cette fonction livre son verdict, elle doit être appelée par la procédure actualiser_Click après le téléchargement et la décompression.

• Dans la branche du If de la procédure actualiser_Click, ajouter l'appel suivant (en gras) :

...
flux.SaveToFile chemin, 2
flux.Close
decompresser chemin, ThisWorkbook.Path & "\tirages\"
importer Replace(chemin, ".zip", ".csv")
Else
MsgBox "Une erreur est survenue"
...

Sur le chemin d'accès complet, nous remplaçons l'extension zip par l'extension csv pour pointer directement sur le fichier décompressé et ce, grâce à la fonction VBA Replace.

Désormais et si vous revenez sur la feuille et que vous cliquez sur le bouton Actualiser, vous obtenez une réponse cinglante.



Ce n'est pas la première ligne du fichier Csv qui est retournée mais l'intégralité du contenu, certes limité à quelques centaines de caractères dans un MsgBox. Vous aurez beau cliquer sur le bouton Ok, aucune ligne suivante ne défilera.

Remplacer les sauts par des retours à la ligne
Donc pour imaginer entreprendre la lecture de ce fichier Csv ligne à ligne, afin d'en découper les informations sur le séparateur (;), nous devons remplacer tous les sauts de ligne (Chr(10)) par des retours à la ligne (Chr(13)), puisqu'ils ne sont pas détectés en l'état par la méthode d'accès du VBA.

• Remplacer le précédent accès en lecture par le suivant :

...
ligne = 4: compteur = 1

Open Fichier For Input As #1
Do While Not EOF(1)
Line Input #1, texte
texte = Replace(texte, Chr(10), Chr(13))
Loop
Close #1

Open Fichier For Output As #1
Print #1, texte
Close #1

Nous accédons donc de nouveau au fichier en lecture (For Input). Comme il n'est composé que d'une ligne aux yeux du VBA, grâce à la fonction Replace, nous remplaçons tous les sauts de ligne (Chr(10)) par des retours à la ligne (Chr(13)), dans la variable texte pour l'instant stockée en mémoire.

C'est alors que nous accédons à ce même fichier, même en écriture cette fois (For Output). Grâce à l'instruction Print, nous remplaçons l'intégralité de son contenu par ce même contenu hébergeant cette fois des retours à la ligne en lieu et place des anciens sauts de ligne.

Contrôler les lignes parcourues
Suite à cette modification de masse, nous devons maintenant nous assurer que les lignes du fichier Csv peuvent être interprétées indépendamment. Pour cela, nous proposons d'accéder une fois encore au fichier en lecture, pour restituer le contenu de quelques premières lignes. Si nous parvenons à les faire défiler, nous saurons que nous pourrons prochainement entreprendre son analyse séquentielle pour importer ses données dans la feuille du classeur Excel.

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

...
Open Fichier For Input As #1
Do While Not EOF(1)
Line Input #1, texte
MsgBox texte
If compteur > 5 Then Exit Do
compteur = compteur + 1
Loop
Close #1
...

Au-delà de cinq passages, nous stoppons (Exit Do) l'exécution de la boucle. Nous souhaitons seulement constater que nous avons explicitement séquencé les données du fichier Csv sur des lignes différentes, pour que le code VBA puisse les interpréter indépendamment.

• Enregistrer les modifications et revenir sur la feuille Excel,
• Puis, cliquer sur le bouton Actualiser,
• A l'invite de la boîte de dialogue, cliquer à deux ou trois reprises sur le bouton Ok.

Cette fois, les lignes défilent. Cela signifie que nous serons en mesure, dans le prochain volet, de récolter les informations de champs qui nous intéressent pour les importer dans la grille de la feuille Excel.