Pierre Fauconnier

Salut.

Dans cette discussion, la question est de savoir lister les feuilles d'un classeur sans l'ouvrir^(*). L'idée est de choisir un classeur dans une liste et d'en récupérer la liste des feuilles pour un traitement ultérieur.

Sans ouvrir le classeur, une piste est donnée en utilisant VBA et la bibliothèque ADODB. De fait, cette bibliothèque dispose d'outils pour considérer et gérer un classeur Excel "comme une base de données" en considérant que chaque feuille est une table. On peut alors retrouver le catalogue de cette "base de données" pour en extraire les noms des tables.

C'est chouette, bien sûr, mais:

• c'est du VBA (=> extension xlsm et éventuelles limitations);
• il faut connaître le code (ou le copier du net);
• il faut connaître la chaine de connexion à un classeur Excel;
• ...

Bref, il faut savoir pro-gra-mmer! Car là, pas question, comme je le mets en signature, de penser Excel avant de penser VBA. ADODB n'en a cure, et il faut vraiment mettre les mains dans le cambouis... Mais ça fonctionne, et ça fonctionne plutôt bien!

Perso, j'ai donné une autre réponse, un peu synthétique en ce qui concerne sa mise en oeuvre, et je vais détailler tout cela dans ce billet, en m'intéressant à la démarche. En fin de billet, vous trouverez le classeur d'exemple.

Récupérer le contenu d'un classeur

Si on est curieux, ce que je vous engage à être, on peut tenter des trucs sympas avec Power Query, puisque la saisie semi-automatique nous aide. Ainsi, si dans la barre de formule ou de commande, on saisit =Excel, on voit apparaître de jolies choses, et on peut aller un peu plus loin en saisissant =Excel.Workbook... On voit alors que Power Query attend qu'on lui donne le contenu d'un fichier "en binaire" (Au passage, on remarque que Excel.Workbook peut prendre 3 arguments)







Contenu d'un fichier? Fichier, en anglais, ça se dit File, non? Essayons...






Yeah! Trop fort! On vient de découvrir, par curiosité bien placée, comment récupérer la structure d'un fichier Excel...





Récupérer la liste des feuilles

En examinant cette table et plus particulièrement la colonne Kind (Type en français), on s'aperçoit que le classeur contient des feuilles (sheet), des tableaux structurés ou tables (table) et des plages nommées (DefinedName). En filtrant sur Sheet et en ne gardant que la colonne Name, on récupère les noms des feuilles qu'on renvoie dans Excel... Filtrer, vous savez faire, c'est comme dans Excel. Pour supprimer toutes les colonnes sauf la première, le clic droit est votre ami => clic droit sur la colonne à garder puis "supprimer les autres colonnes"...




Rendre le nom du classeur paramétrable depuis Excel

Dans cette requête, on a saisi le nom du fichier en dur, comme l'indique l'étape Source de notre requête



Et si on souhaitait récupérer le nom du classeur au départ d'Excel, par exemple d'une cellule nommée?

Solution 1: La sous-requête

J'explique dans ce billet comment gérer une plage nommée dans Power Query. On se place dans la cellule nommée puis on incorpore la donnée dans Power Query. L'étape qui nous intéresse est la première (les deux autres sont inutiles ici) et on exécute un "drill down" pour extraire la valeur. La requête renvoie alors le texte contenu dans la cellule.

Code powerQuery :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
let
    Source = Excel.CurrentWorkbook(){[Name="NomFichier"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

Pour incorporer la sous-requête, on va d'abord rassembler les deux étapes en une seule: = Excel.CurrentWorkbook(){[Name="NomFichier"]}[Content]{0}[Column1]. On va alors remplacer le nom du fichier en dur dans l'étape source de notre première requête pour variabiliser le fichier au départ d'Excel:




Solution 2: La fonction Power Query personnalisée

Si on a besoin de récupérer ce nom de classeur dans différentes requêtes, il pourrait être utile de créer une fonction perso en Power Query (voir ce billet). Dans une requête vide, on passe sur l'éditeur avancé et on crée notre fonction. Ici, on ne paramétrise pas la fonction et on la nomme NomFichier:

Code PowerQuery :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
let
    Source = () as text =>
    Excel.CurrentWorkbook(){[Name="NomFichier"]}[Content]{0}[Column1]
in
    Source

On peut alors remplacer le nom du fichier en dur ou la sous-requête par l'appel de la fonction dans l'étape Source de notre requête principale =>


Que ce soit par sous-requête ou par fonction, il suffira de modifier le nom du fichier dans la cellule nommée puis d'actualiser la requête dans Excel pour récupérer les feuilles du nouveau classeur choisi.


Rappel: Pour n'avoir qu'une fonction qui renvoie la valeur de la cellule de la première colonne de la première ligne d'une plage nommée, on pourrait typer la fonction as any et la doter d'un argument.

Code PowerQuery :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
let
    Source = (Nom as text) as any =>
    Excel.CurrentWorkbook(){[Name=Nom]}[Content]{0}[Column1]
in
    Source

On remarque alors que la fonction demande un argument, et on pourra le passer lors de l'étape de récupération du fichier.







Récupérer le nom du classeur dans une liste

Dans la question du forum, il était question de récupérer le nom du classeur dans une liste. On peut imagier plusieurs solutions:

1. ajouter une colonne de choix que l'on marque d'un x pour le classeur à traiter et adapter la fonction/sous-requête pour tenir compte de cette modification côté Excel;
2. parcourir la liste des noms puis boucler sur celle-ci en VBA pour pousser le nom du classeur dans la plage nommée et actualiser la requête en VBA;
3. ...

J'ai exposé le code de la deuxième solution dans la discussion citée au début de mon billet, pour illustrer que VBA et Power Query peuvent faire bon ménage.


Classeur d'exemple

PQ-Feuilles-1.xlsm

Conclusions

Avec un peu de pratique, Power Query permet de mettre rapidement en place des solutions intéressantes qui évitent le VBA. Ce n'est pas que je n'aime pas le VBA, qui est un langage que j'apprécie beaucoup et que je trouve très pro, mais vous, l'utilisateur Excel, n'êtes pas forcément un "programmeur". Power Query peut avantageusement remplacer VBA pour une part importante du traitement de vos données.

Et vous, vous pratiquez Power Query?



^(*) Je précise toutefois qu'il n'est pas possible de récupérer le contenu d'un classeur sans l'ouvrir. Si vous avez déjà essayé de récupérer le contenu d'un pot de confiture sans l'ouvrir, vous avez fait l'expérience que c'était rigoureusement impossible. "Sans ouvrir" signifie ici "sans l'ouvrir dans l'interface Excel".

Rappel important: POWER QUERY est sensible à la casse!! (différenciation majuscules - minuscules)





.