Excel Captura e Armazena Dados Externos

Alexandre José Costa · 25 de janeiro

Boa noite a todos. Espero que estejam bem.

Eu trago aqui um problema aparentemente simples, mas estou há dias batendo cabeça e não resolvo isso, então peço ajuda. O Excel deve capturar dados de uma lista células que recebem dados externos via RTD. Estes dados devem ser capturados e armazenados nas colunas à frente, na mesma aba. Criei uma planilha para publicar aqui e segue anexa. Seguem os detalhes:

Na aba "CapturaDados", o VBA deve capturar as alterações que acontecem nos ranges C4:C13 e D17:D26. Para identificar as mudanças, o VBA deve verificar se a alteração de cada linha é igual ao valor da cél da col à esquerda. Em caso positivo, o VBA deve capturar o valor e armazená-lo das respectivas linhas, nas colunas subsequentes vazias, a partir da coluna F. O VBA deve fazer isso automaticamente, já que os dados mudam constantemente, dados estes fornecidos pelo RTD. O nome da macro deve ser "CapturaArmazenaDados".

Nas céls da Col E, o VBA deve guardar os últimos dados de cada linha armazenada até que sejam alterados novamente. Ou seja, a diferença entre os dados da Col E para as cols à sua frente é que na Col E os dados serão sobrepostos a cada atualização, enquanto que as cols à frente, a partir da Col F, guardam o histórico das alterações do dia.
Toda vez que uma alteração for capturada, a hora a alteração também será capturada e armazenada no topo de cada nova col à frente. (ao ver planilha ficará claro).

Eu não sou programador e não entendo nada de VBA. As dezenas de versões que tentei foram criadas a partir de IA, logo, acredito que o cód VBA atual não ajude.

Desde já agradeço a ajuda.

CapturaArmazenaDados_ClubeDoHD.xlsx

Midori · 25 de janeiro

Neste tópico teve uma questão parecida,

https://www.clubedohardware.com.br/forums/topic/1606559-excel-copia-e-guarda-x-valores-entregues-pelo-rtd-real-time-data/

A mesma macro pode servir. No Calculate tenta editar o loop para o range dessa planilha.

Alexandre José Costa · 25 de janeiro

1 hora atrás, Midori disse:

Neste tópico teve uma questão parecida,

https://www.clubedohardware.com.br/forums/topic/1606559-excel-copia-e-guarda-x-valores-entregues-pelo-rtd-real-time-data/

A mesma macro pode servir. No Calculate tenta editar o loop para o range dessa planilha.

Olá Midori, bom dia.
É um prazer estar com você novamente.
Vou consultar o link.
Tks.

Alexandre José Costa · 25 de janeiro

@Midori

Eu consultei o link. Ele foi resultado de uma dúvida que eu mesmo postei e você acabou resolvendo aqui. De fato são problemas parecidos. Eu fui colocar o cód. na minha planilha e o Excel bloqueou a execução de macros.

Para desbloquear, eu segui as instruções da MS (abaixo), mas a caixa "desbloquear" não aparece para mim.

Desbloquear um único arquivo

Na maioria dos casos, você pode desbloquear as macros modificando as propriedades do arquivo da seguinte maneira:

Abra o Explorador de Arquivos do Windows e acesse a pasta onde você salvou o arquivo.
Clique com o botão direito do mouse no arquivo e escolha Propriedades no menu de contexto.
Na parte inferior da guia Geral , marque a caixa de seleção Desbloquear e selecione OK.

Fonte: https://support.microsoft.com/pt-br/topic/uma-macro-potencialmente-perigosa-foi-bloqueada-0952faa0-37e7-4316-b61d-5b5ed6024216

Midori, apesar dos problemas serem parecidos, agora as regras são outras. Acredito que mais simples que de outrora.

Midori · 25 de janeiro

Salvou o arquivo como xlsm?

Caso necessário, as configurações de segurança ficam no excel em opções > central de confiabilidade.

Alexandre José Costa · 25 de janeiro

15 minutos atrás, Midori disse:

Salvou o arquivo como xlsm?

Caso necessário, as configurações de segurança ficam no excel em opções > central de confiabilidade.

@Midori, salvei agora como xlsm. Mas preciso sua ajuda com o VBA.
O VBA do outro tópico travava minha plan e você fez uma alteração final que resolveu naquela oportunidade. Mas esta alteração chama uma macro que não existe agora.
Não sei como lidar com isso.

