Modifier les propriétés de fichiers 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 :


Accès aux propriétés des fichiers en VBA

Dans ce volet, nous souhaitons démontrer l'intérêt de manipuler les attributs des fichiers externes par le code Visual Basic, notamment avec Excel. Avec Access, nous avions appris à accéder aux fichiers d'un dossier dans le but d'archiver les informations en base de données. Ainsi, nous avions récupéré les extensions, dates de créations et noms des fichiers. Ici nous souhaitons aller plus loin en modifiant des propriétés spécifiques pour masquer les fichiers et les archiver en lecture seule. L'objectif est de pouvoir exporter et sauvegarder toutes les factures clients émises, tout en les protégeant.

Données de facturation à archiver
Pour réaliser ces travaux, nous proposons de récupérer une facturation automatisée conçue au fil des précédentes formations. Un code Visual Basic permet en outre de mettre les stocks à jour à validation.

• Télécharger le classeur acces-fichiers-externes.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

A l'ouverture du classeur de facturation, une interface graphique surgit au premier plan. C'est elle qui contrôle et ordonne les produits à intégrer sur la facture.



Le code Visual Basic Excel réceptionne les choix utilisateur par le biais des contrôles de l'interface graphique. Il inscrit alors à la suite de la facture les renseignements sur le code article et la quantité achetée. Et là, ce sont des fonctions Excel, notamment la rechercheV qui permettent d'extraire les informations correspondantes, comme la désignation et le prix unitaire, issus de la feuille articles.

• Choisir la référence b003 par exemple, à l'aide de la liste déroulante,
• Saisir une quantité de 2 dans la zone de texte prévue à cet effet,
• Cliquer ensuite sur le bouton Ajouter,
• Choisir ensuite la référence b007 en conservant la quantité à 1,
• Puis, cliquer de nouveau sur le bouton Ajouter,
• Choisir enfin la référence b012 avec une quantité de 3 et cliquer sur Ajouter,

Comme vous le constatez, en même temps que les références s'inscrivent les unes en dessous des autres dans la colonne Code article, les éléments des autres colonnes sont récupérés depuis le catalogue. De fait, des formules se déclenchent pour calculer les montants hors taxes de chaque ligne, en fonction des quantités achetés.

Ce sont ces éléments que nous souhaitons archiver et protéger, dès lors que la facture est validée, pour en conserver la trace. D'ailleurs à ce stade, si vous cliquez sur le bouton Valider la facture, un code VBA Excel se déclenche pour mettre à jour les stocks du catalogue, dans la feuille articles, en fonction des quantités achetées.

Interface graphique VBA - UserForm
Si le vendeur valide la facture en cliquant sur le bouton du Userform, alors les données doivent être parcourues, récupérées et inscrites dans un fichier externe de type csv, pour être archivées. Cela signifie que nous devons adapter le code VBA associé au bouton Valider la facture.

• Cliquer sur la croix de l'interface graphique pour fermer le UserForm,
• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projets sur la gauche, double cliquer sur l'objet Facture,

Si l'explorateur de projets n'est pas visible dans votre environnement, vous devez premièrement cliquer sur le menu Affichage en haut de l'éditeur de code. Puis dans la liste, vous devez cliquer sur Explorateur de projets. Quoiqu'il en soit, nous affichons le Userform de facturation en mode conception. Des codes sont associés à des procédures événementielles, directement attachées aux contrôles comme les boutons de l'interface.

• Double cliquer sur le bouton Valider la facture du Userform,

Nous basculons ainsi entre les bornes de la procédure valider_Click, soit le code VBA Excel qui se déclenche au clic du vendeur sur le bouton Valider la facture. Ce code récolte une réponse fournie par l'utilisateur au clic sur un bouton (Oui ou Non) proposé par une boîte de dialogue MsgBox :

reponse = MsgBox("Souhaitez-vous valider la facture et mettre à jour les stocks", vbYesNo + vbQuestion)

Si le vendeur clique sur le bouton Oui (If (reponse = 6) Then), alors des instructions sont mises en oeuvre afin de mettre à jour les stocks du catalogue, selon les quantités d'articles achetés. C'est donc, entre les bornes de cette instruction VBA conditionnelle, que nous devons ajouter le code permettant l'archivage de la facture.

