Excel Executar Macro ao receber novos dados externos (conexão)

[mapires]

Amigos, estou precisando de ajuda. Sou iniciante e não entendo muito de vba, mas tenho pesquisado.

 

Seguinte... Tenho uma Planilha que recebe dados externos de uma conexão com um Formulário do Google Forms. Cada formulário preenchido chega na planilha como uma nova linha nessa tabela de importação.

 

O lance é que a cada linha recebida eu preciso executar uma macro (que realiza alguns procedimentos e exporta um dado por email). A parte de importação dos dados está ok. A parte de calculos e exportar dados também. O que não estou conseguindo fazer é acionar essa macro de calculo e exportação a partir da entrada de novos dados na planilha específica.

 

Segue abaixo o que tentei:

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim UltimaLinha As Long
 

UltimaLinhaX = Cells(Rows.Count, "B").End(xlUp).Row

If Intersect(Target, Range("B" & UltimaLinha & ":" & "B1000")) Is Nothing Then

Exit Sub

Else

Call Macro1

End If

End Sub
 

 

O problema é que a UltimaLinha ainda faz parte da tabela ne, então o excel, quando faz o refresh dos dados, entende que houve alteração ainda que não tenha havido e roda a Macro1 indevidamente. Se eu colocar um "+ 1" na variavel UltimaLinha pra ele contar a linha logo abaixo da tabela, assim que a tabela é atualizada e recebe novos dados, a Macro1 não roda, porque ele "empurra" a referencia para a linha abaixo dos novos dados importados..... 

 

Alguma luz?

 

 

 

 

 

 

 

 

 

[Visitante]

4 horas atrás, mapires disse:

 

Cada formulário preenchido chega na planilha como uma nova linha nessa tabela de importação.

Em qual linha da planilha os dados são inseridos?

 

O que não estou conseguindo fazer é acionar essa macro de calculo e exportação a partir da entrada de novos dados na planilha específica.

Cole uma cópia do código abaixo no lugar do código atual e veja se a Caixa de Mensagem é exibida ao receber uma nova linha de dados.

Private Sub Worksheet_Change(ByVal Target As Range)
 msgbox "olá"
End Sub

Se não exibir a Caixa então experimente o código abaixo.

Private Sub Worksheet_Calculate()
 MsgBox "olá"
End Sub

 

Dim UltimaLinha As Long
UltimaLinhaX = ... <~~~ esse "X" realmente existe no seu código ?

 

O problema é que a UltimaLinha ainda faz parte da tabela ne, então o excel, quando faz o refresh dos dados, entende que houve alteração ainda que não tenha havido e roda a Macro1 indevidamente.

Este "problema" se refere ao problema apontado antes ou se refere a um "outro problema" ?

O que exatamente você chama de "refresh" ?

 

If Intersect(Target, Range("B" & UltimaLinha & ":" & "B1000")) Is Nothing Then <~~~ qual a missão deste comando?

 

 

[mapires]

Boa Tarde, @@osvaldomp 

 

Primeiramente muito obrigado pela ajuda!!

 

Vamos lá.. Primeira coisa, quando eu disse "Refresh", é a conexão de dados que se "atualiza" a cada 1 minuto, por confirguração. Depois até gostaria de ver alguma linha de comando para atualizar com menos tempo (15 ou 30 segundos).

 

Bom, como eu disse essa atualização é uma configuração da própria tabela importada do google form... mas o comando disso é algo como:

Citação

ActiveWorkbook.Connections("Consulta - Table 0 (2)").Refresh

 

Continuando... o 1o código q você mandou,

Citação

 

Private Sub Worksheet_Change(ByVal Target As Range)

msgbox "olá"

End Sub

 

 

Executa a msgbox tendo dados novos ou não, então não adianta..

 

O 2o código q você mandou

Citação

 

Private Sub Worksheet_Calculate()

MsgBox "olá"

End Sub

 

 

Não executa a MsgBox nem com nem sem dados novos

 

Quanto ao UltimaLinhaX, esse é o nome mesm da variável que coloquei... la em cima na declaração eu havia esquecido o X quando mandei aqui, mas corrigi e nao resolveu..

 

Já o comando 

 

Citação

If Intersect(Target, Range("B" & UltimaLinha & ":" & "B1000")) Is Nothing Then

 

serve para localizar a "atual" ultima linha de dados na planilha, pois justamente o 1o código que você mandou so daria certo se o excel verificasse dados novos apenas abaixo dessa linha. Porque isso? O Excel, ao atualizar os dados (a cada 1 minuto), ele entende que houve alteracao nos dados importados tendo novo dado ou não e acaba por rodar a macro mesmo sem dados novos. Me fiz entender??  

 

