Récupérer liste des onglets de classeurs sans les ouvrir

**Pierre Fauconnier** · 27/09/2021, 16h12

Envoyé par Kestion100

[...]Comment procèdes-tu dans ce cas, tu passes par une feuille Excel pour la liste des fichiers et tu fais une requête après?

Voici l'illustration de mon classeur tel qu'il est conçu. J'ai tout mis sur une feuille pour que ce soit plus clair, mais on peut dispatcher sur plusieurs feuilles, puisque l'on travaille avec des tableaux structurés et des plages nommées... Le tableau bleu est nommé t_Fichiers et le vert (résultat de la requête) est nommé pq_Feuilles

Nom : 2021-09-27_160551.png
Affichages : 178
Taille : 68,1 Ko

Nom : 2021-09-27_160551.png
Affichages : 178
Taille : 68,1 Ko

Voici la requête Power query qui récupère les noms des feuilles du classeur dont le nom figure dans la plage nommée Fichier...

Code PowerQuery :

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

1
2
3
4
5
6
let
    Fichier = Excel.CurrentWorkbook(){[Name="Fichier"]}[Content]{0}[Column1],
    Source = Table.SelectRows(Excel.Workbook(File.Contents(Fichier)), each [Kind] = "Sheet"),
    Feuilles = Table.SelectColumns(Source, {"Name"})
in
    Feuilles

