Appeler un tableau issu de Power Query dans VBA

Bonjour à tous,

Je continue à faire joujou avec Power Query, mais là j'ai besoin d'utiliser un peu de VBA.

Tout est dans le titre : je cherche à appeler un tableau issu de Power Query dans VBA pour en extraire que certains éléments et le mettre en forme à souhait. J'ai essayé avec ListObject, comme il s'agit en quelque sorte d'un tableau structuré, mais je ne veux pas le charger dans une feuille de mon classeur (je voudrais juste une connexion), donc de manière logique, ça me renvoie une erreur.

Une idée ?

Merci d'avance

EDIT : je rajoute en PJ un fichier modèle pour que ça soit plus clair.

Dans la Feuille 1, j'ai le tableau qui a été travaillé avec PowerQuery. Dans l'idéal, ce tableau doit juste être en mode connexion, i.e. non apparent dans une des feuilles du classeur.

En Feuille 2, j'ai une cellule (en rouge) où je viens sélectionner le prestataire. Exemple ici pour le prestataire 1.

Si le contrat est de type A, je veux récupérer dans le premier tableau la liste sans doublons des programmes (en colonne C) qui font appel à ce prestataire, ainsi que les noms des chef de programme et sponsor concernés (en colonne E).

Si le contrat est de type B, je veux récupérer dans le second tableau la liste sans doublons des programmes (en colonne G) et projets (en colonne H) qui font appel à ce prestataire, ainsi que le nom du chef de projet concerné (en colonne J).

Je voudrais alimenter et mettre en forme ces 2 tableaux via VBA (sauf si bien sûr il existe une solution avec Power Query). J'ai donc besoin d'appeler, comme dit plus haut, le tableau source issu de PowerQuery dans mon code VBA.

bonjour

je ne vois pas trop l'intérêt de PQuery (que j'adore pourtant)

un TCD suffirait-il ?

amitiés

Bonjour à tous

On peut filtrer la requête sur le prestataire et avoir

  • 1 tableau proche de ton 1er tableau : il faudra jouer sur MFC pour masquer la répétition du programme et alterner les couleurs mais on peut aussi n'utiliser qu'une ligne avec un lf pour séparer chef de programme et sponsor placés ensemble
  • 1 autre équivalent au second

Nul besoin de VBA

Merci pour vos réponses.

@jmd : je ne souhaite, si possible, pas utiliser de TCD.

@78chris : je me doutais bien que l'on pouvait aller au bout avec Power Query, sans passer par du VBA. Peux-tu me donner plus de précision sur la possibilité d'appliquer un filtre à une requête à partir d'une cellule paramètre ?

EDIT :

Je vois néanmoins un problème au fait de charger directement mon tableau Power Query dans la feuille que je veux : c'est qu'à chaque fois que je vais actualiser la requête, la mise en forme de mon tableau va bouger. Et je devrais donc passer par du VBA pour la figer...

RE

Dans Excel tu nommes ta cellule Choix par exemple puis en début de requête via l'éditeur tu mets entre les lignes let et Source

Choix= Table.FirstValue(Excel.CurrentWorkbook(){[Name="Choix"]}[Content]),

Tu peux ensuite utiliser la variable Choix pour filtrer par exemple : [Prestataire] = Choix

Dans Excel tu nommes ta cellule Choix par exemple puis en début de requête via l'éditeur tu mets entre les lignes let et Source

Choix= Table.FirstValue(Excel.CurrentWorkbook(){[Name="Choix"]}[Content]),

J'ai réussi à faire ça, mais je bloque pour la suite...

Et pour le problème lié à la mise en forme du tableau qui saute à chaque actualisation (cf. EDIT précédent post), qu'en penses-tu ?

re

les TCD sont une fonction des plus puissantes des tableurs

pourquoi s'en priver ?

RE

Je ne comprends pas de quelle mise en forme tu parles c'est de la source (supposée dans PQ et non sur un onglet si j'ai bien compris) ou celle des tableaux bleus.

J'ai reproduit tes tableaux et je ne constate pas de problème.

Comment as-tu fais la mise en forme ?

La requête initiale traite quelles données ?