Obrigado mais uma vez,

 

Abraço,

 

Marcus.

 

[mapires]

3 horas atrás, osvaldomp disse:

 

Só esqueci de responder uma pergunta sua, me desculpe.. A minha tabela hoje ocupa da linha B1:T1(cabeçalho) até a linha B29:T29 (ou seja, já tenho 28 linhas de dados). Quando novos dados do Google Form forem recebidos, a cada formulário enviado, uma nova linha desses planilha sera preenchida.. 

 

Talvez eu esteja olhando a logica do que deve ser feito de forma errada.

 

Pensando de forma mais clara e direta, uma programação que monitore (a partir de agora) a celula B30 por exemplo e ative a macro de cálculos assim que ela for preenchida, e em seguida comece a monitorar a celula B31 e novamente acione a macro assim que esta mude de valor e assim sucessivamente com a B32... B33... B34... eternamente, pra mim resolveria... Um dado importante, cada formulario do Google Form preenche apenas uma linha e dados da coluna B nunca vêem em branco, outras colunas podem vir sem dados, mas a coluna B não. 

 

 

 

 

[Visitante]

 

6 horas atrás, mapires disse:

Olá, Marcus.

Ainda tentando entender. Confirme, por favor.

 

 ...é a conexão de dados que se "atualiza" a cada 1 minuto, por confirguração.

A atualização não depende de macro, é feita automaticamente decorrente de configuração no Excel.

 

Continuando... o 1o código q você mandou, Executa a msgbox tendo dados novos ou não, então não adianta..

post #1 ~~~> "quando faz o refresh dos dados, entende que houve alteração ainda que não tenha havido"

post # 3 ~~~> "ao atualizar os dados (a cada 1 minuto), ele entende que houve alteracao nos dados importados tendo novo dado ou não e acaba por rodar a macro mesmo sem dados novos."

Com base nos seus comentários acima tive a impressão de que a atualização pode inserir dados na planilha ou pode não inserir dados, e neste segundo caso, que equivaleria a "inserir vazios", mesmo assim o evento WS_Change é disparado.

post #4 ~~~> "e dados da coluna B nunca vêem em branco, outras colunas podem vir sem dados, mas a coluna B não"

No entanto esse seu último comentário diz que nunca ocorre a inserção de "vazios". Aí restou a dúvida do que realmente ocorre. Veja comentário ao final.

 

Quanto ao UltimaLinhaX, ...mas corrigi e nao resolveu..

Faltou eu comentar que o "X" não tem relação com os problemas que você relatou. Liguei o alerta pois da forma que está publicado, ao ser executado, o código iria travar no comando seguinte pois a variável estaria com valor zero.

 

 

Por favor faça novos testes com o código abaixo.

A Caixa de Mensagem irá exibir em qual célula houve alteração e qual o conteúdo inserido.

Se em algumas atualizações a Caixa exibir o endereço de uma célula, ou de um intervalo de células, e não exibir o conteúdo inserido, ou seja, exibir vazio na segunda linha, então você poderá monitorar a atualização com um "If" para só executar a Macro1 se for inserido conteúdo diferente de vazio na coluna B. É uma ideia.

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox Target.Address & vbLf & Target.Value
End Sub

Fiz um teste aqui: enfiei um "jacaré" em B16.

em seguida removi o "jacaré" de B16 (inseri vazio) ~~~>

[mapires]

3 minutos atrás, osvaldomp disse:

A atualização não depende de macro, é feita automaticamente decorrente de configuração no Excel.

Isso. Exatamente, 

 

 

 

3 minutos atrás, osvaldomp disse:

Por favor faça novos testes com o código abaixo.

A Caixa de Mensagem irá exibir em qual célula houve alteração e qual o conteúdo inserido.

Se em algumas atualizações a Caixa exibir o endereço de uma célula, ou de um intervalo de células, e não exibir o conteúdo inserido, ou seja, exibir vazio na segunda linha, então você poderá monitorar a atualização com um "If" para só executar a Macro1 se for inserido conteúdo diferente de vazio. É uma ideia.

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox Target.Address & vbLf & Target.Value
End Sub

Fiz um teste aqui: enfiei um "jacaré" em B16.

 

em seguida removi o "jacaré" de B16 (inseri vazio) ~~~>

 

 

Coloquei o código sugerido, mas obtive o seguinte erro ao fazer a atualização dos dados:

 

Erro em tempo de execução '13'

Tipos incompatíveis

 

Não executou a Msgbox...

 

 

 

 

adicionado 8 minutos depois

Tirei o Target.Value e aí deu certo..

 