Midori · 25 de janeiro

1 hora atrás, Alexandre José Costa disse:

Mas esta alteração chama uma macro que não existe agora

Qual macro não existe?

Copiou o procedimento Atualiza e colocou o Calculate na planilha?

Alexandre José Costa · 25 de janeiro

2 horas atrás, Midori disse:

Qual macro não existe?

Copiou o procedimento Atualiza e colocou o Calculate na planilha?

Não, eu só tinha copiado o Atualiza. Vou fazer o que você falou. Se der certo eu altero o range.

@Midori, copiei o procedimento "Atualiza" e o "Calculate". Abaixo segue o print, pois, não sei se a ordem deles está correta:

Estamos evoluindo.

Como pode ver no print, ele está pedindo para nomear a macro (não sei o que fazer);
As regras deste projeto, como descritas na abertura do tópico são um pouco diferentes do tópico que estamos usando como referência:
1. Se olhar na plan modelo, a col D tem dois ranges: C4:C13 e D17:D26;
2. Na plan modelo, o armazenamento do histórico se inicia na col F (duas à frente da D);
3. Neste projeto, os valores de cada linhas são sempre os mesmos, com isso, o VBA não enxerga os valores mudando de C para C, de C+ para C+, etc. Talvez ajude: na descrição das regras, que eu propus na abertura o tópico, falo para os ranges da Col D "olharem" para a Col C para saber se o novo valor da Col D é igual ao da Col C à esquerda;
4. Toda vez que uma alteração for capturada, a hora da alteração também será capturada e armazenada no topo de cada nova col à frente. (ao ver planilha modelo ficará claro);
5. Neste projeto, os valores do "histórico" são contínuos, enquanto que no tópico anterior (que estamos tomando como referência), guardavam apenas os últimos 5 valores;

Pensando em trazer as coisas mais mastigadas para você, o que consigo ver, por hora, é isso. Parece que o cód de referência vai funcionar.

Desde já muito agradecido.

Alexandre José Costa · 25 de janeiro

1 hora atrás, Alexandre José Costa disse:

Na plan modelo, o armazenamento do histórico se inicia na col F (duas à frente da D);

Revendo aqui, o cód de referência, vai atender.

1 hora atrás, Alexandre José Costa disse:

Neste projeto, os valores do "histórico" são contínuos, enquanto que no tópico anterior (que estamos tomando como referência), guardavam apenas os últimos 5 valores;

Já vi onde mudar isso no cód de referência.

DE: Call Atualiza(Valor, 5)

PARA: Call Atualiza(Valor, 16380)

É isso mesmo?

Midori · 26 de janeiro

17 horas atrás, Alexandre José Costa disse:

Como pode ver no print, ele está pedindo para nomear a macro (não sei o que fazer);

Isso pode acontecer se tentar rodar Atualiza manualmente (com F5, p.ex). Esse procedimento só deve ser chamado por outro que nesse caso é o Calculate.

17 horas atrás, Alexandre José Costa disse:

Se olhar na plan modelo, a col D tem dois ranges: C4:C13 e D17:D26;

Tenta resolver só o primeiro range e se der certo aplique para outro.

16 horas atrás, Alexandre José Costa disse:

Já vi onde mudar isso no cód de referência.

DE: Call Atualiza(Valor, 5)

PARA: Call Atualiza(Valor, 16380)

O segundo parâmetro é a quantidade de colunas usadas para salvar os dados.

Assim vai até a coluna 16380 e é quase a quantidade limite de colunas da atual versão do Excel.

Se quiser guardar o histórico sem depender desse limite, é melhor fazer por linhas e não colunas.

Alexandre José Costa · 26 de janeiro

35 minutos atrás, Midori disse:

Isso pode acontecer se tentar rodar Atualiza manualmente (com F5, p.ex). Esse procedimento só deve ser chamado por outro que nesse caso é o Calculate.

Tenta resolver só o primeiro range e se der certo aplique para outro.

O segundo parâmetro é a quantidade de colunas usadas para salvar os dados.

Assim vai até a coluna 16380 e é quase a quantidade limite de colunas da atual versão do Excel.

Se quiser guardar o histórico sem depender desse limite, é melhor fazer por linhas e não colunas.

@Midori, boa tarde e obrigado pelo retorno.

