Usuário com melhor resposta
Excel VBA

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?
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
Todas as Respostas
-
-
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
-
-
-
-
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.
-
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
-
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
-
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