Ao atualizar, abriu a Msgbox com  $A$1:$B$29

 

 

[mapires]

Funcionou a msgbox ne.. mas ainda não entendi a rotina que devo fazer para rodar minha macro..

 

 

[Visitante]

36 minutos atrás, mapires disse:

Funcionou a msgbox ne.. mas ainda não entendi a rotina que devo fazer para rodar minha macro..

 

 

Para poder lhe sugerir um caminho eu preciso antes entender com exatidão o que ocorre em cada Atualização, e como eu não consigo reproduzir aqui e não tenho acesso ao seu arquivo, então para entender eu dependo das suas informações.

 

"Ao atualizar, abriu a Msgbox com  $A$1:$B$29"

1. o intervalo A1:B29 estava vazio antes de ocorrer a Atualização ?

2. após ocorrer a Atualização quais células foram preenchidas no intervalo mostrado na Caixa?

3. observe durante 4 ou 5 Atualizações e informe:

   a) intervalo mostrado na Caixa

   b) quais células foram preenchidas no intervalo mostrado na Caixa

4. após ocorrer a Atualização a Macro1 faz alterações no intervalo mostrado na Caixa ? se sim, quais?

5. alguma outra informação que você julgar relevante e que ocorre antes, durante ou após cada Atualização

adicionado:

6. a Caixa é exibida em todas as Atualizações ?

[mapires]

15 minutos atrás, osvaldomp disse:

 

Para poder lhe sugerir um caminho eu preciso antes entender com exatidão o que ocorre em cada Atualização, e como eu não consigo reproduzir aqui e não tenho acesso ao seu arquivo, então para entender eu dependo das suas informações.

 

"Ao atualizar, abriu a Msgbox com  $A$1:$B$29"

1. o intervalo A1:B29 estava vazio antes de ocorrer a Atualização ?

NÃO. Ele tem dados já encaminhados em formularios anteriores. Contudo, a cada atualização, como esta é uma tabela de importação, acredito que o excel por rotina própria, zere tudo e faça a importação novamente. Por isso ele indica que todas as celulas da tabela foram alteradas.

2. após ocorrer a Atualização quais células foram preenchidas no intervalo mostrado na Caixa?

SIM. Mesmo não havendo novos dados enviados, a tabela e preenchida conforme estava antes. Posso fazer um arquivo modelo, mas nao sei como te encaminhar para analise.

3. observe durante 4 ou 5 Atualizações e informe:

   a) intervalo mostrado na Caixa

Não houve novos dados, entao o intervalo é sempre o mesmo. No caso, $A$1:$B$29

   b) quais células foram preenchidas no intervalo mostrado na Caixa

TODAS... Elas já estavam preenchidas antes. Mas como citei anteriormente, acho que na checagem de novos dados na origem da conexão, o que ele faz é apagar tudo e reimportar TODOS os dados, e não só dados novos.

4. após ocorrer a Atualização a Macro1 faz alterações no intervalo mostrado na Caixa ? se sim, quais?

NÃO. A Macro leva os dados para processá-los em outra Planilha. 

5. alguma outra informação que você julgar relevante e que ocorre antes, durante ou após cada Atualização

adicionado:

Acredito que a solução possa ser a seguinte > contar o número de celulas preenchidas na coluna B. Nesse momento tenho 29 células preenchidas, sendo 1 de cabeçalho (celula B1) e todas as outras com dados (B2:B29). O lance seria "monitorar" esse número. Caso suba pra 30, acione a Macro de cálculo. Daí então a referência de monitoramente subiria pra 31. Assim que o número de celulas preenchidas for 31, aciona macro. Então subir a referencia para 32, e assim por diante.  Tem como construir uma rotina assim?

6. a Caixa é exibida em todas as Atualizações ?

SIM.

 

[Visitante]

"Não houve novos dados, entao o intervalo é sempre o mesmo. No caso, $A$1:$B$29"

Acho que agora entendi. De início me pareceu erradamente que "novos dados" seria atualizar os dados sempre no mesmo intervalo de destino, com células vazias, de acordo com alterações ocorridas ou não na origem dos dados, mas com base no sua afirmação acima e com base na sua ideia de monitorar a primeira linha vazia abaixo da tabela, (só agora ... ) entendi que "novos dados" implicam em novas linhas preenchidas no destino.

 

Para monitorar a última linha da tabela vejo duas possibilidades: uma variável global ou uma "marcação" feita pela Macro1 em alguma célula auxiliar.

 

"Posso fazer um arquivo modelo, mas nao sei como te encaminhar para analise."

Seria útil se você puder anexar o seu arquivo. Veja a dica no final da minha postagem.