Accéder à un classeur Excel protégé par un mot de passe

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


Sujets que vous pourriez aussi aimer :


Accéder à un classeur protégé

Grâce aux deux astuces précédentes, nous avons appris à accéder à un classeur externe fermé et à y réaliser des recherches pour produire des extractions dans le classeur appelant. Mais lorsque ce classeur externe est protégé par un mot de passe, la problématique se corse.

Classeurs Excel à télécharger
Nous allons tout de suite le constater en récupérant les travaux précédents.

• Télécharger le fichier compressé extraire-classeur-excel-protege.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

La décompression livre naturellement deux classeurs. La base de données externe se nomme bdd.xlsx. Elle est protégée par un mot de passe. Vous pouvez le constater en tentant de l'ouvrir dans Excel. Ce mot de passe est : abc123. L'autre classeur, nommé acceder-classeur-protege.xlsm, est celui qui doit extraire les informations de cette base de données externe et protégée.

• Double cliquer sur le fichier acceder-classeur-protege.xlsm pour l'ouvrir dans Excel,

Il héberge le code VBA du volet précédent pour réaliser la recherche de la référence choisie avec la liste déroulante en cellule E5. Cette recherche est destinée à produire les extractions des informations attachées sur la désignation, le prix, le poids et le stock dans les cellules du dessous. Ce code VBA est tout à fait fonctionnel. Mais entre-temps, la base de données externe a été protégée par un mot de passe, ce qui modifie considérablement l'approche. Nous proposons de le constater.

• Avec la liste déroulante en E5, choisir une nouvelle référence,

Malgré le code VBA existant, autrefois fonctionnel, la requête échoue, comme vous pouvez le constater. Pour importer la désignation, une barrière se dresse. Le mot de passe doit être renseigné, ce qui est fort logique. Et il doit être répété à trois reprises pour les trois autres extractions concordantes à fournir. Vous en conviendrez, cette solution n'est pas viable. Il est urgent de trouver une parade.

Les variables de l'instance
Cette parade consiste à piloter une instance d'Excel pointant sur cette base de données externe pour l'ouvrir en lui spécifiant le mot de passe d'accès. Nous avons besoin d'objets pour piloter l'application Excel, le classeur externe et sa feuille qui héberge les données à questionner.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,

Nous y découvrons la procédure Worksheet_Change. C'est elle qui se déclenche automatiquement au changement de valeur dans la liste déroulante (If Target.Address = "$E$5" Then). Elle engage plusieurs recherches verticales (vlookup) de la référence dans la base de données externe pour rapatrier les informations associées. Mais en l'absence de chaîne de connexion présentant officiellement le mot de passe en vigueur, l'accès lui est barré.

• Dans la partie déclarative, ajouter les déclarations de variables suivantes :

...
Dim fenetre As Excel.Application: Dim classeur As Workbook
Dim feuille As Excel.Worksheet
Dim position As Integer: Dim chemin As String
...

L'objet fenetre est destiné à piloter une instance d'Excel. Dans cette instance, l'objet classeur doit plus précisément piloter le classeur externe de la base de données protégée. Dans ce classeur, l'objet feuille doit pointer sur la feuille hébergeant cette base de données. Enfin, nous stockerons la position de la référence trouvée dans la base de données, dans la variable position. Grâce à elle, nous saurons extraire les éléments demandés depuis leurs colonnes respectives.

Initialiser les variables
Comme nos objets existent, nous devons maintenant les initialiser pour pointer sur la base de données externe et protégée.

• Dans l'instruction conditionnelle, supprimer toutes les précédentes lignes de code,
• Puis, ajouter les nouvelles lignes VBA suivantes :

...
If Target.Address = "$E$5" Then
chemin = ThisWorkbook.Path & "\bdd.xlsx"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin, , True, , "abc123")
Set feuille = classeur.Worksheets("Catalogue")
fenetre.Visible = False
End If
...

