Os filtros regulares da tabela dinâmica oferecem as páginas Mostrar todos os filtros de relatório, mas os Slicers não oferecem suporte a essa funcionalidade. Hoje, alguns VBA percorrem todas as combinações possíveis de fatiador.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com, Podcast Episódio 2106: Crie um PDF de cada combinação de 3 Slicers.
Que grande pergunta temos hoje. Alguém escreveu, queria saber se era possível. No momento, eles têm 3 segmentações de dados executando uma tabela dinâmica. Não sei como é a tabela dinâmica. É confidencial. Não tenho permissão para ver, então estou apenas supondo, certo? Então, o que eles estão fazendo é escolher um item de cada fatiador e criar um PDF, e então escolher o próximo item e criar um PDF, e então o próximo item, e o próximo item, e você pode imagine, com 400 combinações de fatiadores, isso poderia levar uma eternidade, e eles disseram, há alguma maneira de fazer um programa percorrer e percorrer todas as opções?
Eu disse, tudo bem, aqui estão algumas perguntas de qualificação. Número um, não estamos em um Mac, certo? Nem Android, nem Excel para o iPhone. Este é o Excel para Windows. Sim, eles disseram. Ótimo. Eu disse, a segunda pergunta realmente importante é, queremos escolher um item de um divisor e, eventualmente, o outro item do divisor e, em seguida, o outro item do divisor. Não precisamos de combinações como ANDY, e depois ANDY e BETTY, e depois ANDY e CHARLIE, certo? Isso está fora. Vou fazer apenas um item de cada fatiador. Sim Sim Sim. É assim que vai ser. Perfeito, eu disse. Então aqui, diga-me isso, escolha cada fatiador, vá para FERRAMENTAS DE FATIADORES, OPÇÕES e vá para CONFIGURAÇÕES DE FATIAS. Acabamos de fazer isso 2 episódios atrás. Não é uma loucura? NOME PARA USAR EM FÓRMULAS e sei que é SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,tudo bem? Então, acho que entendi.
Agora, vamos mudar para o VBA aqui e, a propósito, ter certeza de que você está salvo como xlsm e ter certeza de que sua segurança de macro está configurada para permitir macros. Se estiver salvo como xlsx, acredite em mim, você tem que fazer um ARQUIVO, SALVAR COMO, você vai perder todo o seu trabalho se deixar como xlsx. Sim, 99,9% das planilhas que você usa são xlsx, mas esta com uma macro não funcionará. ALT + F11. Tudo bem, então aqui está o código.
Encontraremos três caches de segmentação, um item de segmentação e 3 intervalos. Para cada um dos caches do fatiador, vamos defini-lo com o nome usado na fórmula que acabei de mostrar na caixa de diálogo CONFIGURAÇÕES DO SLICER. Então, nós temos três desses. Quero limpar tudo isso para ter certeza de que estamos de volta a tudo que está sendo selecionado. Este contador será usado posteriormente no nome do arquivo.
Tudo bem. Agora, esta próxima seção aqui, PARA A DIREITA, CONSTRUA TRÊS LISTAS ESTÁTICAS DE TODOS OS ITENS FATIADORES. Veja o outtake # 2 para ver por que essa loucura teve que acontecer. Então, vou descobrir onde está a próxima coluna disponível, meio que passar de 2 da última coluna, lembre-se disso para que eu possa excluir as coisas mais tarde e, em seguida, para cada SI, item de segmentação, IN SC1.SLICERITEMS, vamos escrever essa legenda de segmentação na planilha. Quando terminarmos com todos esses itens de segmentação, descubra quantas linhas temos hoje e nomeie esse intervalo como SLICERITEMS1. Vamos repetir tudo isso para o cache de segmentação 2, passando por 1 coluna, SLICERITEMS2 e SLICERITEMS3.
Deixe-me mostrar como é neste momento. Então, vou colocar um ponto de interrupção bem aqui e vamos executar este código. Tudo bem. Isso foi rápido. Vamos mudar para o VBA e, bem longe aqui, do lado direito, vou obter 3 novas listas. Essas listas são tudo o que está no divisor e você vê que se chama SLICERITEMS1, SLICERITEMS2 e SLICERITEMS3, certo? Vamos nos livrar disso no final, mas isso nos dá algo para percorrer. De volta ao VBA.
Tudo bem. Vamos percorrer todos os itens em SLICERITEMS1, limpar o filtro do cache 1 do divisor e, em seguida, examinar, um de cada vez, cada item do divisor e ver se este item do divisor é = para este CELL1.VALUE e, novamente, estamos percorrendo cada um dos valores. Então, na primeira vez, será ANDY e depois BETTY e, você sabe, e assim por diante.
É frustrante. Não consegui encontrar nenhuma maneira de desligar todos os cortadores de uma vez. Eu até tentei gravar o código e escolher um fatiador, e o código gravado estava retornando 9 fatiadores e ligando o único fatiador, certo? Tão frustrante que não consegui encontrar nada melhor do que isso, mas não consegui encontrar nada melhor do que isso.
Portanto, definimos o primeiro divisor = como ANDY. Em seguida, avançamos e, para o segundo fatiador, vamos definir it = para o primeiro item. Para o terceiro divisor, defina = para o primeiro item.
Tudo bem. Então, aqui embaixo, DECIDA SE ESTA É UMA COMBINAÇÃO VÁLIDA. Eu tenho que explicar a você por que isso é importante. Se nós, como humanos, estamos fazendo isso, ANDY, não escolheríamos A52 porque está claramente acinzentado, mas a macro vai ser muito estúpida e vai escolher A52 e depois 104, e vai criar este vazio tabela dinâmica. Portanto, há mil combinações possíveis aqui. Eu sei que existem apenas 400 relatórios possíveis. Isso é o que a pessoa me disse, então vamos chegar 600 vezes onde vamos criar um PDF deste (feio - 04:45) relatório.
Então, o que vou fazer é olhar aqui na guia ANALYZE - ela se chamava OPTIONS em 2010 - e ver qual é o nome desta tabela dinâmica, e quero ver quantas linhas Nós temos. No meu caso, se obtiver 2 linhas, sei que é um relatório que não desejo exportar. Se obtiver mais de 2 linhas, 3, 4, 5, 6, sei que é um relatório que desejo exportar. Você vai ter que descobrir em sua situação qual é.
Tudo bem. Então, é por isso que estamos verificando se a tabela dinâmica 2 e, esse é o nome que estava lá na faixa, .TABLERANGE2.ROWS.COUNT é> 2. Se não for> 2, não queremos crie um PDF, certo? Portanto, esta instrução IF até este END IF está dizendo que só vamos criar os PDFs para as combinações de relatório que têm valores. MYFILENAME, criei uma pasta chamada C: REPORTS. É apenas uma pasta vazia. C: RELATÓRIOS. Certifique-se de que possui uma pasta e usa o mesmo nome de pasta na macro. C: REPORTS / e o nome do arquivo será REPORT001.PDF. Agora, o contador que inicializamos de volta é 1 usando FORMAT, que é equivalente no Excel a dizer o texto do contador e 000. Dessa forma, obterei 001, depois 002, depois 003 e depois 004. Eles vai classificar corretamente.Se eu tivesse apenas chamado este REPORT1, e mais tarde eu tenho um REPORT10 e 11, e mais tarde no REPORT100, todos eles vão se classificar quando não estiverem juntos, certo? Portanto, criando o nome do arquivo caso o arquivo exista da última vez que executamos isso, iremos eliminá-lo. Em outras palavras, exclua-o. Claro, se você tentar matar um arquivo que não está lá, eles irão gerar um erro. Então, se obtivermos um erro na próxima linha, tudo bem. Continue, mas reinicio o erro verificando ON ERROR GOTO 0.Claro, se você tentar matar um arquivo que não está lá, eles irão gerar um erro. Então, se obtivermos um erro na próxima linha, tudo bem. Continue, mas reinicio o erro verificando ON ERROR GOTO 0.Claro, se você tentar matar um arquivo que não está lá, eles irão gerar um erro. Então, se obtivermos um erro na próxima linha, tudo bem. Continue, mas reinicio o erro verificando ON ERROR GOTO 0.
Aqui está a FOLHA ATIVA, EXPORTAR COMO FORMATO FIXO, como PDF, tem o nome do arquivo, todas essas opções, e então eu incremento o contador, para assim, da próxima vez que encontrarmos um que tenha registros, estaremos criando REPORT002.PDF . Conclua esses três loops e APAGUE AS LISTAS ESTÁTICAS. Então, vou lembrar em qual coluna éramos, redimensionar 1 linha, 3 colunas, ENTIRECOLUMN.CLEAR e, em seguida, uma pequena caixa de mensagem para mostrar que as coisas foram criadas. OK. Vamos ver.
Tudo bem. Agora, o que deve estar acontecendo aqui é se formos e olharmos no Windows Explorer, aí está. OK. É criar … tipo, a cada segundo, obtemos 2 ou 3 ou 4 ou mais. Vou pausar isso e deixá-lo funcionar. Tudo bem. Aqui estamos. 326 relatórios foram criados. Ele percorreu todas as 1000 possibilidades e manteve apenas aquelas em que havia um resultado real. Tudo bem, das 9:38 às 9:42, 4 minutos para fazer tudo isso, mas ainda mais rápido do que fazer 400, certo?
Tudo bem. Então, essa é a maneira macro de fazer isso. A outra coisa que me chamou a atenção aqui é que pode ou não funcionar. É muito difícil dizer. Vamos pegar nossos dados e vou movê-los para uma nova pasta de trabalho. MUDE OU COPIE, CRIE UMA CÓPIA, para um NOVO LIVRO, clique em OK e vamos usar um truque que aprendi com Szilvia Juhasz - um grande consultor de Excel do sul da Califórnia - e vamos adicione um campo KEY aqui. O campo KEY é = REVIEWER & ANTENNA & DISCIPLINE. Vamos copiar isso e inserir uma nova tabela dinâmica. Clique em OK, e vamos pegar esse campo, o campo KEY, e movê-lo para os antigos FILTROS, e então vamos ver. (Vamos dissipar um pequeno relato aqui com - 08:30) REVISOR, ANTENA, DISCIPLINA e RECEITA, assim.
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.
E a terceira saída, certo? Esse é o louco. Se eu quiser gravar uma macro, se eu quiser (escrever uma macro - 13:35) escolher apenas um item, descubra como fazer isso usando DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, clique em OK, e nós simplesmente escolhemos um item. FLO. Clique em STOP RECORDING, então vamos ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT, e, com certeza, eles tornam FLO TRUE e então todos os outros FLASE. Isso significa que se eu tivesse um fatiador com 100 itens, eles teriam que colocar 100 linhas de código para desmarcar todo o resto. Parece incrivelmente ineficiente, mas aí está você.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2106.xlsx