Re

@jmd :

Le TCD ne me permet pas de donner la mise en forme et la structure précises que je veux aux données. Par exemple, avec ton TCD, je ne peux pas avoir la colonne D (qui répète juste les libellés Chef de programme et Sponsor), ni positionner les noms des chef de programme et sponsor l'un en dessous de l'autre. D'autant que le fichier que je vous ai fourni est une version très simplifiée, donc avec le fichier réel, un TCD ne serait pas top.

@78chris : peux-tu me partager ce que tu as fait ?

En PJ, le fichier avec les premières étapes dans Power Query.

En fait, il me semble, peut-être à tort, que la mise en forme (y compris conditionnelle) des tableaux structurés (bleus) est un peu instable, comme celle des TCD : c'est-à-dire qu'ils ont tendance à retrouver leur mise en forme d'origine à chaque actualisation. Mais je dois me tromper : je n'ai pas encore testé avec le fichier en question.

Les données initiales ne sont pas dans le fichier ci-joint, je ne voyais pas l'intérêt de les transmettre.

RE

Dans ce cas je n'ai pas besoin de ton fichier, je t'envoie ce que j'avais fait avec le 1er

119power-queryvbano.xlsx (27.28 Ko)

C'est pas mal du tout, merci beaucoup !

Plusieurs questions néanmoins :

  • Le VBA est par contre nécessaire pour actualiser automatiquement les tableaux à chaque fois qu'un nouveau Prestataire est sélectionné dans la cellule Choix, non ?
  • Il me manquait cette ligne de code pour réaliser le filtrage sur la colonne Prestataire à partir de la cellule Choix :

    Est-ce qu'il y a un moyen de faire la même chose en passant par les boutons du ruban (comme je ne maitrise pas le langage dans l'éditeur avancé) ?
  • Comment fais-tu pour affecter respectivement les 2 tableaux aux 2 requêtes Tab01 et Tab02 ? Parce qu'ils sont en "connexion uniquement", donc logiquement pas visibles dans les feuilles du classeur...
  • J'ai changé le Prestataire de la cellule Choix et j'ai actualisé les 2 tableaux : leur mise en forme, comme je m'y attendais, a sauté (certaines lignes se sont soudainement remplies en noir) : voir fichier ci-joint. Pourtant, tu me dis que ce n'était pas le cas chez toi ?

RE

Le VBA est par contre nécessaire pour actualiser automatiquement les tableaux à chaque fois qu'un nouveau Prestataire est sélectionné dans la cellule Choix, non ?

Oui une ligne de code

Il me manquait cette ligne de code pour réaliser le filtrage sur la colonne Prestataire à partir de la cellule Choix :

#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Prestataire] = Choix))

Est-ce qu'il y a un moyen de faire la même chose en passant par les boutons du ruban (comme je ne maitrise pas le langage dans l'éditeur avancé) ?

Oui tu fais un filtre normal puis tu corriges dans la barre de formule PQ

Comment fais-tu pour affecter respectivement les 2 tableaux aux 2 requêtes Tab01 et Tab02 ? Parce qu'ils sont en "connexion uniquement", donc logiquement pas visibles dans les feuilles du classeur...

Toutes mes requêtes sont toujours connexions seulement.

Selon besoin j'utilise ensuite Données, connexions existantes pour insérer le tableau résultant où je veux

Pour la couleur je n'ai pas de souci si je recolore les tableaux en bleu clair, et que je refais des changements de Prestataire et actualisations successifs mais je teste sur 2010. Je vérifierai sur 2016 et autres.

Edit : il y a semble-t-il un souci avec T02 dans ton fichier

Super, c'est bon j'ai réussi !

Pour la couleur je n'ai pas de souci si je recolore les tableaux en bleu clair, et que je refais des changements de Prestataire et actualisations successifs mais je teste sur 2010. Je vérifierai sur 2016 et autres.

Sous 2016, c'est très instable en tout cas, impossible de figer quoi que ce soit, mais j'ai contourné le problème en n'utilisant que des mises en forme conditionnelles.

Encore merci pour tout !!

Rechercher des sujets similaires à "appeler tableau issu power query vba"