Entendido. Sim, todos estes pontos já foram vistos. Quanto ao range, eu já fiz a alteração e ele está funcionando bem, ele captura e armazena. Agora surgiram outros pontos para se adequarem a este projeto:

1) Este arquivo (Excel) é chamado pelo TRYD, uma plataforma que não aceita o .xlsm. Podemos mudar a extensão de modo que o VBA funcione corretamente?

2) Como o VBA está capturando e armazenando corretamente, você pode, por favor, escrever o VBA com os ranges corretos? A col D tem dois ranges para verificar se ocorrem alterações nos valores. São eles: C4:C13 e D17:D26. Mas as céls de horas (C4 e D17 que estão dentro destes ranges) só devem ser capturadas e armazenadas quando uma alteração for capturada nas outras linhas do range.

3) Toda vez que uma alteração for capturada, a hora da alteração também deverá ser capturada e armazenada no topo de cada nova col. Se observar a planilha modelo (veja o print abaixo), na aba CapturaDados, nós temos dois tipos diferentes de históricos, o CPA e o VDA. As horas, quando capturadas, devem ficar nas linhas 4 e 17 de cada nova col do histórico. Veja o print:

4) quando os valores de cada linha do range sofrem alterações, eles são sempre os mesmos. Se você olhar para a col C do print acima, poderá ver que ela mostra alguns códigos. São estes códigos que vão aparecer no histórico. Estou aqui pensando que isso pode ser um problema, já que o VBA precisa enxergar os valores mudando de C para C, de C+ para C+, de V para V, De V+ para V+, etc, isso pode ser um problema para identificar que algo mudou. Neste ponto, talvez ajude saber que:

a) enquanto o RTD não traz nova alteração as céls mostrarão FALSO;
b) a cada alteração que o RTD traz para a Col D , o VBA pode conferir se o novo valor é igual ao da Col C à esquerda. (a col C não se altera, apenas exibe os códs de cada linha)

A resolução destes 4 pontos devem fechar a questão.
Por favor, você pode, por gentileza, me auxiliar nisso?

Desde já sigo muito agradecido.

Alexandre José Costa · 28 de janeiro

Em 26/01/2024 às 13:32, Alexandre José Costa disse:

1) Este arquivo (Excel) é chamado pelo TRYD, uma plataforma que não aceita o .xlsm. Podemos mudar a extensão de modo que o VBA funcione corretamente?

Olá Midori,

Fiz uma busca para obter a resposta da pergunta acima. Em suma, minha versão do Excel aceita macros e VBA nos arquivos .xls. Com isso temos um item a menos para resolver. Agora restam 3 deles.

Abaxo segue a resposta completa:

Sim, o Microsoft Excel 2019 suporta macros e VBA, e você pode usar a extensão ".xls" para documentos que contenham macros e código VBA. A versão que você mencionou, "Microsoft Excel 2019 MSO (Versão 2312 Build xx.x.xxxxxxxx 32 bits(aqui one aparecem "x" omiti número de minha versão) ", é uma versão do Excel que oferece suporte a macros e VBA.

Ao usar a extensão ".xls", você pode criar documentos que incluam código VBA. Lembre-se de que, ao abrir um arquivo que contém macros, o Excel geralmente solicitará que você habilite a execução de macros por motivos de segurança. Certifique-se de permitir a execução de macros apenas em arquivos de fontes confiáveis.

Se você tiver requisitos específicos para compatibilidade com versões mais antigas do Excel, a extensão ".xls" é apropriada. No entanto, ao considerar as melhores práticas e recursos mais recentes, pode ser apropriado usar o formato ".xlsm" para documentos que contêm macros e VBA, especialmente ao compartilhar arquivos entre diferentes versões do Excel.

Alexandre José Costa · 29 de janeiro

Em 28/01/2024 às 11:30, Alexandre José Costa disse:

Olá Midori,

Fiz uma busca para obter a resposta da pergunta acima. Em suma, minha versão do Excel aceita macros e VBA nos arquivos .xls. Com isso temos um item a menos para resolver. Agora restam 3 deles.

Abaxo segue a resposta completa:

Sim, o Microsoft Excel 2019 suporta macros e VBA, e você pode usar a extensão ".xls" para documentos que contenham macros e código VBA. A versão que você mencionou, "Microsoft Excel 2019 MSO (Versão 2312 Build xx.x.xxxxxxxx 32 bits(aqui one aparecem "x" omiti número de minha versão) ", é uma versão do Excel que oferece suporte a macros e VBA.

