Listas no Excel com VBA e Power Query – Criação de lista de e-mails

Neste artigo é demonstrado como trabalhar a criação de uma lista personalizada de forma automática, tanto com o VBA, quanto com o Power Query no Excel.

No exemplo é demonstrado como podemos criar uma lista personalizada de e-mails com base somente na quantidade dela e no nome da filial.

Perceba que o prefixo dos e-mails e os sufixos são semelhantes, mudando apenas o número sequencial. O objetivo é que esta lista sirva para a infraestrutura possa criar e-mails para cada uma das filiais da sua empresa.

Então, como nós transformamos esta lista simples na lista sequencial de e-mails que variam a quantidade conforme a necessidade de cada filial?

Nós temos três caminhos, fórmulas, VBA ou o Power Query.

Nós veremos como fazer isso de duas formas, via VBA ou via PowerQuery, a de fórmulas iríamos ter um trabalho enorme.

Para o desenvolvimento em VBA nós utilizamos o seguinte código fonte:

Veja aqui que foi identificada a quantidade de registros, depois foi feito um loop pela quantidade de registros inserindo as linhas conforme a quantidade de registros que são necessários para cada uma das filiais.

Public Sub lsPreencherPlanilha()
    Dim lUltimaLinhaAtiva   As Long
    Dim lContadorLista      As Long
    Dim lContadorSubLista   As Long
    Dim lFilial             As String
    Dim lQuantidade         As Long
    
    Application.ScreenUpdating = False
    
    lUltimaLinhaAtiva = Planilha1.Cells(Planilha1.Rows.Count, 1).End(xlUp).Row
    lContadorLista = 2
    
    While lContadorLista <= lUltimaLinhaAtiva
        lFilial = Range("A" & lContadorLista).Value
        lQuantidade = Range("B" & lContadorLista).Value
        
        For lContadorSubLista = 1 To lQuantidade
            Range("A" & lContadorLista).Value = lFilial
            Range("C" & lContadorLista).Value = lFilial & CStr(lContadorSubLista) & "@guiadoexcel.com.br"
            
            'If lContadorSubLista < lQuantidade Then
                Rows(lContadorLista + 1).Insert
                lContadorLista = lContadorLista + 1
                lUltimaLinhaAtiva = Planilha1.Cells(Planilha1.Rows.Count, 1).End(xlUp).Row
            'End If
        Next lContadorSubLista
        
        lContadorLista = lContadorLista + 1
    Wend
    
    Application.ScreenUpdating = True
    
    MsgBox "Processo concluído!"
End Sub

Vamos rodar pra ver como fica.

Não é difícil de fazer, mas necessita de conhecimento de VBA e além disso, o Excel permite você fazer isso de uma forma muito mais inteligente e rápida.

O meu amigo Joviano Silveira, que está mais avançado no uso deste recurso fez em alguns minutos e me passou a solução, que eu aprendi e agora vou ensinar pra vocês, no Power Query.

Se você já tem o Excel 2016 você pode ir na guia Dados e em Obter e Transformar, se forem as versões 2010 ou 2013 você pode instalar gratuitamente, basta baixar no site da Microsoft, e neste caso seguir os passos de algum tutorial da internet de como instalar.

1. Primeira coisa você tem que transformar seus dados, que estão em forma de matriz em uma tabela.
2. Para isso pressione CTRL+ALT+T, ou ALT+T+TA.
3. Em design vamos dar um nome pra nossa tabela de dimensão, neste caso vamos chamar ela de dFiliais.
4. Para isso nós temos esta planilha, chamada Lista e-mails
5. Na primeira coluna nós temos o nome da filial e no segundo a quantidade de e-mails que devem ser criados.
6. Então primeiro vamos carregar esta tabela para o modelo de dados.

Agora nós precisamos que esta lista tenha mais linhas, uma para cada um dos e-mails que serão criados, não é mesmo?

Para isso nós usaremos a função List.Numbers do PowerQuery.

1. Selecione coluna personalizada e vamos escrever na linguagem M o que precisamos.
2. = List.Numbers(1, 20, 1), o primeiro campo é em que número começamos a nossa lista, o segundo é até quanto, e o terceiro o é o incremental.

   

3. Damos um ok e pronto, temos a nossa lista, veja que ela está recolhida, então no ícone logo acima da coluna para expandir.

   

Veja que cada item da nossa lista tem 20 linhas agora, vamos criar uma outra coluna, agora condicional.

Esta coluna condicional servirá para que verifiquemos quais os itens que devemos manter na nossa lista e quais devemos excluir.

Fazemos aqui então: a coluna quantidade é maior ou igual a coluna total, se for escrever manter, senão excluir.

E temos a nossa coluna criada ao lado, agora vamos filtrar e deixar somente as “Mantém“

Criamos mais uma coluna calculada, neste caso uma coluna aonde nós iremos fazer o seguinte, concatenar estes dados.

Lembrando que para concatenar números é necessário usar o Number.ToText. E já temos agora o nosso e-mail.

Por fim vamos excluir as colunas que não precisamos mais.

E clicamos em carregar e agora nós temos a nossa lista pronta para ser enviada.

Legal que se eu incluir mais e-mails, ou mesmo alterar a quantidade de um, não preciso mais rodar novamente, o PowerQuery atualiza automaticamente estes dados. Alterar valores, incluir e excluir linhas.

Abaixo o link para baixar esta planilha de exemplo.

Baixe a planilha

Forte abraço, e até o próximo artigo.

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: