Importer des données multimédias en Visual Basic Excel

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


Sujets que vous pourriez aussi aimer :


Importer les données Multimédias en VBA Excel

Dans cette formation VBA Excel, nous souhaitons réaliser l'importation de données, qui sont associées à des images. L'objectif principal consiste à restituer l'affichage des éléments multimédias et à les attacher aux cellules des enregistrements importés. Il s'agit donc d'exploiter le résultat de l'exportation CSV fournie par la formation précédente en VBA Access.



Comme l'illustre la capture ci-dessus de l'application finalisée, tous les enregistrements sont restitués explicitement sur des lignes différentes. Et pour chacun d'entre eux, les informations de champ sont parfaitement réparties dans leurs colonnes. La dernière d'entre elles propose même l'affichage du fichier multimédia attaché. C'est le code Visual Basic Excel qui permet de charger les images pour les insérer dans des cellules de la feuille.

Sources et présentation de la problématique
Pour réaliser cette importation automatisée par le code VBA, nous devons récupérer plusieurs sources. Le fichier CSV d'exportation est nécessaire. C'est lui qui renferme toutes les informations textuelles exportées depuis Access. Bien entendu, nous avons aussi besoin des images ainsi que du classeur source.

• Télécharger le fichier importer-donnees-multimedias.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous le remarquez, la décompression produit deux fichiers et un sous dossier. Il y a tout d'abord le classeur Excel : importer-donnees-multimedias.xlsm, à partir duquel nous allons réaliser les travaux VBA. Le sous dossier images propose toutes les miniatures des enregistrements exportés. Ces derniers sont listés dans le fichier exportation.txt.

• Double cliquer sur le fichier exportation.txt pour l'ouvrir,

C'est le caractère de la barre verticale qui est utilisé comme séparateur de liste afin de fractionner l'information de champ, pour chaque enregistrement.

Il s'agit à l'origine d'une base de données des idées de sorties référencées par activité, département et ville. Vous notez de même la présence des noms de fichiers pour les images. Il va donc s'agir d'utiliser cette information pour charger la miniature correspondante dans Excel, depuis le sous dossier images.



Un fichier CSV est un moyen de communication universel entre tous les logiciels. Mais bien entendu à ce stade, les données ne sont pas structurées et sont compliquées à déchiffrer. C'est l'importation par le code VBA Excel, qui doit permettre de restituer leur organisation, pour une présentation explicite.

• Fermer le fichier texte,
• Dans le dossier de décompression, double cliquer sur le classeur importer-donnees-multimedias.xlsm pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Seule la feuille Importation compose ce classeur. Elle propose la structure pour recevoir les données externes. Les colonnes sont organisées dans le même ordre que les informations de champ dans le fichier Csv. Vous notez la présence d'un bouton de Macro (MAJ). Il est inopérant à ce stade. Il doit être attaché à une macro VBA pour lancer l'exécution des traitements. Et ces macros VBA n'existent pas encore puisqu'il s'agit de les coder. Enfin, si vous faites défiler la feuille vers le bas à l'aide de l'ascenseur vertical, vous constatez que les cellules s'engouffrent sous la ligne 2. Nous avons en effet figé les volets pour conserver l'affichage des titres.

Nettoyer les cellules d'une feuille Excel automatiquement
Nous partons du principe que les données sont actualisées à chaque nouvelle exportation. De fait, un clic sur le bouton Maj doit permettre de réaliser la mise à jour. Les précédentes importations doivent donc d'abord être purgées avant que le fichier Csv ne soit traité. Nous proposons de créer la procédure purger pour nettoyer ces cellules. Ce nettoyage doit intervenir à partir de la ligne 3, tant qu'un contenu est détecté. Les colonnes B à H (indices 2 à 8) sont concernées par ce traitement. Ce code doit être appelée par la procédure principale que nous nommerons : import.

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

Nous affichons ainsi la feuille de code associée à la feuille Excel sur laquelle nous souhaitons réaliser le traitement. Pour basculer dans l'éditeur Visual Basic Excel, nous aurions aussi pu exploiter le ruban Développeur.

• Dans la feuille de code au centre de l'écran, créer les deux procédures comme suit :

Option Explicit

Sub import()

End Sub

Sub purger()

End Sub

Nous avons ajouté l'instruction Option Explicit en entête de code afin que l'éditeur veille à la bonne déclaration de nos variables. Justement, c'est ce par quoi nous devons débuter.