Tout d'abord, nous mémorisons le chemin d'accès à la base de données externe dans la variable chemin. C'est en effet la propriété Path de l'objet ThisWorkbook qui renseigne sur le chemin d'accès du classeur actif. Tous deux sont en effet placés dans le même dossier. Ensuite, grâce à la fonction VBA CreateObject, nous instancions la classe d'Excel (Excel.Application). C'est ainsi que notre objet fenetre hérite des propriétés et méthodes pour piloter une nouvelle instance d'Excel. Pour preuve dans l'enchaînement, nous exploitons la méthode Open de sa collection Workbooks pour initialiser l'objet classeur. Nous l'initialisons sur la base de données externe (chemin), que nous ouvrons en lecture seule (True) avec le mot de passe d'accès en cinquième paramètre. Dès lors, nous n'avons plus qu'à exploiter la collection héritée Worksheets pour pointer sur la feuille Catalogue du classeur externe. C'est grâce à cet objet feuille que nous allons désormais pouvoir piloter des cellules précises de cette feuille, pour rapatrier les données souhaitées. Enfin, nous réglons la propriété Visible de l'objet fenetre parent à False. C'est ainsi que le traitement se produira en tâche de fond, sans ouvrir de fenêtre Excel.

Position de la référence
Puisque nous sommes maintenant en mesure de piloter les cellules du classeur externe, nous devons commencer par trouver la position de la référence cherchée, dans la base de données. Cette recherche doit être faite en première colonne. Chaque colonne est nommée en fonction de l'intitulé de son entête. Pour la colonne des références, il s'agit de l'intitulé produit_ref. Vous pouvez le constater en ouvrant manuellement ce classeur externe et en déployant la zone Nom en haut à gauche de la feuille. Pour exercer cette recherche, nous suggérons d'exploiter la fonction Equiv. En anglais, elle se nomme Match.

• A la suite dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
position = classeur.Application.Match([E5].Value, feuille.[produit_ref], 0) + 3
...

Sur le classeur externe (objet classeur), grâce à la méthode Match de l'objet Application, nous engageons la recherche de la référence choisie dans le classeur actif, grâce à la liste déroulante ([E5].Value). Sur la feuille du classeur externe (objet feuille), nous dirigeons cette recherche sur la première colonne de la base de données ([produit_ref]). Nous réalisons une recherche exacte (0). Comme la fonction Equiv (Match) raisonne relativement à la plage de cellules qui lui est passée, nous réajustons son calcul de trois unités (+3). Il existe en effet trois lignes au-dessus de la base de données. C'est ainsi et en valeur absolue que nous enregistrons la position de la référence trouvée dans la variable position.

Les données de la référence trouvée
Comme l'objet feuille permet de manipuler les cellules de la base de données externe et comme nous connaissons désormais la ligne de la référence cherchée, nous pouvons maintenant entreprendre la récupération des informations associées. Il s'agit de la désignation, du prix, du poids et du stock. Ces données sont à importer en cellules C7, C9, E9 et G9 de la feuille active. Dans la base de données externe, elles se trouvent respectivement en colonnes 3, 4, 5 et 7.

• A la suite du code de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
[C7].Value = feuille.Cells(position, 3).Value 'Désignation
[C9].Value = feuille.Cells(position, 4).Value 'Prix
[E9].Value = feuille.Cells(position, 5).Value 'Poids
[G9].Value = feuille.Cells(position, 7).Value 'Stock
...

Dans chaque cellule désignée (C7, C9, E9, G9), sur la feuille externe et grâce à l'objet Cells, nous pointons sur la ligne de la référence trouvée (position) pour importer les données depuis leurs colonnes respectives (3, 4, 5, 7).

Fermer les objets de programmation
Pour finir en beauté et comme nous en avons l'habitude, nous devons décharger les objets de programmation de la mémoire de l'ordinateur. C'est ainsi que nous allons libérer les ressources.

• A la fin de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
classeur.Close False
fenetre.Quit
Set classeur = Nothing
Set fenetre = Nothing
Set feuille = Nothing
...

Nous exploitons respectivement les méthodes Close et Quit pour fermer le classeur dans l'instance et l'instance elle-même. Le paramètre false permet de ne pas sauvegarder les modifications à la fermeture du classeur. Dans notre cas, il n'est pas utile car nous avons accédé à ce classeur en lecture seule. Cependant, la méthode est intéressante à connaître pour d'autres contextes. Enfin, nous réinitialisons (Set) chaque objet à Nothing pour les libérer de la mémoire de l'ordinateur.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Puis, choisir une nouvelle référence avec la liste déroulante en cellule E5,

Comme vous pouvez l'apprécier et malgré la protection en vigueur sur le classeur externe, nous récupérons toutes les informations attachées à la référence choisie. Aucune boîte de dialogue ne se dresse pour empêcher l'accès. Cette méthode est donc particulièrement précieuse pour savoir questionner des classeurs Excel verrouillés.