Les filtres de tableau croisé dynamique standard offrent les pages Afficher tous les filtres de rapport, mais les segments ne prennent pas en charge cette fonctionnalité. Aujourd'hui, certains VBA parcourent toutes les combinaisons de slicers possibles.
Regarder la vidéo
Transcription vidéo
Apprenez Excel de, Podcast Episode 2106: Créez un PDF de chaque combinaison de 3 segments.
Quelle belle question nous avons aujourd'hui. Quelqu'un a écrit, voulait savoir si c'était possible. À l'heure actuelle, ils ont 3 segments exécutant un tableau croisé dynamique. Je ne sais pas à quoi ressemble le tableau croisé dynamique. C'est confidentiel. Je n'ai pas le droit de le voir alors je suppose juste, non? Donc, ce qu'ils font, c'est de choisir un élément de chaque segment, puis de créer un PDF, puis de choisir l'élément suivant et de créer un PDF, puis l'élément suivant, et l'élément suivant, et vous pouvez Imaginez, avec 400 combinaisons de segments, cela pourrait prendre une éternité, et ils ont dit, y a-t-il un moyen de faire passer un programme et de parcourir toutes les options?
J'ai dit, d'accord, voici quelques questions de qualification. Numéro un, nous ne sommes pas sur un Mac, non? Pas Android, pas Excel pour l'iPhone. Ceci est Excel pour Windows. Oui, ont-ils dit. Génial. J'ai dit, la deuxième question vraiment importante est, nous voulons choisir un élément d'un segment, puis finalement l'autre élément du segment, puis l'autre élément du segment. Nous n'avons pas besoin de combinaisons comme ANDY, puis ANDY et BETTY, puis ANDY et CHARLIE, non? C'est fini. Je vais juste faire un élément de chaque segment. Oui oui oui. C'est comme ça que ça va se passer. Parfait, ai-je dit. Alors ici, dites-moi ceci, choisissez chaque segment, allez dans les OUTILS DE LA TRANCHEUSE, OPTIONS, et allez dans PARAMÈTRES DE LA TRANCHEUSE. Nous venons de faire cela il y a 2 épisodes. N'est-ce pas fou? NOM À UTILISER DANS LES FORMULES et je sais que c'est SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,bien? Donc, je pense que je l'ai.
Maintenant, nous allons passer à VBA ici, et, en passant, assurez-vous que vous êtes enregistré en tant que xlsm et assurez-vous que la sécurité de votre macro est définie pour autoriser les macros. S'il est enregistré en xlsx, croyez-moi, vous devez aller faire un FICHIER, ENREGISTRER SOUS, vous allez perdre tout votre travail si vous le laissez en xlsx. Oui, 99,9% des feuilles de calcul que vous utilisez sont xlsx mais celle-ci avec une macro ne fonctionnera pas. ALT + F11. Très bien, voici le code.
Nous allons trouver trois caches de segment, un élément de segment et 3 plages. Pour chacun des caches de slicer, nous allons le définir avec le nom utilisé dans la formule que je viens de vous montrer dans la boîte de dialogue PARAMÈTRES DE TRANCHE. Donc, nous avons les trois de ceux-ci. Je veux effacer tout cela pour m'assurer que nous sommes de retour à tout ce qui est sélectionné. Ce compteur sera utilisé ultérieurement dans le nom du fichier.
Bien. Maintenant, cette section suivante ici, À DROITE, CONSTRUISEZ TROIS LISTES STATIQUES DE TOUS LES ARTICLES DE TRANCHEUSE. Voir outtake # 2 pour voir pourquoi cette folie a dû se produire. Je vais donc déterminer où se trouve la prochaine colonne disponible, en quelque sorte aller au-dessus de 2 de la dernière colonne, rappelez-vous cela pour que je puisse supprimer le contenu plus tard, puis, pour chaque élément SI, slicer, IN SC1. nous allons écrire cette légende de segment dans la feuille de calcul. Lorsque nous en avons terminé avec tous ces éléments de segment, déterminez le nombre de lignes que nous avions aujourd'hui, puis nommez cette plage comme SLICERITEMS1. Nous allons répéter tout cela pour le cache de slicer 2, en passant par 1 colonne, SLICERITEMS2 et SLICERITEMS3.
Laissez-moi vous montrer à quoi cela ressemble à ce stade. Donc, je vais mettre un point d'arrêt ici et nous allons exécuter ce code. Bien. C'était rapide. Nous allons passer à VBA, et loin d'ici sur le côté droit, je vais avoir 3 nouvelles listes. Ces listes sont tout ce qui se trouve dans le slicer, et vous voyez que cela s'appelle SLICERITEMS1, SLICERITEMS2 et SLICERITEMS3, d'accord? Nous nous débarrasserons de cela à la fin, mais cela nous donne quelque chose à parcourir. Retour à VBA.
Bien. Nous allons parcourir tous les éléments de SLICERITEMS1, effacer le filtre du cache de segment 1, puis nous allons parcourir, un à la fois, chaque élément de segment et voir si cet élément de segment est = à ceci CELL1.VALUE et, encore une fois, nous parcourons chacune des valeurs. Donc, la première fois, ce sera ANDY et ensuite BETTY et, vous savez, et ainsi de suite.
C'est frustrant. Je n'ai trouvé aucun moyen d'éteindre tous les trancheurs à la fois. J'ai même essayé d'enregistrer le code et de choisir un slicer, et le code enregistré retournait 9 slicers et allumait le seul slicer, d'accord? Tellement frustrant que je n'ai rien trouvé de mieux que ça, mais je n'ai rien trouvé de mieux que ça.
Donc, nous définissons le premier slicer = sur ANDY. Ensuite, nous passons à travers, et pour le deuxième slicer, nous allons le définir = sur le premier élément. Pour le troisième segment, définissez-le = sur le premier élément.
Bien. Ensuite, en bas ici, DÉCIDEZ SI CECI EST UNE COMBINAISON VALIDE. Je dois vous expliquer pourquoi c'est important. Si nous, en tant qu'humains, nous faisons cela, ANDY, nous ne choisirions pas A52 car il est clairement grisé, mais la macro va être trop stupide et elle va choisir A52 puis 104, et cela va créer ce vide tableau croisé dynamique. Donc, il y a mille combinaisons possibles ici. Je sais qu'il n'y a que 400 rapports possibles. C'est ce que la personne m'a dit, et donc nous allons obtenir 600 fois où nous allons créer un PDF de ce rapport (moche - 04:45).
Donc, ce que je vais faire est de regarder ici sur l'onglet ANALYSER - il s'appelait OPTIONS en 2010 - et voir quel est le nom de ce tableau croisé dynamique, et je veux voir combien de lignes on a. Dans mon cas, si j'obtiens 2 lignes, je sais que c'est un rapport que je ne veux pas exporter. Si j'obtiens plus de 2 lignes, 3, 4, 5, 6, alors je sais que c'est un rapport que je souhaite exporter. Vous allez devoir comprendre dans votre situation ce que c'est.
Bien. C'est pourquoi nous vérifions si le tableau croisé dynamique 2 et, c'est le nom qui était là-bas dans le ruban, .TABLERANGE2.ROWS.COUNT est> 2. Si ce n'est pas> 2, nous ne voulons pas créer un PDF, d'accord? Donc, cette instruction IF jusqu'à ce END IF indique que nous allons uniquement créer les fichiers PDF pour les combinaisons de rapports qui ont des valeurs. MYFILENAME, j'ai créé un dossier appelé C: REPORTS. C'est juste un dossier vide. C: RAPPORTS. Vous vous assurez que vous disposez d'un dossier et utilisez le même nom de dossier dans la macro. C: REPORTS / et le nom du fichier sera REPORT001.PDF. Maintenant, le compteur que nous avons initialisé il y a 1 en utilisant FORMAT, ce qui équivaut dans Excel à dire le texte du compteur, et 000. De cette façon, je vais obtenir 001, puis 002, puis 003, puis 004. Ils va trier correctement.Si je venais d'appeler ce REPORT1, et que plus tard j'aurais un REPORT10 et 11, et plus tard un REPORT100, ils vont tous trier ensemble lorsqu'ils ne vont pas ensemble, d'accord? Donc, en créant le nom du fichier au cas où le fichier existe depuis la dernière fois que nous l'avons exécuté, nous allons le tuer. En d'autres termes, supprimez-le. Bien sûr, si vous essayez de tuer un fichier qui n'est pas là, ils généreront une erreur. Donc, si nous obtenons une erreur dans la ligne suivante, c'est très bien. Continuez simplement, mais j'ai réinitialisé la vérification des erreurs ON ERROR GOTO 0.Bien sûr, si vous essayez de tuer un fichier qui n'est pas là, ils généreront une erreur. Donc, si nous obtenons une erreur dans la ligne suivante, c'est très bien. Continuez simplement, mais j'ai réinitialisé la vérification des erreurs ON ERROR GOTO 0.Bien sûr, si vous essayez de tuer un fichier qui n'est pas là, ils généreront une erreur. Donc, si nous obtenons une erreur dans la ligne suivante, c'est très bien. Continuez simplement, mais j'ai réinitialisé la vérification des erreurs ON ERROR GOTO 0.
Here’s the ACTIVE SHEET, EXPORT AS FIXED FORMAT, as a PDF, there's the file name, all those choices, and then I increment the counter, so that way, next time we find one that has records, we will be creating REPORT002.PDF. Finish those three loops and then CLEAR OUT THE STATIC LISTS. So, I'll remember which column we were, resize 1 row, 3 columns, ENTIRECOLUMN.CLEAR, and then a nice little message box there to show that things have been created. Okay. Let's run it.
Bien. Maintenant, ce qui devrait se passer ici, c'est que si nous allons regarder dans l'Explorateur Windows, le voilà. Bien. C'est créer… comme, chaque seconde, nous en obtenons 2, 3 ou 4 ou plus. Je vais mettre cela en pause et le laisser fonctionner. Bien. Nous y sommes. 326 rapports ont été créés. Il parcourait les 1000 possibilités et ne conservait que celles où il y avait un résultat réel. D'accord, de 9h38 à 9h42, 4 minutes pour faire tout ça, mais toujours plus rapide que de faire le 400, d'accord?
Alright. So, that's the macro way to do this. The other thing that struck me here that it may or may not work. It's really tough to say. Let's take our data and I'm going to move the data to a brand new workbook. MOVE OR COPY, CREATE A COPY, to a NEW BOOK, click OK, and we’re going to use a trick here that I first learned from Szilvia Juhasz - a great Excel consultant out in Southern California -- and we're going to add a KEY field here. The KEY field is = REVIEWER & ANTENNA & DISCIPLINE. We'll copy that down and we'll insert a new pivot table. Click OK, and we're going to take that field, the KEY field, and move it up to the old-fashioned FILTERS, and then let's see. (Let's dispel a little report here with - 08:30) REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, like that.
Alright, now, normally what we would do here is would come open this filter and choose one item from the filter, but the trick from Szilvia is that we can take this pivot table and go to either the ANALYZE tab in ’13 or ’16, or the OPTIONS tab in 2010, open the OPTIONS dropdown, say SHOW REPORT FILTER PAGES, SHOW ALL PAGES OF KEY, and what it's doing right now is it’s inserting a new worksheet for every unique combination of the KEY, probably 300 and some files, alright? Now, how many worksheets can you have in a workbook? Well, that number is different on every computer and it depends on how complicated the workbook is because it's limited by available memory, but here we start on ANDY B37 112. I’m going to press CONTROL and this arrow down to JOE, like that.
The beautiful advantage here is, when I do FILE, EXPORT, CREATE A PDF, and then ALLREPORTS, we’re going to end up with a single PDF with all 326 reports in it. Now, we could have created a single PDF using Adobe Acrobat, select all of these reports, right click, and COMBINE FILES IN ACROBAT, but that requires you have a full version of Acrobat, not just Acrobat Reader.
So, this great trick using SHOW REPORT FILTER PAGES from Szilvia might be a great, great alternative if you have enough memory to create all the versions.
Alright. To learn more about VBA, check out this book Excel 2016 VBA And Macros by Bill Jelen and Tracy Syrstad. That will get you up the VBA learning curve.
Alright. The goal is to loop through all combinations in 3 slicers and generate a PDF for each. Used a little VBA to loop through those slicers. Save as PDF using VBA. The alternate solution there at the end is Szilvia Juhasz’s SHOW REPORT FILTER PAGES and then export the whole thing as PDF.
Hey. I want to thank you for stopping by. We'll see you next time for another netcast from.
Well, this will be an outtake. First time I ran this darn thing, I got a 1000 of them, and every darn one of them was Andy A52 104. I'm like what the heck is going on? Except I didn’t say heck.
Alright. So, here, watch this code. This was the code I had. I said I'm going to go through all of the filters FOR EACH SI IN SC1.SLICERITEMS and then I set it = to FALSE, and then the one that I want, I'll set = to TRUE, right? Sound like a great, great bit of code, alright?
So, here's what happens. The first one is Andy, goes away. Betty goes away. Charlie goes away. Dale. Here, I'll just keep pressing F8, F8, F8, F8. I'm down to the last one. This is JOE. I'm about to set JOE = to FALSE and watch what happens over there in Excel. Bam. Once you turn JOE off, it turns them all back on. I mean, that stinks, Excel, and then I would try and turn, what is it, ANDY back on and turning ANDY back on when everybody else is already on. So, it ran through… it created a 1000 of the PDFs, every stinking one. It was ANDY A52 104. It's funny now. It wasn't then.
Alright. Here’s another outtake. Why did I go to the trouble of building the list, the static list, off to the right hand side so I can loop through that static list? Well, originally, I was looping through all of the items in the slicers themselves and it was causing some wrong results. See, here, Andy A52 112 should be 0, but when I actually ran the loop, ANDY A52 112 is showing up with six rows. I’m like, well, that can't be. So, over here, my code, ALT+F11, I put a thing, if SI1.CAPTION=ANDY, SI2.CAPTION=A52, SI3 CAPTION=112, THEN STOP, right? So let's run this code, then stop.
There we are, and I will come back. We should have ANDY A52 112, but when I look, ANDY, it’s not A52, it’s D33. What the heck is going on, and then I come back here, ALT+F11, and I right-click and say that I want to ADD A WATCH, and when I look at this, it claims that the caption is A52 but, very clearly, it's D33. So, is this a bug or am I just violating some weird rule by looping through a collection of 10 items when the order of those 10 items is constantly being reordered? It seems like that must be the problem. Hence, we went with the static list off to the right.
Et la troisième sortie, d'accord? C'est celui qui est fou. Si je veux enregistrer une macro, si je veux (écrire une macro - 13:35) pour choisir un seul élément, découvrez comment le faire en utilisant DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, cliquez sur OK, et nous en choisissons simplement un article. FLO. Cliquez sur STOP RECORDING, puis nous allons ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT ça, et, bien sûr, ils rendent FLO TRUE et puis tout le monde FLASE. Cela signifie que si j'avais un slicer avec 100 éléments, ils devraient y mettre 100 lignes de code pour désélectionner tout le reste. Cela semble incroyablement inefficace, mais vous y êtes.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2106.xlsx