• Entre les bornes de la procédure purger, ajouter les déclarations et affectations suivantes :

Dim ligne As Integer: Dim colonne As Byte
Dim image As Object

ligne = 3: colonne = 2

Les variables ligne et colonne parlent d'elles-mêmes. Elles doivent servir à parcourir les cellules en hauteur et en largeur pour les nettoyer avant importation, lorsqu'elles ne sont pas vides. Les lignes peuvent être nombreuses. Nous typons donc la variable comme un Integer afin de ne pas risquer un dépassement de capacité. Les colonnes à parcourir sont connues. Il n'y en a que 7, de B à H. Nous typons donc la variable comme un entier court, soit un Byte. Puis nous les initialisons de manière à désigner la première cellule. B3 est en effet située en ligne 3 et en colonne 2.

Enfin, vous notez la déclaration d'une variable particulière typée comme un objet (Object). En effet, le fait de supprimer le contenu des cellules n'aura aucune incidence sur les images importées. Or elles doivent être actualisées au même titre que les données textuelles. Donc elles doivent d'abord être supprimées. Cet objet doit donc servir à parcourir l'ensemble des images recensées dans la collection des éléments multimédias pour la feuille en cours.

Les cellules aussi doivent être parcourues, tant qu'elles ne sont pas vides. Le traitement récursif dépend d'un critère. Donc nous devons utiliser une boucle While.

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

While Cells(ligne, colonne).Value <> ""
For colonne = 2 To 8
Cells(ligne, colonne).Value = ""
Next colonne

ligne = ligne + 1: colonne = 2
Wend

La boucle While permet de tester le contenu de la cellule en cours, repérée par son indice de ligne et de colonne. Si elle n'est pas vide, le contenu de chaque cellule sur la largeur du tableau doit être supprimé. C'est pourquoi pour chaque ligne, nous passons en revue toutes les colonnes de B à H (2 à 8) grâce à une boucle For. Et dans ce double traitement itératif donc, nous supprimons le contenu de chaque cellule passée en revue, grâce à l'affectation de la propriété Value de l'objet Cells. Enfin, avant de boucler, nous n'oublions pas d'incrémenter la variable ligne, pour ne pas rester sur place. Et puis, nous réinitialisons la variable colonne à l'indice 2, pour que le critère de la boucle se réalise sur la colonne B, pour la ligne suivante.

Le traitement de nettoyage n'est pas terminé. Il s'agit désormais de supprimer toutes les images potentiellement présentes sur la feuille. Nous devons toutes les parcourir. L'objet VBA Excel ActiveSheet propose la propriété Pictures. Cette dernière renvoie la collection d'images recensées sur la feuille active. En Visual Basic, c'est la boucle For Each qui permet de parcourir tous les éléments d'un groupe d'éléments, soit toutes les images dans la collection. C'est la raison pour laquelle nous avons typée notre variable image comme un Object.

• A la suite du code de la procédure purger, ajouter la boucle de traitement des images :

For Each image In ActiveSheet.Pictures
image.Delete
Next image

Notre variable objet est ainsi typée à la volée comme une image, grâce à la boucle For Each. De fait, elle hérite de la méthode Delete qui permet de supprimer chacune d'entre elles, tant que la dernière n'est pas atteinte.

Notre procédure purger est terminée. Si vous inscrivez quelques informations sur plusieurs lignes entre les colonnes B et H, vous constatez en effet leur suppression au déclenchement du code. Nous testerons son efficacité sur les images lorsque nous les aurons importées. Le code complet de la procédure purger est le suivant :

Sub purger()
Dim ligne As Integer: Dim colonne As Byte
Dim image As Object

ligne = 3: colonne = 2

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

For colonne = 2 To 8
Cells(ligne, colonne).Value = ""
Next colonne

ligne = ligne + 1: colonne = 2
Wend

For Each image In ActiveSheet.Pictures
image.Delete
Next image

End Sub

Importer les données d'un fichier CSV en VBA
Il s'agit désormais d'accéder au fichier exportation.txt par le code VBA Excel. Les enregistrements doivent être récupérés tour à tour. En conséquence, le fichier texte doit être parcouru ligne à ligne. Donc une boucle de traitement est de nouveau nécessaire. Et pour chaque enregistrement, les informations de champ doivent être découpées sur le caractère remarquable de la barre verticale. Donc, nous avons besoin d'un tableau de variables pour les ranger indépendamment.

• Entre les bornes de la procédure import, ajouter les déclarations et instructions suivantes :

