Macro que faça resumo das abas/guias

andrefebronio · January 27, 2019, 10:28pm

Prezados,

Tenho uma planilha em que cada aba/guia recebe o nome de um cliente e nelas está escrito cada historico de contato com eles. Na célula B7 de cada aba mostra a data do ultimo contato.

Preciso criar uma rotina que retorne na primeira aba (RESUMO), na coluna A, uma lista com o nome de cada aba existentes na planilha, com um hiperlink para a cada uma listada, e na coluna B a data do ultimo contato (célua B7 da respectiva aba listada na coluna A). Essa rotina deve rodar cada vez que eu clicar na aba RESUMO de forma a atualizar a lista de abas(clientes) existentes e a data do ultimo contato constante e cada aba.

Verifiquei que o navegador do Calc faz algo parecido, no entanto não soube como colocá-lo em uma aba/guia, além de fazê-lo mostrar informações especificas das abas.

Caso possível, solicito ajuda na criação.

schiavinatto · January 28, 2019, 2:03am

Ola @andrefebronio, seja bem vindo ao Grupo.

Considerando linha 2:

Se digitar o Nome da aba na coluna A, a fórmula na coluna B pode ser assim:

=INDIRETO(A2&".B7")

Também é possível, Primeiro listar o name da ABA no RESUMO e depois criar a aba com este nome, veja este: Como faço para nomear uma planilha com os dados de uma célula?

Complemento

A Macro não ficou bonita, mas esta funcionando:

A primeira é do Livro “Macros para o OpenOffice.org - Calc” as demais fiz com o gravador e cortei as gorduras.

'================================================|
Sub ListarPlanilhas
'================================================|
	Call IrPara "RESUMO.A1"
	Call LimparResumo
	Dim oPlan
	Dim nome As String
	Dim cont As Integer
		oPlan = ThisComponent.Sheets
			For cont = 0 To oPlan.getCount() - 1
				nome = nome & oPlan(cont).Name & Chr(10)
				Var1 = oPlan(cont).Name 
	Call Digitar Var1
			Next
	Call PreencherRESUMO	
End Sub



'================================================|
sub Digitar (xmensagem as string)
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = xmensagem
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
end sub

'================================================|
sub IrPara (xlocal as string)
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = xlocal
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
end sub

'================================================|
sub PreencherRESUMO
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
	Call Digitar "=INDIRETO(A2&"".""&""B7"")"
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfDataSel", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
	Call IrPara "$A$1"
end sub