Ao usar a extensão ".xls", você pode criar documentos que incluam código VBA. Lembre-se de que, ao abrir um arquivo que contém macros, o Excel geralmente solicitará que você habilite a execução de macros por motivos de segurança. Certifique-se de permitir a execução de macros apenas em arquivos de fontes confiáveis.

Se você tiver requisitos específicos para compatibilidade com versões mais antigas do Excel, a extensão ".xls" é apropriada. No entanto, ao considerar as melhores práticas e recursos mais recentes, pode ser apropriado usar o formato ".xlsm" para documentos que contêm macros e VBA, especialmente ao compartilhar arquivos entre diferentes versões do Excel.

@Midori, boa tarde.
Espero que tenha tido um bom fds.
você acha que consegue seguir com sua ajuda?
Desde já agradeço.

Midori · 29 de janeiro

Não fiz o registro do horário porque isso ainda não ficou muito claro.

A tabela pode ter mais de um True com históricos variados, nesse caso como o horário deve ser registrado?

Sub Eventos()
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Call AtualizaDados(Range("E5:E13,E18:E26"), 10)
    Application.EnableEvents = True
End Sub

Sub AtualizaDados(Dados As Range, Colunas As Integer)
    Dim Ultimo      As Range
    Dim Sequencia   As Range
    Dim Conta       As Integer
    
    For Each Ultimo In Dados
        If Ultimo(, 0).Value = True Then
            Set Sequencia = Ultimo(, 2).Resize(1, Colunas)
            Conta = WorksheetFunction.CountA(Sequencia)
            If Conta <> 0 Then
                Conta = IIf(Conta < Colunas, Conta, Conta - 1)
                Set Sequencia = Ultimo(, 2).Resize(1, Conta)
                Sequencia.Offset(0, 1).Value = Sequencia.Value
            End If
            Ultimo(, 2).Value = Ultimo.Value
        End If
    Next Ultimo
End Sub

Alexandre José Costa · 29 de janeiro

Olá @Midori,

Obrigado pelo retorno.

A col que sofre alterações é a D. Já alterei aqui.
DE: Call AtualizaDados(Range("E5:E13,E18:E26"), 10)
PARA: Call AtualizaDados(Range("D5:D13,D18:D26"), 10)

Eu copiei e colei o último cód que você fez acima. Infelizmente, nada acontece. Se altero um valor qualquer da col D, a plan não armazena a alteração. Será que é porque estou usando uma extensão .xls?

Se isso for um empecilho, eu terei um sério problema, pois, só posso usar .xls e .xlsx.

OBS: durante o fds nenhum outro cód funcionou. Inclusive o antigo que estamos adaptando aqui. Este antigo chegou a armazenar, sabe? Mas até ele parou. Por isso perguntei sobre a extensão .xls.

Sobre o registro dos horários: a ideia é que os horários sejam registrados para mostrar quando houve uma alteração dentro do range. E isso fica no histórico. Onde fica armazenado? Nas linhas 4 e 17 de cada nova col gerada, encabeçando cada um dos ranges.

Midori · 29 de janeiro

17 minutos atrás, Alexandre José Costa disse:

Eu copiei e colei o último cód que você fez acima. Infelizmente, nada acontece.

A atualização só acontece para True. Aqui essa coluna está Boolean. Talvez o seu Excel esteja interpretando como String e se for esse caso coloque True entre aspas lá no código.

Alexandre José Costa · 29 de janeiro

Assim?

Sub Eventos()
    Application.EnableEvents = "True"
End Sub

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Call AtualizaDados(Range("D5:D13,D18:D26"), 10)
    Application.EnableEvents = "True"
End Sub

Sub AtualizaDados(Dados As Range, Colunas As Integer)
    Dim Ultimo      As Range
    Dim Sequencia   As Range
    Dim Conta       As Integer
    
    For Each Ultimo In Dados
        If Ultimo(, 0).Value = True Then
            Set Sequencia = Ultimo(, 2).Resize(1, Colunas)
            Conta = WorksheetFunction.CountA(Sequencia)
            If Conta <> 0 Then
                Conta = IIf(Conta < Colunas, Conta, Conta - 1)
                Set Sequencia = Ultimo(, 2).Resize(1, Conta)
                Sequencia.Offset(0, 1).Value = Sequencia.Value
            End If
            Ultimo(, 2).Value = Ultimo.Value
        End If
    Next Ultimo