Dim ligne As Integer: Dim colonne As Byte
Dim chaine As String: Dim tab_contenu() As String

ligne = 3
purger

Pour importer les données ligne à ligne et les restituer dans leurs colonnes respectives, nous déclarons de nouveau les variables ligne et colonne. Nous les typons comme précédemment pour les mêmes raisons. La variable chaine est déclarée comme un string afin de réceptionner la ligne en cours de lecture dans le fichier d'exportation. C'est elle qui doit être découpée sur le séparateur de liste, afin de fractionner l'information par champ. Donc nous déclarons le tableau de variables tab_contenu pour les accueillir dans ses rangées.

Enfin, nous initialisons la variable ligne à 3 pour définir le point de départ de l'importation. Puis, nous appelons notre procédure purger afin de nettoyer la feuille avant de débuter le traitement.

Il s'agit désormais d'accéder au fichier externe en lecture. La formation VBA Excel pour importer et exporter les données nous avait appris les techniques. Il s'agit d'exploiter l'instruction VBA Open pour désigner le fichier à charger en mémoire afin d'accéder à son contenu.

• A la suite du code de la procédure import, ajouter les instructions suivantes :

Open ThisWorkbook.Path & "\exportation.txt" For Input As #1

Close #1

Grâce à la propriété Path de l'objet VBA ThisWorkbook, nous récupérons le chemin d'accès complet au classeur Excel. Nous le concaténons de manière à accéder au fichier exportation.txt. Nous définissons un mode d'accès en lecture (For Input). Comme ces accès séquentiels se produisent nécessairement en mémoire, nous attribuons un numéro arbitraire d'adressage (#1). Par la suite, c'est grâce à ce numéro que nous désignerons le fichier. Nous n'oublions pas de fermer cet accès à l'issue du traitement (Close #1). Et comme vous le constatez, pour libérer la Ram de ce fichier, nous le désignons bien par son adresse.

Désormais, nous devons coder les instructions entre ces bornes, pour récupérer les données. Il s'agit tout d'abord de parcourir l'ensemble du fichier, ligne à ligne. Nous devons donc créer une boucle capable de le parcourir jusqu'à la fin.

• Entre les instructions Open et Close, ajouter les lignes VBA suivantes :

Do While Not EOF(1)
Line Input #1, chaine

ligne = ligne + 1
Loop

Nous l'avons présentée à de nombreuses reprises, la fonction EOF signifie littéralement End Of File. Précédée de l'instruction Not, elle permet donc à la boucle Do While de parcourir le fichier texte, tant que la fin n'est pas atteinte, soit la dernière ligne. Et à l'intérieur de la boucle, bornée par le mot clé Loop, nous réceptionnons la ligne en cours de lecture, dans la variable chaine. C'est l'instruction Line Input qui récupère la ligne du fichier désigné par son adresse mémoire (#1). Nous n'oublions pas d'incrémenter la variable ligne sur le même rythme (ligne = ligne + 1). C'est elle qui permettra de restituer les enregistrements les uns en dessous des autres sur la feuille Excel.

A chaque passage dans la boucle, nous réceptionnons donc un nouvel enregistrement. Celui-ci doit être découpé sur le séparateur de liste, grâce à la fonction VBA Split. Chaque donnée ainsi séparée pourra être rangée dans le tableau de variables.

• A la suite du code dans la boucle, avant l'incrémentation de la variable ligne, ajouter les instructions VBA suivantes :

tab_contenu = Split(chaine, "|")
DoEvents

Nous passons le texte à découper en premier argument de la fonction Split. En deuxième argument, nous lui indiquons le caractère sur lequel la découpe doit être opérée. Le résultat, soit toutes les informations de champs, est affecté au tableau de variables tab_contenu. Désormais, chaque donnée est rangée dans une ligne différente du tableau. Enfin, nous exploitons l'instruction VBA DoEvents qui permet de rendre la main à chaque passage. Nous l'avions exploitée dans la formation VBA Access pour créer une visionneuse automatique d'images, capable de se stopper sur demande de l'utilisateur. Ici, elle permettra de visualiser l'importation des données dans la feuille Excel, au fur et à mesure de leur lecture par le code VBA.

Ces données séparées doivent être restituées entre les colonnes B et H. Nous proposons donc d'exploiter de nouveau une boucle For, pour parcourir les colonnes de l'indice 2 à l'indice 8. Dans le même temps, elle permettra de parcourir les lignes du tableau de variables, moyennant l'indice à adapter.

• Après l'instruction DoEvents, ajouter la boucle For comme suit :

For colonne = 2 To 8

Next colonne

A l'intérieur de cette boucle, le traitement est identique pour les colonnes B à G. Il s'agit d'y importer les informations textuelles telles qu'elles ont été découpées dans le tableau de variables. Pour la colonne H d'indice 8, le principe est différent. Après avoir réceptionné le nom du fichier multimédia, le code VBA doit y insérer la miniature correspondante, située dans le sous dossier images. Pour envisager ces deux cas différents, nous devons donc exploiter une instruction conditionnelle If. Son objectif est de tester si l'indice de colonne en cours est 8 ou s'il est inférieur. Selon le cas, elle permettra d'enclencher le traitement adapté.

• Dans les bornes de la boucle For, ajouter l'instruction VBA conditionnelle suivante :

If (colonne < 8) Then

Else

End If

Si l'indice de colonne en cours de lecture n'est pas le dernier, nous procèderons à la restitution des informations textuelles de champ, contenues dans le tableau de variables. Sinon, nous procèderons à la récupération et au chargement du fichier image.

Nous proposons de débuter par le cas le plus simple. Il s'agit de restituer les informations de champ, dans leurs colonnes respectives.

• Pour ce faire, dans la branche If de l'instruction conditionnelle, ajouter la ligne suivante :

Cells(ligne,colonne).Value = Replace(tab_contenu(colonne - 2), "#","'")

Nous affectons la cellule en cours, pour la ligne et la colonne variables, au contenu rangé dans le tableau de variables, dans la même colonne. Etant donné que la zone d'exportation sur la feuille commence à partir de la deuxième colonne, nous retranchons deux unités pour pointer sur la rangée correspondante du tableau de variables (tab_contenu(colonne- 2)). Ce dernier est passé en paramètre de la fonction VBA Replace. En effet, dans la base de données source, les apostrophes étaient encodées par des symboles dièse (#). Nous restituons les apostrophes de cette manière, au moment de l'affectation.

Nous pouvons déjà tester l'importation des données textuelles à ce stade.

• Enregistrer les modifications (CTRL + S),
• Basculer sur la feuille Importation du classeur Excel (ALT + F11),
• Cliquer avec le bouton droit de la souris sur le bouton MAJ situé en cellule A2,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la boîte de dialogue, sélectionner la procédure Feuil1.Import et valider,
• Cliquer dans une cellule de la feuille pour désélectionner le bouton,
• Puis, cliquer sur le bouton lui-même,

Comme vous le constatez, les enregistrements sont parfaitement récoltés et restitués ligne à ligne. Pour chacun d'entre eux, l'information est découpée sur le caractère remarquable, grâce à la fonction VBA Split. De fait, nous récupérons des données lisibles, organisées dans leurs colonnes respectives. Elles sont donc parfaitement exploitables. De même, vous notez que la fonction VBA Replace a fait son travail puisque toutes les apostrophes ont remplacé les symboles du dièse, dans le champ de la description. Dans la base de données Access d'origine, nous avions 271 enregistrements. Notre importation renseigne les cellules jusqu'à la ligne 273, comme l'illustre la capture ci-dessous. Etant donné qu'elle débute à partir de la ligne 3, nous avons bien récupéré l'intégralité des enregistrements. Notre code VBA Excel pour l'importation des données textuelles à partir d'un fichier CSV est donc parfaitement fonctionnel.



Il nous reste à traiter le cas de l'indice de colonne 8 dans la boucle For. Le dernier champ correspond en effet au nom du fichier de la miniature. Cette image doit être insérée en pointant sur le fichier spécifié, grâce à son chemin d'accès complet. Une macro automatique pourrait fournir les lignes de code VBA. Mais nous proposons de les présenter ici.

• Revenir dans l'éditeur de code Visual Basic Excel (ALT + F11),
• Dans la branche Else de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

Cells(ligne, colonne).Select
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\images\" & tab_contenu(colonne - 2)).Select
Selection.ShapeRange.Name = tab_contenu(colonne - 2)
ActiveSheet.Shapes.Range(Array(tab_contenu(colonne - 2))).Select
Selection.Placement = xlMoveAndSize

Pour insérer une photo dans une cellule désignée, encore faut-il la sélectionner préalablement. C'est ce que nous faisons pour la cellule en cours, grâce à la méthode Select de l'objet Cells. Comme nous l'avions vu dans la boucle de nettoyage, la propriété Pictures de l'objet ActiveSheet renvoie la collection d'images recensées sur la feuille en cours. La méthode Insert permet d'ajouter une image à cette collection. Pour cela, nous lui passons son chemin d'accès complet en paramètre. tab_contenu(colonne - 2) restitue le nom de la miniature, issu du fichier Csv. Comme toujours, ThisWorkbook.Path renvoie le chemin d'accès complet au dossier du classeur actif. Nous concaténons ce chemin au sous dossier images (& "\images\"), pour atteindre la miniature spécifiée. Ensuite, la méthode Select permet de garder l'objet image actif.

De fait, grâce à l'objet Selection, nous lui attribuons le nom du fichier image avec la propriété dérivée Name de l'objet ShapeRange. Grâce à ce nom, nous pouvons accéder facilement à l'image ainsi nommée. Enfin la propriété Placement d'un objet Picture permet de définir son comportement par rapport à la cellule. L'attribut xlMoveAndSize spécifie que l'image doit être déplacée et dimensionnée avec la cellule. Entre d'autres termes, elle lui est liée. Ce réglage est particulièrement important pour les formations à venir. Nous verrons en effet comment il est possible d'extraire des données multimédias, selon des critères de recherche, uniquement par les formules Excel. Pour qu'elles fonctionnent en effet, elles doivent pouvoir désigner la cellule de l'image. Et donc, l'image doit lui être attachée.

Il s'agit désormais de tester le code VBA pour vérifier que l'importation des données Multimédias fonctionne.

• Enregistrer les modifications et basculer sur la feuille Importation,
• Cliquer sur le bouton MAJ pour lancer l'exécution de la procédure Import,

Tout d'abord, vous constatez que les précédentes importations textuelles sont parfaitement purgées. Puis les nouveaux enregistrements importés apparaissent au fur et à mesure, grâce à l'instruction DoEvents. Et cette fois, chacun est en effet accompagné de sa miniature, insérée dans la colonne H.



Nous avons donc parfaitement réussi l'importation des données Multimédias, par le code Visual Basic Excel. Nous devons néanmoins procéder à deux vérifications. Nous n'avons pas encore eu l'occasion de tester le bon fonctionnement de la boucle For Each dans la procédure purger, pour supprimer toutes les images.

• Clique de nouveau sur le bouton MAJ pour réaliser une nouvelle importation,

Les données textuelles disparaissent fort logiquement dans un premier temps. Chronologiquement, cette boucle de traitement est en effet placée avant celle des images. Puis, toutes les images disparaissent pour fournir une feuille purgée. Et la nouvelle importation débute.

Nous devons aussi nous assurer que chacune des miniatures est parfaitement liée à sa cellule.

• Cliquer avec le bouton droit de la souris sur l'une des images importées,
• Dans le menu contextuel, choisir Format de l'image,
• Dans le volet qui apparaît, cliquer sur l'icône de menu Taille et propriétés,
• Puis, déployer la rubrique Propriétés,

Comme vous le constatez, la propriété Déplacer et dimensionner avec les cellules est cochée. Chaque image est donc parfaitement intégrée pour être exploitée par les calculs, pour fournir des extractions multimédias. Sachez que ce n'est pas le cas par défaut. Si vous insérez une image manuellement, celle-ci est libre de se déplacer indépendamment. C'est la propriété Placement d'un contrôle Image réglée sur xlMoveAndSize par le code VBA qui a permis de la verrouiller.

Pour de tels résultats, le développement reste relativement simple. Le code VBA complet de la procédure import est le suivant :

Sub import()
Dim ligne As Integer: Dim colonne As Byte
Dim chaine As String: Dim tab_contenu() As String

ligne = 3
purger

Open ThisWorkbook.Path & "\exportation.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, chaine

tab_contenu = Split(chaine, "|")
DoEvents

For colonne = 2 To 8
If (colonne < 8) Then
Cells(ligne, colonne).Value = Replace(tab_contenu(colonne - 2), "#","'")
Else
Cells(ligne, colonne).Select
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\images\" & tab_contenu(colonne - 2)).Select
Selection.ShapeRange.Name = tab_contenu(colonne - 2)
ActiveSheet.Shapes.Range(Array(tab_contenu(colonne - 2))).Select
Selection.Placement = xlMoveAndSize
End If
Next colonne

ligne = ligne + 1
Loop
Close #1

End Sub