Accès séquentiels aux fichiers externes
Pour archiver les données de facturation, nous choisissons de coder les instructions dans une procédure indépendante. Même si ce n'est pas très conventionnel, nous allons d'abord réaliser l'appel à cette procédure, que nous créerons ensuite.

• Dans la procédure valider_Click, juste avant le End If, ajouter l'instruction suivante :

archiver

Il s'agit du nom que nous allons attribuer à la procédure d'archivage des données de facturation. Bien sûr, comme elle n'existe pas encore, si nous exécutons le code à ce stade, une erreur est générée.

• Cliquer sur le menu Insertion en haut de l'éditeur de code,
• Dans la liste, choisir Module,

Un nouveau module, prêt à réceptionner des procédures de code indépendantes, apparaît dans l'arborescence de l'explorateur de projets.

• Dans sa feuille de code, au centre de l'écran, créer la procédure archiver, comme suit :

Sub archiver()

End Sub

Nous allons effectuer un test pour nous assurer que l'appel à cette procédure fonctionne bien. Pour cela, nous choisissons d'afficher un message de confirmation, sous forme de boîte dedialogue.

• Entre les bornes de la procédure, ajouter l'instruction suivante :

MsgBox "ok"

• Enregistrer les modifications (CTRL + S),
• Dans l'explorateur de projets, double cliquer sur l'élément ThisWorkbook,
• Cliquer entre les bornes de la procédure Workbook_Open et enfoncer la touche F5,

L'interface graphique se déclenche au-dessus de la feuille de facturation, comme si nous venions d'ouvrir le classeur. Les données précédemment inscrites, du fait des manipulations, doivent toujours être présentes.

• Cliquer sur le bouton Valider la facture,
• A la question, cliquer sur Oui pour entrer dans l'instruction If du code VBA,

La petite boîte de dialogue du MsgBox s'affiche alors, confirmant que l'appel à notre procédure archiver fonctionne parfaitement.

• Valider ce MsgBox et fermer l'interface en cliquant sur la croix de son onglet,
• Puis, revenir dans l'éditeur de code (ALT + F11),
• Double cliquer sur le Module1 depuis l'explorateur de projets,

Le code de cette procédure doit récupérer les données de la facture pour les exporter et les inscrire dans un fichier de type csv. Le format csv est interprété par n'importe quel logiciel, si bien que ces informations pourront être importées dans n'importe quelle base de données. Par esprit de structure, nous souhaitons que tous ces fichiers soient enregistrés dans un sous dossier archives, du classeur de facturation.

• Créer un sous dossier archives dans le répertoire où a été téléchargé le classeur Excel,
• De retour dans l'éditeur VBA Excel, ajouter une apostrophe (') devant la ligne du MsgBox,

L'instruction passe en vert, nous venons de la transformer en commentaire, soit un code qui ne se déclenche plus. Avant d'accéder au fichier en écriture pour y inscrire les données à archiver, nous avons besoin de deux variables. L'une doit permettre de mémoriser le chemin d'accès au fichier. La seconde doit permettre de mémoriser le nom du fichier lui-même.

• Au début de la procédure archiver, ajouter les deux déclarations de variables suivantes :

Dim nom_fichier As String: Dim chemin As String

Comme nous l'avons déjà appris, l'emploi des deux points (:) dans la syntaxe VBA, permet d'énumérer les déclarations de variables, sur une même ligne.

Chaque nom de fichier doit être unique. Pour créer un code unique d'identification, nous pouvons nous caler sur l'horloge à la seconde près, soit précisément au moment où la facture est validée. C'est la fonction VBA Now qui renvoie cette information.



Si nous inscrivons la fonction VBA Now dans un MsgBox, nous pouvons visualiser ce qu'elle retourne précisément. Et c'est ce qu'illustre la capture ci-dessus. Les chiffres des jours, mois et années sont séparés par des slashs (/) tandis que les chiffres des heures, minutes et secondes sont séparés par le symbole deux points (:). Or ces caractères ne sont pas autorisés dans les noms de fichiers. La fonction VBA Replace permet de remplacer une occurrence par une autre, dans une chaîne de caractères. Sa syntaxe est la suivante :

Replace(chaine_de_caracteres, "terme_a_remplacer", "terme_de_remplacement")

