none
Excel VBA RRS feed

  • Pergunta

  • Faço um controle de alertas de vírus em uma planilha no excel, como geralmente acontece mais de um alerta por usuário, preciso concatenar os valores repetidos (alertas que ocorreram dentro de uma exato período e tempo) de um usuário, para pode-los enviar por e-mail. Alguém consegue me ajudar?

    quinta-feira, 14 de maio de 2015 18:46

Respostas

  • Olá, boa noite.

    Desculpe a demora, mas essa semana foi corrida.

    Não tive muito tempo para pensar no assunto, mas fiz uma alternativa para utilizar a Tabela Dinâmica para coletar os dados. A ideia, apesar de não ser eficiente, é coletar os dados selecionando os filtros na tabela. Não gosto muito da sua proposta de enviar email automático quando selecionada dada célula (isso pode gerar um grande transtorno), por isso coloquei um botão. Também não mesclei com o envio de email, isso deixo pra vc. Também deixei pra vc criar um flag que indique que o email já foi enviado (pode ser um campo de página da tabela, buscando a informação na sua tabela fonte).

    Coloquei a saída em MsgBox apenas para uma visualização simplista (isso não tem sentido no código). Use as informações diretamente para envio das informações.

    Segue o código e a planilha que usei de exemplo:

    Sub TesteDados()
    
    Erro = 0
    On Error GoTo Proximo
    ActiveSheet.PivotTables("Tabela dinâmica1").ClearAllFilters
    
    nx1 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems.Count   'Item 1: Eventos
    nx2 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems.Count   'Item 2: Email
    'nx3 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(3).PivotItems.Count
    'nx4 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(4).PivotItems.Count
    'nx5 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(5).PivotItems.Count
    'nx6 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(6).PivotItems.Count
    
    
    Application.ScreenUpdating = False
    
    For ii = 1 To nx1
        ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems(ii).Visible = True
        For i = 1 To nx1
            If i <> ii Then ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems(i).Visible = False
        Next
        For jj = 1 To nx2
            
            
            ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems(jj).Visible = True
            If Erro = -1 Then GoTo Proximo2
            For j = 1 To nx2
                If j <> jj Then ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems(j).Visible = False
            Next
            
            n = ActiveSheet.PivotTables(1).RowRange.Rows.Count               'Número max de dados
            Li = ActiveSheet.PivotTables(1).RowRange.Row + 1                 'Linha Inicial
            Lf = ActiveSheet.PivotTables(1).RowRange.Rows.Count + Li - 2 - 1 'Linha Final
            
            If n > 2 Then
                Evento = CDate(ActiveSheet.Cells(Li, 1).Value2)
                Email = ActiveSheet.Cells(Li, 2).Value2
                Usuário = ActiveSheet.Cells(Li, 3).Value2
                NomePC = ActiveSheet.Cells(Li, 4).Value2
                NomeVírus = ActiveSheet.Cells(Li, 5).Value2
                Caminho = ActiveSheet.Cells(Li, 6).Value2
                
                For k = Li + 1 To Lf
                    NomeVírus = NomeVírus & vbCrLf & ActiveSheet.Cells(k, 5).Value2
                    Caminho = Caminho & vbCrLf & ActiveSheet.Cells(k, 6).Value2
                Next
                Contagem = ActiveSheet.Cells(Lf + 1, 7).Value2
                
                MsgBox Evento & " | " & Email & " | " & Usuário & " | " & NomePC & " | " & Contagem
                MsgBox NomeVírus
                MsgBox Caminho
            End If
    Proximo:
        If Err.Number > 0 Then Erro = -1: Resume Next
    Proximo2:
        Erro = 0
        Next jj
    Next ii
    
    Application.ScreenUpdating = True
    End Sub
    
    

    Planilha: http://1drv.ms/1PCfoPq

    Abraço.


    Filipe Magno

    • Marcado como Resposta Juve AC terça-feira, 26 de maio de 2015 12:51
    sábado, 23 de maio de 2015 02:54