End Sub

Se for só isso, não está armazenando. A saber eu já salvei a plan modelo com outras extensões, até .xlsm, mas infelizmente o comportamento é mesmo, altero os valores do range, mas eles não são armazenados.

Midori · 29 de janeiro

É só no teste do procedimento AtualizaDados. As linhas de EnableEvents não devem ser modificadas.

Alexandre José Costa · 29 de janeiro

@Midori,

Fiz assim....

Sub Eventos()
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Call AtualizaDados(Range("D5:D13,D18:D26"), 10)
    Application.EnableEvents = True
End Sub

Sub AtualizaDados(Dados As Range, Colunas As Integer)
    Dim Ultimo      As Range
    Dim Sequencia   As Range
    Dim Conta       As Integer
    
    For Each Ultimo In Dados
        If Ultimo(, 0).Value = "True" Then
            Set Sequencia = Ultimo(, 2).Resize(1, Colunas)
            Conta = WorksheetFunction.CountA(Sequencia)
            If Conta <> 0 Then
                Conta = IIf(Conta < Colunas, Conta, Conta - 1)
                Set Sequencia = Ultimo(, 2).Resize(1, Conta)
                Sequencia.Offset(0, 1).Value = Sequencia.Value
            End If
            Ultimo(, 2).Value = Ultimo.Value
        End If
    Next Ultimo
End Sub

Se fiz certo, a plan não reage ao código.

Midori · 29 de janeiro

Talvez as macros ou os eventos estejam desativados.

Tenta rodar manualmente.

Selecione Calculate e digite F5.

Alexandre José Costa · 29 de janeiro

Fiz isso que está na img e digitei F5. Nada aconteceu.

Alexandre José Costa · 30 de janeiro

Bom dia, @Midori,

Fiz certo o que você me pediu?
A plan parece inerte, não acusa o VBA trabalhando.

Midori · 30 de janeiro

@Alexandre José Costa As macros estão ativadas? Tentou rodar alguma para verificar isso? Pode ser um Msgbox.

A macro vai pegar os dados da coluna E/ULT_Dados. Se não tiver nada lá o histórico vai ficar em branco.

Estes são os resultados dos meus testes,

Coloquei valores aleatórios para simular a atualização.

Alexandre José Costa · 30 de janeiro

Olá @Midori,

Obrigado pelo retorno.
Vou tentar fazer esta verificação e lhe retorno.

Apenas para alinhar sua plan com minha:

1) a col que recebe os dados RTD é a D;
2) a col E deveria apresentar as últimas atualizações de qualquer linha do range;
3) os valores recebidos em cada linha do range (D5:D13 e D18:D26) vindos do RTD são sempre os mesmos, cada linha te o seu valor. Eu falei sobre isso aqui:

Em 26/01/2024 às 13:32, Alexandre José Costa disse:

o VBA precisa enxergar os valores mudando de C para C, de C+ para C+, de V para V, De V+ para V+, etc, isso pode ser um problema para identificar que algo mudou. Neste ponto, talvez ajude saber que:

a) enquanto o RTD não traz nova alteração as céls mostrarão FALSO;
b) a cada alteração que o RTD traz para a Col D , o VBA pode conferir se o novo valor é igual ao da Col C à esquerda. (a col C não se altera, apenas exibe os códs de cada linha)

Penso que isso pode ser visto depois. Só estou me adiantando, porque o dev pode tomar um linha de desenvolvimento que poderia ser evitada se soubesse de alguma informação importante antes.

Vou tentar fazer a verificação que você me orientou e lhe retorno.
Obrigado.

Alexandre José Costa · 5 de fevereiro

@Midori,

Boa tarde.

Espero que tudo esteja bem.

Em 30/01/2024 às 10:32, Midori disse:

As macros estão ativadas? Tentou rodar alguma para verificar isso? Pode ser um Msgbox.

Eu não sei fazer isso. Mas eu mudei as diretrizes aqui e, por hora, eu vou abortar o funcionamento desta macro. Mas eu sei que uma hora ou outra vou voltar na mesma questão de armazenamento de valores.

Escrevo para agradecer sua boa atenção de sempre.
Seu conhecimento para os iniciantes aqui é de muito valor.
Muito obrigado.