Dans la chaîne de caractères renvoyée par la fonction Now, nous souhaitons remplacer le slash, les deux points et l'espace par rien. Cela signifie que nous souhaitons afficher tous les chiffres côte à côte. Nous pourrions donc écrire la suite des instructions suivantes, afin de réaliser les remplacements tour à tour.

nom_fichier = Replace(Now, "/", "")
nom_fichier = Replace(nom_fichier, " ", "")
nom_fichier = Replace(nom_fichier,":", "") & ".csv"

Mais nous pouvons optimiser ce code en imbriquant les fonctions Replace les unes dans les autres. Les remplacements se font ainsi en cascade.

• Après la déclaration de variables, ajouter les deux lignes suivantes :

nom_fichier = Replace( Replace( Replace(Now, "/", ""), " ", ""), ":", "") & ".csv"
MsgBox nom_fichier

Nous affectons dans la variable nom_fichier, le résultat condensé du remplacement de caractères, dans la chaîne Now, grâce à l'imbrication des fonctions Replace. Nous concaténons ce résultat (&), à l'extension .csv que nous attribuons donc au fichier d'exportation.

• Enregistrer les modifications (CTRL + S) et exécuter le code de la procédure (F5),

Comme l'illustre la capture ci-dessus, nous obtenons un nom de fichier bien formé et unique désormais. Encore faut-il connaître son emplacement. Il s'agit du sous-dossier archives que nous avons créé dans le répertoire du classeur Excel. Et c'est la propriété Path de l'objet VBA Excel ThisWorkbook qui renvoie ce chemin.

• Cliquer sur le bouton Ok du MsgBox pour stopper l'exécution du code,
• Revenir dans l'éditeur de code (ALT + F11),
• Remplacer la ligne du MsgBox par les deux instructions suivantes :

chemin = ThisWorkbook.Path & "\archives\"
MsgBox chemin & nom_fichier