Voici le code VBA qui parcourt le tableau des fichiers, qui actualise la requête et qui affiche dans un msgbox la concaténation des noms des feuilles trouvés, à chaque boucle (C'est évidemment pour l'exemple, et j'ai utilisé TEXTJOIN <=> JOINDRE.TEXTE, une exclusivité 365)...

Code :

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

1
2
3
4
5
6
7
8
9
Sub Test()
  Dim r As Range
 
  For Each r In Range("t_Fichiers[Classeur]")
    Range("Fichier").Value = r.Value
    Range("pq_Feuilles").ListObject.QueryTable.Refresh False
    MsgBox Application.Evaluate("textjoin("" - "",true,pq_Feuilles)")
  Next r
End Sub

A adapter en fonction de tes besoins et de ta version, bien sûr. C'est l'idée que j'illustre ici

**Kestion100** · 28/09/2021, 15h30

Ca fonctionne très bien sauf pour le code VBA:

Code :

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

Range("Fichier").Value = r.Value

Erreur 1004
La méthode Range de l'objet _Global a échoué

**Pierre Fauconnier** · 28/09/2021, 15h49

Donne tout le code...

A vue de nez, je dirais que la plage Fichier n'existe pas.

**Kestion100** · 28/09/2021, 15h53

J'ai repris ton code:

Code :

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

1
2
3
4
5
6
7
8
9
Sub Test()
  Dim r As Range
 
  For Each r In Range("t_Fichiers[Classeur]")
    Range("Fichier").Value = r.Value
    Range("pq_Feuilles").ListObject.QueryTable.Refresh False
    MsgBox Application.Evaluate("textjoin("" - "",true,pq_Feuilles)")
  Next r
End Sub

**Pierre Fauconnier** · 28/09/2021, 15h55

Via le gestionnaire de noms, vérifie qu'il y a bien un nom Fichier qui pointe vers une cellule de portée de classeur

**Kestion100** · 28/09/2021, 16h03

En l’occurrence c'est quelque chose que je n'ai pas trop saisi dans ton code, tu pars de deux plages nommées:

t_Fichiers et pq_Feuilles
Et je ne trouvais pas Fichier

La plage résultant du code:

Code :

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

1
2
3
4
5
6
let
    Fichier = Table.SelectRows(Excel.CurrentWorkbook(){[Name="t_Fichiers"]}[Content], each ([Choix] = "x")){0}[Classeur],
    Source = Table.SelectRows(Excel.Workbook(File.Contents(Fichier)), each [Kind] = "Sheet"),
    Feuilles = Table.SelectColumns(Source, {"Name"})
in
    Feuilles

posté antérieurement
est nommée:
t_Fichiers2

**Kestion100** · 28/09/2021, 16h08

Le tableau de destination se situe dans une autre feuille?? ceci expliquant cela???

**Pierre Fauconnier** · 28/09/2021, 16h09

J'explique la mise en oeuvre un peu plus en détails dans ce billet de blog.

t_Fichiers est un tableau structuré qui contient les fichiers qu'il faut inspecter.
fichier est une cellule nommée de classeur qui contiendra le nom du classeur dont il faut récupérer les feuilles.
pq_Feuilles représente deux choses: le nom de la requête dans Power Query et le nom du tableau structuré qui est renvoyé dans Excel.

Tu dois donc adapter la requête Power Query et le code VBA selon les noms que TU as donné à ces trois éléments.

Est-ce plus clair?

**Kestion100** · 28/09/2021, 16h17

Plus clair oui.
Concernant une colonne de cellules contenant des chemins de fichiers, je peux toutes les nommer une à une? (question un peu profane, je l'avoue)

**Pierre Fauconnier** · 28/09/2021, 16h18

Envoyé par Kestion100

Le tableau de destination se situe dans une autre feuille?? ceci expliquant cela???

Non, justement. Le fait de travailler avec des éléments nommés (tableau structuré et plage nommée) permet de les placer où tu veux dans le classeur et même de les déplacer à l'envi, puisqu'aussi bien dans le code VBA que dans la requête Power Query, on n'utilise pas les adresses des éléments mais leurs alias (les noms qu'on leur a donnés)...

Lorsque ton code s'arrête sur une ligne, tu peux regarder les variables via la fenêtre "variables locales" et tester certaines valeurs dans la fenêtre d'exécution (CTRL+G). Par exemple, tu pourrais saisir ? range("fichier").address dans la fenêtre d'exécution pour voir si c'est cette plage qui pose problème.

Voici mon fichier d'exemple...

**Pierre Fauconnier** · 28/09/2021, 16h27

Envoyé par Kestion100

Plus clair oui.
Concernant une colonne de cellules contenant des chemins de fichiers, je peux toutes les nommer une à une? (question un peu profane, je l'avoue)

Non, ce n'est pas intéressant. C'est là toute la puissance des tableaux structurés en VBA. Tu utilises Range("t_Fichiers[Fichier]") pour pointer sur la colonne Fichier sur laquelle tu boucles. Ainsi, si demain tu ajoutes des fichiers au tableau, la macro les prendra en compte automatiquement. Tu retrouves donc en VBA les avantages de la plage "dynamique" issue du fait que tu utilises les tableaux structurés (faut vraiment que je finisse mon tuto sur ce truc... ^^)

Connais-tu suffisamment les tableaux structurés? Tu peux rafraîchir tes connaissances en lisant mon tuto sur les tableaux structurés en Excel, à défaut d'avoir celui sur les tableaux structurés en VBA.

**Kestion100** · 29/09/2021, 08h13

Oui, en effet, l'intéressant est de nommer la cellule du fichier rechercher.

Le code PowerQuery a fonctionné parfaitement 1 fois, le code VBA lui n'a jamais fonctionné.

J'en suis maintenant à une erreur dans PowerQuery qui ne s'était pas présentée la première fois, même quand je repars de 0:
Expression.Error : Désolé... Nous n'avons pas trouvé le champ « Kind » de l'enregistrement.
Détails :
Name=Définitions
Data=[Table]

**Pierre Fauconnier** · 29/09/2021, 08h24

Pourrais-tu poster le classeur qui contient la solution Power Query et le code VBA? Ce sera plus simple pour voir exactement ce que tu as mis en place.

**Kestion100** · 29/09/2021, 08h39

ONGFICH.xlsm ONGFICH.xlsm

Évidemment, les adresses des fichiers sont fictives

**Kestion100** · 29/09/2021, 08h47

En appliquant ton code, sans rien toucher sauf les noms des fichiers et chemins, j'ai une première MsgBox qui s'affiche puis une erreur sur la ligne:

Code :

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

Range("pq_Feuilles").ListObject.QueryTable.Refresh False

Erreur d'exécution 1004
Expression.error Désolé...Nous n'avons pas trouvé le champ "Kind" de l'enregistrement

La première fois que j'avais fait l'opération, 0 pb.....

**Pierre Fauconnier** · 29/09/2021, 08h59

Pour la conception, je n'ai peut-être pas assez détaillé la manoeuvre, considérant qu'il s'agissait d'un prérequis, mais je constate ceci:

la plage nommée Fichier fait partie de ton tableau t_Fichiers alors qu'elle devrait être la cellule B1 de la feuille de paramètres;
ta solution Power Query joue avec la croix dans la seconde colonne du tableau et pas avec la cellule nommée Fichier;
Power Query ne renvoie pas de données dans Excel. La requête Power Query est nommée t_Fichiers (comme le tableau des noms de fichiers). Il conviendrait de la renommer;
il n'y a pas de code vba dans ton classeur, mais si tu as utilisé un code qui boucle sur les fichiers pour placer dans Fichier le nom à traiter dans Power Query, tu auras forcément un souci puisque Power Query ne "joue" pas avec cette plage nommée.

Tu dois choisir la technique que tu préfères:

mettre une croix dans le tableau (manuellement ou par VBA) => ta requête Power Query est alors correcte et le code VBA doit refléter ce choix;
placer le nom du fichier dans la cellule nommée Fichier => ta requête Power Query doit alors être adaptée pour refléter ce choix et récupérer le nom du fichier de la cellule nommée, et le code VBA doit lui aussi refléter ce choix.

Ici, en plus du fait qu'on n'a pas le code VBA, il y a un mix des deux solutions, et cela ne peut forcément pas fonctionner.

Dans quelle direction veux-tu aller?

**Kestion100** · 29/09/2021, 09h13

Oubli:
ONGFICH.xlsm

Dans mon cas, je pense qu'inscrire les chemin et nom du fichier concerné dans une cellule (validation de données liste me semble intéressant) serait le plus pratique

OK, tes codes offraient 2 possibilités que j'ai mélangées quelque peu, j'y vois plus clair

**Pierre Fauconnier** · 29/09/2021, 09h33

Comme dit précédemment, tu dois choisir UNE des techniques, et la mise en place doit refléter cette technique. Dans ton dernier fichier, ton code VBA utilise la plage nommée Fichier (qui, comme déjà dit, est placée au mauvais endroit), alors que ta requête Power Query utilise le fichier renseigné par une croix dans le tableau t_Fichiers... Ca ne saurait pas fonctionner. De plus, ton tableau structuré t_Fichiers contient des lignes vides. Comme je le précise dans le tuto dont j'ai donné le lien précédemment, il n'est pas nécessaire de prévoir des lignes dans un tableau structuré, et c'est même contre-productif.

Maintenant, tu parles d'une liste déroulante pour choisir le fichier. Perso, je trouve que c'est plus facile de mettre une croix en regard du fichier choisi que de le choisir dans une liste de validation qui n'est pas intuitive (elle ne complète pas la saisie) et qui ne permet pas le zoom... Soit.

On constate donc ici que c'est d'abord un problème de choix de conception (que veux-tu réaliser exactement? Avec quelles données au départ? avec quelle ergonomie? Pour quel résultat?) que de technique pure. Les techniques mises en oeuvre découleront des choix de conception. On peut t'aider tant qu'on veut, tant que tu n'auras pas arrêté UNE conception (savoir où tu vas), il ne servira à rien de te parler technique (te montrer la carte et te dire comment hisser les voiles)...

A toi donc de préciser d'abord les tenants et aboutissants de ton classeur., me semble-t-il. Car côté technique, tout à déjà été dit ici et sur mon blog

**Kestion100** · 29/09/2021, 09h59

Copie de PQ-Feuilles-1-3.xlsm
Avec ce fichier pour lequel je n'ai fait que remplacer les chemins et noms de fichiers mais qui pour le reste est entièrement celui de ton poste, j'ai toujours l'erreur:

Code :

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

Range("pq_Feuilles").ListObject.QueryTable.Refresh False

Erreur d'exécution 1004
Expression.error Désolé...Nous n'avons pas trouvé le champ "Kind" de l'enregistrement

Alors que je récupère bien les données attendues dans le tableau de destination

**Kestion100** · 29/09/2021, 10h24

Dans la requête:

Code :

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

1
2
3
4
5
 
Expression.Error : Désolé... Nous n'avons pas trouvé le champ « Kind » de l'enregistrement.
Détails :
    Name=XXX
    Data=[Table]

XXXX est bien le nom de l'un des ongles recherché

Récupérer liste des onglets de classeurs sans les ouvrir

Macros et VBA Excel

Discussions similaires

Partager

Partager