Vanliga pivottabelfilter erbjuder sidorna Visa alla rapportfilter, men skivare stöder inte denna funktion. Idag kan vissa VBA slinga igenom alla möjliga skivkombinationer.
Kolla på video
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2106: Skapa en PDF av varje kombination av 3 skivor.
Vilken bra fråga vi har idag. Någon skrev in, ville veta om det var möjligt. Just nu har de tre skivor som kör ett pivottabell. Jag vet inte hur svängbordet ser ut. Det är konfidentiellt. Jag får inte se det så jag gissar bara, eller hur? Så vad de gör är att de väljer ett objekt från varje skivare och sedan skapar en PDF, och sedan går och väljer nästa objekt och skapar en PDF, och sedan nästa objekt och nästa objekt, och du kan tänk dig, med 400 kombinationer av skivor, kan detta ta evigt, och de sa, finns det något sätt att få ett program att gå igenom och slinga igenom alla alternativ?
Jag sa, okej, här är några kvalificerade frågor. Nummer ett, vi är inte på en Mac, eller hur? Inte Android, inte Excel för iPhone. Detta är Excel för Windows. Ja, sa de. Bra. Jag sa, den andra riktigt viktiga frågan är att vi vill välja ett objekt från en skivare och sedan så småningom det andra från skivaren och sedan det andra föremålet från skivaren. Vi behöver inte kombinationer som ANDY, och då ANDY och BETTY, och sedan ANDY och CHARLIE, eller hur? Det är ute. Jag ska bara göra ett objekt från varje skivare. Ja ja ja. Så kommer det att gå. Perfekt, sa jag. Så här, berätta för mig detta, välj varje skivare, gå till SKÄRVÄRDEN, ALTERNATIV och gå till SKÄRVINSTÄLLNINGAR. Vi gjorde precis detta för 2 avsnitt sedan. Är det inte galet? NAMN ATT ANVÄNDA I FORMULER och jag vet att det är SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,OK? Så jag tror att jag har det.
Nu ska vi byta till VBA här, och förresten, se till att du är sparad som xlsm och se till att din makrosäkerhet är inställd på att tillåta makron. Om det är sparat som xlsx, lita på mig, du måste göra en FIL, SPARA AS, du kommer att förlora allt ditt arbete om du lämnar det som xlsx. Ja, 99,9% av kalkylarken som du använder är xlsx men den här med ett makro fungerar inte. ALT + F11. Okej, så här är koden.
Vi kommer att hitta tre skivcacher, en skivartikel och 3 intervall. För varje skivcache kommer vi att ställa in det till namnet som används i formeln som jag just visade dig i dialogrutan SKÄRNINGSINSTÄLLNINGAR. Så vi har de tre av dessa. Jag vill rensa alla för att se till att vi är tillbaka till allt som väljs. Denna räknare kommer att användas i filnamnet senare.
OK. Nu, nästa avsnitt här, BYGGT TILL HÖGER TRE STATISKA LISTER AV ALLA SKIVARE. Se uttag # 2 för att se varför denna galenskap måste ske. Så jag ska ta reda på var nästa tillgängliga kolumn är, typ av att gå över 2 från den sista kolumnen, kom ihåg det så att jag kan ta bort grejerna senare, och sedan för varje SI, skivartikel, I SC1.SLICERITEMS, vi ska skriva den skivade texten till kalkylbladet. När vi är klara med alla dessa skivartiklar, ta reda på hur många rader vi har idag och namnge det intervallet som SLICERITEMS1. Vi kommer att upprepa det hela för skivcache 2, gå över 1 kolumn, SLICERITEMS2 och SLICERITEMS3.
Låt mig visa dig hur det ser ut just nu. Så jag lägger en brytpunkt här och vi kommer att köra den här koden. OK. Det var snabbt. Vi kommer att byta till VBA, och långt borta till höger kommer jag att få 3 nya listor. Dessa listor är allt som finns i skivaren, och du ser att det heter SLICERITEMS1, SLICERITEMS2 och SLICERITEMS3, okej? Vi kommer att bli av med det i slutet, men det ger oss något att gå igenom. Tillbaka till VBA.
OK. Vi kommer att slinga igenom alla artiklar i SLICERITEMS1, rensa filtret för slicer cache 1, och sedan ska vi gå igenom, en i taget, genom varje skivartikel och se om detta skivartikel är = till detta CELL1.VALUE, och igen, vi går igenom vart och ett av värdena. Så första gången kommer det att bli ANDY och sedan BETTY och, du vet, och så vidare.
Det är frustrerande. Jag kunde inte hitta något sätt att stänga av alla skivorna på en gång. Jag försökte till och med spela in koden och välja en skivare, och den inspelade koden gav tillbaka 9 skivor och slog på den ena skivaren, okej? Så frustrerande att jag inte kunde hitta något bättre än så men jag kunde inte hitta något bättre än så.
Så vi ställer in den första skivaren = till ANDY. Sedan går vi igenom, och för den andra skivaren ska vi ställa in den = till det första objektet. För den tredje skivaren, ställ den = till det första objektet.
OK. Då, här nere, BESLUT OM DETTA ÄR EN GILDIG KOMBINATION. Jag måste förklara för dig varför det är viktigt. Om vi, som människor, gör det här, ANDY, skulle vi inte välja A52 eftersom det tydligt är nedtonat, men makrot kommer att bli för dumt och det kommer att välja A52 och sedan 104, och det kommer att skapa detta tomma vridbord. Så det finns tusen möjliga kombinationer här. Jag vet att det bara finns 400 möjliga rapporter. Det är vad personen sa till mig, och så kommer vi att få 600 gånger där vi ska skapa en PDF av den här (fula - 04:45) rapporten.
Så vad jag ska göra är att jag kommer att titta här på fliken ANALYSERA - den hette ALTERNATIV 2010 - och se vad namnet på denna pivottabell är, och jag vill se hur många rader vi får. I mitt fall, om jag får två rader, vet jag att det är en rapport som jag inte vill exportera. Om jag får mer än 2 rader, 3, 4, 5, 6 vet jag att det är en rapport som jag vill exportera. Du måste räkna ut i din situation vilken det är.
OK. Så det är därför vi kollar för att se om pivottabellen 2 och, det är namnet som var där i menyfliksområdet .TABLERANGE2.ROWS.COUNT är> 2. Om det inte är> 2 vill vi inte skapa en PDF, okej? Så detta IF-uttalande ner till detta END IF säger att vi bara ska skapa PDF-filer för rapportkombinationerna som har värden. MYFILENAME, jag skapade en mapp som heter C: RAPPORTER. Det är bara en tom mapp. C: RAPPORTER. Du ser till att du har en mapp och använder samma mappnamn i makrot. C: RAPPORTER / och namnet på filen kommer att vara REPORT001.PDF. Nu, räknare som vi initialiserade tillbaka upp är det 1 med FORMAT, vilket motsvarar i Excel att säga räknartexten och 000. På det sättet kommer jag att få 001, sedan 002, sedan 003 och sedan 004. De kommer att sortera korrekt.Om jag bara hade kallat den här RAPPORT1, och senare har jag en RAPPORT10 och 11, och senare på RAPPORT100, kommer alla att sortera ihop när de inte hör hemma, okej? Så när du skapar filnamnet om filen existerar från den senaste gången vi körde den här, kommer vi att döda den. Med andra ord, ta bort den. Naturligtvis, om du försöker döda en fil som inte finns, kommer de att kasta ett fel. Så om vi får ett fel i nästa rad är det bra. Fortsätt bara, men sedan återställer jag felkontrollen PÅ FEL GOTO 0.Naturligtvis, om du försöker döda en fil som inte finns, kommer de att kasta ett fel. Så om vi får ett fel i nästa rad är det bra. Fortsätt bara, men sedan återställer jag felkontrollen PÅ FEL GOTO 0.Naturligtvis, om du försöker döda en fil som inte finns, kommer de att kasta ett fel. Så om vi får ett fel i nästa rad är det bra. Fortsätt bara, men sedan återställer jag felkontrollen PÅ FEL GOTO 0.
Här är det AKTIVA ARKET, EXPORT SOM FASTT FORMAT, som en PDF, det finns filnamnet, alla dessa val, och sedan ökar jag räknaren, så på det sättet, nästa gång vi hittar en som har poster, kommer vi att skapa REPORT002.PDF . Avsluta de tre slingorna och rensa sedan bort de statiska listorna. Så jag kommer ihåg vilken kolumn vi var, ändra storlek på 1 rad, 3 kolumner, ENTIRECOLUMN.CLEAR och sedan en trevlig liten meddelanderuta där för att visa att saker har skapats. Okej. Låt oss köra det.
OK. Nu, vad som ska hända här är om vi går och tittar i Windows Explorer, där är det. Okej. Det skapar … som, varje sekund, vi får 2 eller 3 eller 4 eller mer. Jag pausar det här och låter det springa. OK. Där är vi. 326 rapporter har skapats. Den slog igenom alla 1000 möjligheter och behöll bara de där det fanns ett faktiskt resultat. Okej, från 9:38 till 9:42, 4 minuter att göra allt det, men ändå snabbare än att göra 400, okej?
OK. Så det är makrot sättet att göra detta. Det andra som slog mig här att det kanske eller inte fungerar. Det är verkligen svårt att säga. Låt oss ta våra data och jag ska flytta data till en helt ny arbetsbok. FLYTTA ELLER KOPIERA, SKAPA KOPIERING, till en NY BOK, klicka på OK, så ska vi använda ett knep här som jag först lärde mig av Szilvia Juhasz - en bra Excel-konsult ute i södra Kalifornien - och vi ska lägg till ett KEY-fält här. KEY-fältet är = REVIEWER & ANTENNA & DISCIPLINE. Vi kopierar ner det och infogar en ny pivottabell. Klicka på OK, så tar vi det fältet, KEY-fältet, och flyttar det upp till de gammaldags FILTER, och sedan får vi se. (Låt oss skingra en liten rapport här med - 08:30) ANMÄLNING, ANTENNA, DISCIPLINER och INKOMSTER, så.
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.
Och det tredje uttaget, okej? Det här är den som är galen. Om jag vill spela in ett makro, om jag vill (skriva ett makro - 13:35) bara välja ett objekt, ta reda på hur man gör det genom att använda DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klicka på OK, och vi väljer helt enkelt ett Artikel. FLO. Klicka på STOPP INSPELNING, sedan går vi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, REDIGERA det och, nog, de gör FLO TRUE och sedan alla andra FLASE. Det betyder att om jag hade en skivare med 100 artiklar i, skulle de behöva lägga in 100 rader kod för att avmarkera allt annat. Verkar oerhört ineffektivt men där är du.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2106.xlsx