Dans la variable chemin, au répertoire de l'application (ThisWorkbook.Path), nous concaténons la fin de l'adresse pour accéder au sous dossier (& "\archives\"). Si bien que lorsque nous concaténons les deux variables, nous obtenons le chemin d'accès complet au fichier qui doit servir d'archive. Si nous testons le code, une boîte de dialogue affiche cette adresse reconstruite.

Pour inscrire les informations dans ce fichier qui sera créé à la volée, nous devons parcourir le tableau, de la cellule C5 à la cellule G35, soit du début de la facturation jusqu'à la fin. L'objet VBA Cells permet de désigner une cellule Excel par son indice de ligne et de colonne. Par exemple, Cells(1,2) désigne la cellule B1. Intégré dans une double boucle For Next, pour faire varier l'indice de ligne de 5 à 35 et celui de la colonne, de 3 à 7, nous serons en mesure de parcourir chaque cellule de la facture. Préalablement, nous avons besoin de déclarer ces variables.

• Sous les déclarations de la procédure archiver, ajouter les deux déclarations suivantes :

dim ligne as Byte: dim colonne as byte

Nous typons ces variables comme des entiers courts (Byte), dont la capacité d'accueil peut varier de 0 à 255. C'est plus qu'il n'en faut pour faire varier nos indices de lignes et de colonnes.

• A la place du MsgBox précédent, ajouter la construction de la double boucle, comme suit :

For ligne = 5 To 35
For colonne = 3 To 7
If (Cells(ligne, colonne).Value <> "") Then MsgBox Cells(ligne,colonne).Value
Next colonne
Next ligne

Comme nous l'avait appris la formation VBA Excel sur les boucles For Next, une variable est utilisée pour réaliser un traitement récurrent entre deux bornes (5 et 35 pour la première). Le mot clé Next suivi du nom de cette variable permet de borner la boucle. Comme les deux boucles sont imbriquées, nous parcourons ainsi l'ensemble des cellules de la facture. Nous faisons tout d'abord varier l'indice de ligne entre 5 et 35. Et pour chacune des lignes, nous faisons varier l'indice de colonne entre 3 et 7. En d'autres termes, pour chaque ligne située entre l'indice 5 et l'indice 35, nous passons en revue toutes les cellules situées entre la colonne 3 et la colonne 7. Dans un premier, un MsgBox permet d'effectuer un test. Il est intégré dans une instruction conditionnelle If, afin de n'afficher que le contenu des cellules non vides. L'absence du End If est rendue possible dans la syntaxe, lorsqu'une seule action est engagée et qu'elle est écrite sur la même ligne.

• Enregistrer les modifications et exécuter le code,
• Cliquer sur Ok jusqu'à obtenir la valeur de la dernière cellule de la facture, son montant TTC,

Le code est parfaitement fonctionnel. Désormais, nous devons reconstruire ces informations ligne à ligne, séparées par des points-virgules (;), pour matérialiser les colonnes. Chacune de ces lignes doit ensuite être inscrite dans le fichier csv pour l'archivage. Nous avons besoin d'une variable pour reconstruire chaque ligne du csv.

• Dans la partie déclarative, ajouter l'instruction suivante :

Dim chaine As String

Nous déclarons la variable chaine comme un texte (String) puisque nous souhaitons y stocker des chaînes de caractères. L'accès au fichier en écriture doit se faire avant l'initialisation des deux boucles. En effet, c'est à l'intérieur de ces dernières, ligne à ligne, que les informations doivent être écrites. Et pour qu'elles soient écrites, le fichier doit déjà être ouvert. La formation VBA Excel pour importer et exporter des données, nous avait appris les instructions VBA pour l'accès séquentiel aux fichiers.

• Avant la double boucle, ajouter l'instruction suivante :

Open chemin & nom_fichier For Output As #1

L'instruction VBA Open suivie du nom du fichier (chemin & nom_fichier) permet d'ouvrir ce dernier en mémoire. For Output permet d'indiquer que nous accédons à ce fichier en écriture et non en lecture. Et comme cette écriture se fait en mémoire avant la sauvegarde définitive sur le disque, nous devons désigner une adresse mémoire, par le biais d'un numéro (#1). Ce numéro d'allocation mémoire ne doit pas déjà être utilisé. Par de risque ici, puisque nous n'ouvrons aucun autre fichier en même temps. Tout fichier ouvert en mémoire doit être fermé à l'issue du traitement, afin de libérer les ressources précisément. Le traitement sera terminé une fois les deux boucles fermées.

• Après l'instruction Next ligne, ajouter la ligne de code suivante :

Close #1

Pour libérer les ressources, nous utilisons l'instruction Close suivi de l'adresse mémoire désignant le fichier à fermer. C'est à cette occasion que les traitements réalisés seront écrits en dur sur le disque. Sauf que nous n'avons pas encore réalisé ces opérations. Les lignes reconstituées doivent être écrites tour à tour. Cela signifie que pour chaque nouvelle ligne de la facture Excel, la variable chaine doit d'abord être réinitialisée.

• Entre les deux boucles, soit le For ligne et le For colonne, ajouter l'affectation suivante :

chaine = ""

• Puis, à l'intérieur de la boucle For colonne, ajouter l'instruction suivante :

chaine = chaine & ";" & Cells(ligne, colonne).Value

Pour chaque cellule de la ligne en cours, nous ajoutons la valeur contenue dans la case, à celle déjà reconstituée au cours des précédents traitements. Comme nous séparons chacune de ces valeurs d'un point-virgule (";" & Cells(ligne, colonne).Value), nous reconstruisons chaque ligne pour le format csv. Et chacune de ces lignes doit être inscrite dans le fichier csv, avant de passer à la suivante, soit avant la prochaine occurrence de la boucle For ligne.

• En conséquence, avant le Next ligne, ajouter l'instruction suivante :

Print #1,chaine

Print est l'instruction séquentielle qui permet d'écrire dans le fichier désigné par son adresse mémoire (#1), l'information qui lui est passée en paramètre (chaine).

• Enregistrer les modifications et exécuter le code VBA (F5),

En apparence rien ne se produit. Mais en réalité, les données ont bien été consolidées et archivées au format csv, dans le sous dossier archives du classeur de facturation.

• Afficher le sous dossier archives dans l'explorateur Windows,
• Ouvrir le fichier csv ainsi construit dans le bloc note (Clic droit / Ouvrir avec),

Comme l'illustre la capture ci-dessous, toutes les informations de la facture ont bien été écrites ligne à ligne et séparées par des points-virgules, pour chaque colonne.



D'ailleurs, si nous ouvrons directement ce fichier dans Excel (Fichier / Ouvrir), sans même devoir l'importer, nous constatons que les informations sont parfaitement restituées dans des cellules indépendantes.



Modifier les attributs des fichiers externes
Ces archives doivent être sécurisées pour ne pas risquer d'être endommagées. Par définition, une archive est faite pour être consultée et doit durer dans le temps. Pour protéger ces fichiers exportés, nous proposons de modifier certains de leurs attributs par le code VBA. Si le fichier est en lecture seule, il pourra être consulté sans être modifié. Si l'archive est cachée, les risques de modifications et de suppressions sont limités plus encore.

Comme nous l'avait appris la formation VBA Access pour archiver les données, nous avons besoin d'un objet de programmation particulier, pour manipuler les fichiers du disque et accéder à leurs attributs. C'est la fonction VBA CreateObject qui permet d'instancier une classe pour créer un objet ActiveX dont la nature dépend du paramètre qui lui est passé. Et si ce paramètre est scripting.filesystemobject, alors nous créons un objet capable de manipuler les propriétés des fichiers.

• Dans la partie déclarative de la procédure archiver, ajouter les deux déclarations suivantes :

Dim objet_fichier: Dim le_fichier

Ces deux variables ne sont pas typées car il s'agit d'objets instanciant une classe externe. La première variable permettra de déclarer l'objet pour manipuler les fichiers sur le disque. La seconde permettra à partir de la première, de pointer précisément sur un fichier en fonction de son chemin d'accès.

• A la fin de la procédure, avant le End Sub et après le Close #1, ajouter les deux lignes suivantes :

Set objet_fichier = CreateObject("scripting.filesystemobject")
Set le_fichier = objet_fichier.getfile(chemin & nom_fichier)

La méthode GetFile de l'objet ainsi créé, héritant des propriétés et méthodes de la classe instanciée, permet d'affecter un objet pour piloter précisément le fichier qui lui est passé en paramètre.



Comme l'enseigne l'aide en ligne et comme l'illustre la capture ci-dessus, l'objet ainsi créé propose une propriété Attributes qui peut être affectée à différentes valeurs. A la lecture du tableau, la valeur 1 permet de transformer le fichier en lecture seule, tandis que la valeur 2 le masque. Ces valeurs de propriétés se combinent en s'additionnant. Ainsi, avec la valeur 3, nous obtenons un fichier caché en lecture seule.

• Avant le End Sub, ajouter l'affectation suivante :

le_fichier.Attributes = 3

Pour tester le résultat, nous proposons de réaliser une simulation complète.

• Enregistrer les modifications et basculer sur la feuille de facturation,
• Supprimer les données des colonnes Code article et Quantité seulement,
• Fermer le classeur Excel en l'enregistrant de nouveau,
• Puis, le rouvrir dans la foulée,
• A l'aide du Userform qui apparaît, ajouter quelques articles avec des quantités différentes,
• A l'issue, cliquer sur le bouton Valider la facture et confirmer le message du MsgBox,

Si nous affichons le contenu du dossier archives, notre fichier csv est bien présent. Mais pour le voir, il faut cocher la case Eléments masqués du ruban Affichage dans l'explorateur Windows. De plus, si nous affichons la colonne Attributs par clic droit sur les entêtes de l'explorateur, nous notons la présence de l'indication RH en regard du fichier ainsi créé. Il s'agit de la première lettre des deux mots américains, R pour ReadOnly (Lecture seule) et H pour Hidden (Caché). Et si nous tentons de modifier le fichier, nous n'y sommes pas autorisés.



Le code VBA complet de la procédure archiver est le suivant :

Sub archiver()
Dim nom_fichier As String: Dim chemin As String
Dim ligne As Byte: Dim colonne As Byte
Dim chaine As String
Dim objet_fichier: Dim le_fichier

nom_fichier = Replace(Replace(Replace(Now, "/", ""), "", ""), ":", "") & ".csv"
chemin = ThisWorkbook.Path & "\archives\"

Open chemin & nom_fichier For Output As #1
For ligne = 5 To 35
chaine = ""
For colonne = 3 To 7
chaine = chaine & ";" & Cells(ligne, colonne).Value
Next colonne
Print #1, chaine
Next ligne
Close #1

Set objet_fichier = CreateObject("scripting.filesystemobject")
Set le_fichier = objet_fichier.getfile(chemin & nom_fichier)
le_fichier.Attributes = 3
End Sub