Todas as Respostas

  • Como está organizada sua planilha ? ou seja, como estão dispostas as informações ?
    quinta-feira, 14 de maio de 2015 18:55
  • As informações estão dispostas como:

    Data do evento, e-mail, usuário, setor, nome computador, nome vírus, caminho do vírus.

    Exemplo:

       Evento             email            usuário      Nome PC     nome vírus       Caminho

    05/12/2015         teste             João            pc001        Trojan                C:/      

    05/12/2015         teste             João            pc001         Malware                D:/      

    Preciso juntar os valores em outra planilha assim:

       Evento             email            usuário      Nome PC     nome vírus              Caminho      Nº Eventos

    05/12/2015         teste             João            pc001        Trojan, Malware       C:/  D:/              2

                                                                                            

    • Editado Juve AC quinta-feira, 14 de maio de 2015 19:24
    quinta-feira, 14 de maio de 2015 19:17
  • Tem que agrupar por data (mesmo dia) e usuário ?
    sexta-feira, 15 de maio de 2015 11:05
  • Sim, por data e usuário.
    sexta-feira, 15 de maio de 2015 19:41
  • Juve AC,

    o método mais rápido e eficiente para fazer isso é utilizar uma Tabela Dinâmica. O esforço na criação é quase zero e os resultados são muito flexíveis.

    Abraço.


    Filipe Magno

    sábado, 16 de maio de 2015 15:37
  • A tabela dinâmica faz exatamente o que preciso, só que me cria outro problema. Estando os dados concatenados, envio um e-mail automático para o usuário com o alerta em questão. O e-mail é enviado por um código VBA que verifica a linha atual, se na linha houver a palavra enviado, ele junta os dados da linha no formato texto, acessa e envia o e-mail. O problema com a tabela dinâmica é que ela oculta linhas, não funcionando o formato de envio do e-mail, ainda que o caminho do vírus por ser diferente na maioria das vezes, fica separado em duas linhas e precisa juntar todas as informações em uma linha só, para que meu formato de envio funcione. Agradeço a ajuda, não domino muito VBA, talvez possam me dar uma alternativa para isso.
    terça-feira, 19 de maio de 2015 17:52
  • Olá, boa noite.

    Não entendi a parte: "O problema com a tabela dinâmica é que ela oculta linhas". Poderia explicar melhor?

    Quanto a exibição em várias linhas, num primeiro momento penso que é mais fácil adaptar sua macro para ler todos os dados do que escrever uma para concatenar tudo numa mesma linha. Também há o fato que caso sejam muitas informações para concatenar ficará muito desorganizado. Existe alguma restrição em enviar as informações com mais de uma linha ou é apenas o formato que já está configurado?

    Aguardo.


    Filipe Magno

    quarta-feira, 20 de maio de 2015 01:27
  • Bom dia Filipe,

    O formato da macro é que não está adaptado para tabela dinâmica, por exemplo, na tabela os dados podem ficar expandidos ou recolhidos, quando recolhidos, se eu escrevo a opção "enviado" para enviar o e-mail automático com as informações da linha, ele não pega os valores, pois estão recolhidos. Não existe restrição em enviar mais de uma linha, o problema é que minha macro só verifica a linha da qual escrevo a palavra "enviado", não consegui adaptar ela para isso, não sei se entendeu o funcionamento, por isso segue o código da macro de envio de e-mail:

    Sub Worksheet_Change(ByVal Target As Range)

        Dim oMensagem As Object
        Dim oConfiguração As Object
        Dim texto As String
        Dim vFields As Variant
        'Dim linha As Variant

        Set oMensagem = CreateObject("CDO.Message")
        Set oConfiguração = CreateObject("CDO.Configuration")

            oConfiguração.Load -1 'Padrões CDO
            Set vFields = oConfiguração.Fields
            With vFields
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                
                'Se quiser enviar um e-mail pelo Windows Live:
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "servidor smtp"

                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
                
                'Se seu servudor solicita uma conexão protegida (SSL)
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = "true"
                
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "e-mail"
                .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
                .Update
                
            End With
            
        
        linha = ActiveCell.Row - 1
        If Target.Address = "$K$" & linha Then

            If Plan3.Cells(linha, 11) = "Enviado" Then
                texto = "Prezado(a) " & Plan3.Cells(linha, 4) & "," & vbCrLf & vbCrLf & _
                        " Gostaria de informar que houve um alerta de vírus conforme a descrição abaixo:" & vbCrLf & vbCrLf & _
                        "    Usuário: " & Plan3.Cells(linha, 4) & vbCrLf & _
                        "    Data do Evento: " & Plan3.Cells(linha, 2) & vbCrLf & _
                        "    Número de Alertas: " & Plan3.Cells(linha, 10) & vbCrLf & _
                        "    Nome do Computador: " & Plan3.Cells(linha, 6) & vbCrLf & _
                        "    Ação Antivírus: " & Plan3.Cells(linha, 7) & vbCrLf & _
                        "    Nome da(s) Ameaça(s): " & Plan3.Cells(linha, 8) & vbNewLine & _
                        "    Caminho da(s) Ameaça(s): " & Plan3.Cells(linha, 9) & vbCrLf & vbCrLf & _
                        "Pedimos a sua atenção para acessos a dispositivos móveis, acessos a sites da internet, e-mails suspeitos, downloads, que possam vir a trazer alertas ou até mesmo incidências de vírus. Uma incidência de vírus pode causar muitos danos, pois o vírus pode se espalhar pela rede e infectar os demais computadores ou até danos mais sérios." & vbCrLf & vbNewLine & _
                        "Atenciosamente," & vbCrLf & _
                        "Help Desk"

                
        With oMensagem
        
            Set .Configuration = oConfiguração
            .To = Plan4.Cells(linha, 4)
            '.CC = Plan4.Cells(linha, 4)
            '.BCC = "email"
            .From = """Help Desk"" <e-mail>"
            .Subject = "Alerta de Vírus"
            .TextBody = texto
            .Send   'Utilize Send para enviar o email sem abrir o Outlook
            
            End With
            On Error GoTo 0
            
             Set oConfiguração = Nothing
             Set oMensagem = Nothing
             
        End If
        End If
        
    End Sub


    • Editado Juve AC quarta-feira, 20 de maio de 2015 13:30
    quarta-feira, 20 de maio de 2015 13:29
  • Olá, boa noite.

    Desculpe a demora, mas essa semana foi corrida.

    Não tive muito tempo para pensar no assunto, mas fiz uma alternativa para utilizar a Tabela Dinâmica para coletar os dados. A ideia, apesar de não ser eficiente, é coletar os dados selecionando os filtros na tabela. Não gosto muito da sua proposta de enviar email automático quando selecionada dada célula (isso pode gerar um grande transtorno), por isso coloquei um botão. Também não mesclei com o envio de email, isso deixo pra vc. Também deixei pra vc criar um flag que indique que o email já foi enviado (pode ser um campo de página da tabela, buscando a informação na sua tabela fonte).

    Coloquei a saída em MsgBox apenas para uma visualização simplista (isso não tem sentido no código). Use as informações diretamente para envio das informações.

    Segue o código e a planilha que usei de exemplo:

    Sub TesteDados()
    
    Erro = 0
    On Error GoTo Proximo
    ActiveSheet.PivotTables("Tabela dinâmica1").ClearAllFilters
    
    nx1 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems.Count   'Item 1: Eventos
    nx2 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems.Count   'Item 2: Email
    'nx3 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(3).PivotItems.Count
    'nx4 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(4).PivotItems.Count
    'nx5 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(5).PivotItems.Count
    'nx6 = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(6).PivotItems.Count
    
    
    Application.ScreenUpdating = False
    
    For ii = 1 To nx1
        ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems(ii).Visible = True
        For i = 1 To nx1
            If i <> ii Then ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(1).PivotItems(i).Visible = False
        Next
        For jj = 1 To nx2
            
            
            ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems(jj).Visible = True
            If Erro = -1 Then GoTo Proximo2
            For j = 1 To nx2
                If j <> jj Then ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields(2).PivotItems(j).Visible = False
            Next
            
            n = ActiveSheet.PivotTables(1).RowRange.Rows.Count               'Número max de dados
            Li = ActiveSheet.PivotTables(1).RowRange.Row + 1                 'Linha Inicial
            Lf = ActiveSheet.PivotTables(1).RowRange.Rows.Count + Li - 2 - 1 'Linha Final
            
            If n > 2 Then
                Evento = CDate(ActiveSheet.Cells(Li, 1).Value2)
                Email = ActiveSheet.Cells(Li, 2).Value2
                Usuário = ActiveSheet.Cells(Li, 3).Value2
                NomePC = ActiveSheet.Cells(Li, 4).Value2
                NomeVírus = ActiveSheet.Cells(Li, 5).Value2
                Caminho = ActiveSheet.Cells(Li, 6).Value2
                
                For k = Li + 1 To Lf
                    NomeVírus = NomeVírus & vbCrLf & ActiveSheet.Cells(k, 5).Value2
                    Caminho = Caminho & vbCrLf & ActiveSheet.Cells(k, 6).Value2
                Next
                Contagem = ActiveSheet.Cells(Lf + 1, 7).Value2
                
                MsgBox Evento & " | " & Email & " | " & Usuário & " | " & NomePC & " | " & Contagem
                MsgBox NomeVírus
                MsgBox Caminho
            End If
    Proximo:
        If Err.Number > 0 Then Erro = -1: Resume Next
    Proximo2:
        Erro = 0
        Next jj
    Next ii
    
    Application.ScreenUpdating = True
    End Sub
    
    

    Planilha: http://1drv.ms/1PCfoPq

    Abraço.


    Filipe Magno

    • Marcado como Resposta Juve AC terça-feira, 26 de maio de 2015 12:51
    sábado, 23 de maio de 2015 02:54