'================================================|
sub LimparRESUMO
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
dispatcher.executeDispatch(document, ".uno:GoRightSel", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfDataSel", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())
	Call IrPara "A1"
end sub

Arquivo do teste.

Quando acrescenta ou deleta planilhas e volta para a RESUMO já esta atualizada…

andrefebronio · February 26, 2019, 12:21am

Olá,
Agradeço a dica.
Encontrei um modelo que me atende com linguagem para o excel. Como sou muito leigo em programar, não consegui converter para o Calc:

Private Sub Worksheet_Activate()
Dim ws As Worksheet, i As Integer
Application.ScreenUpdating = False
Sheets("RESUMO").Range("A:A").ClearContents
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "RESUMO" Then
i = i + 1
With Sheets("RESUMO")
.Range("A" & i) = ws.Name
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

.

Essa rotina é incluída no “código fonte” da guia/aba RESUMO. Ela altera a coluna A da guia, de modo que toda vez que a abro, o excel percorre todo o documento. Ele chama o conjunto das guias existentes no documento de WS e conforme vou incluindo/excluido as guias ele conta e acrescenta/retira uma dessa lista, retornando o nome das guias.

É possível converter?

andrefebronio · March 13, 2019, 12:57am

Prezado @Gilberto,

Realmente funciona muito bem e é exatamente o que precisava. Desde já estou muito grato pela ajuda.
No entanto a macro dá erro quando a planilha possui apenas duas guias. Caberia algum ajuste na sintaxe?

De forma a incrementar mais a planilha, podes me ajudar a…?:

acrescentar mais informações nas outras colunas a exemplo da coluna B que traz a celula B7;
colocar um hiperlink no nome da planilha listada para que ao clicar em tal nome já se possa ir direto para a guia referente ao nome;
retirar a seleção da célula B2 ao término da macro;
realizar ajustes na formatação, tais como cor da célula ou negrito no texto.

Att.

schiavinatto · March 13, 2019, 2:42am

Ola @andrefebronio, segue alteração, erro corrigido.

Quanto a formatação se for só negrito é só formatar as colunas, quanto a cor da célula, usei Formatação condicional, por exemplo: A formula é: A1<>"" , formatei estilo God,

(explicando se célula diferente de branco o fundo é God = Fundo verde, letra verde e negrito )

Falta o Hiperlink, vou verificar com mais tempo amanhã, ok.

Arquivo corrigido.

Caso a resposta atendeu sua necessidade, por gentileza, click na bolinha a esquerda da resposta, lá em cima, para finalizar a pergunta.

andrefebronio · March 13, 2019, 12:17pm

Olá Gilberto,

Realmente o problema foi resolvido.

Fico no aguardo do hyperlink e do acréscimo do outro “indireto” pra coluna C e pras outras colunas, caso queira trazer mais alguma informação das planilhas.

Att.

schiavinatto · March 13, 2019, 12:20pm

Ola @andrefebronio, com hiperlink

Arquivo com hiperlink

Quanto a mais colunas, quais seria as células de referencia.

andrefebronio · March 13, 2019, 6:16pm

Olá Gilberto.

Poderia ser a B10 ou C10. Pra você me ensinar como incluir mais referências de acordo com a informação q eu quiser puxar das outras planilhas.

Tentei duplicar o “call” do indireto mas dá erro.

schiavinatto · March 13, 2019, 6:36pm

@andrefebronio, vou montar uma maneira de se acrescentar novas colunas de uma maneira mais pratica, ser ter que mexer na macro.

Ou seja vou mexer ma macro para não mexer na macro, estranho né…

Quantos campos devo prever ( entre em contado direto gilberto@schiavinatto.com e/ou gilbertoschiavinatto@yahoo.com.br ),

Conras · January 28, 2019, 10:44am

Olá @andrefebronio,

Segue um modelo que pode ser utilizado.
Neste modelo não utilizei macros. Possuo uma planilha em Excel de um cliente, onde eu trabalho exatamente da forma que você precisa, e ainda, organiza em ordem alfabética os nomes e as abas, porém, este arquivo possuí hoje mais de 300 contatos, e quando a planilha executa as classificações atualizações, ela demora cerca de 45 minutos. Sabemos que a Linguagem VBA do Excel é levemente mais rápida que a do LibreOffice, mas mesmo assim, o processo é extremamente lento.

Portanto, sugiro a utilização sem as macros para atualização.

Seguem modelos:

1 - Arquivo|attachment com fórmulas para atualização do último contato. Deve ser colocado o nome do contato e depois criar a aba com o mesmo nome.

2 - Arquivo com fórmulas para atualização do último contato, e também com a macro que pega o nome e a posição (linha) da célula e cria uma aba com o mesmo nome.

andrefebronio · February 26, 2019, 12:19am

Olá,
Agradeço as dicas, no entanto não serão tantos processos a listar nessa planilha e assim poderei trabalhar com a macro ao invés das formulas.
Encontrei um modelo que me atende muito bem, porém a linguagem é para o excel. Como sou muito leigo em programar, não consegui converter para o Calc:

Private Sub Worksheet_Activate()
Dim ws As Worksheet, i As Integer
Application.ScreenUpdating = False
Sheets("RESUMO").Range("A:A").ClearContents
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "RESUMO" Then
i = i + 1
With Sheets("RESUMO")
.Range("A" & i) = ws.Name
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

Essa rotina é incluída no “código fonte” da guia/aba RESUMO. Ela altera a coluna A da guia, de modo que toda vez que a abro, o excel percorre todo o documento. Ele chama o conjunto das guias existentes no documento de WS e conforme vou incluindo/excluido as guias ele conta e acrescenta/retira uma dessa lista, retornando o nome das guias.

É possível converter?

andrefebronio · February 26, 2019, 12:22am

Se conseguisse resolveria o problema.