Há várias maneiras de fazer o Excel atualizar automaticamente as conexões de dados e recalcular uma planilha. Esses truques são mais importantes com o tipo de dados Estoque no Excel para clientes Microsoft 365.
Normalmente o Excel se atualiza quando você muda um valor de célula. Hoje em dia, há situações em que as células mudam o valor, mas o Excel NÃO atualiza a planilha. Em outras palavras, o Excel moderno mudou de maneiras que a Microsoft ainda não se adaptou totalmente.
O ideal é, O Excel teria uma configuração geral para atualizar a planilha a cada n segundos ou minutos. Do jeito que está, precisamos de soluções alternativas para que isso aconteça.
Aqui estão algumas situações em que forçar a atualização ou recálculo de dados pode ser necessário ou prudente.
Tipo de dados de estoque
Com o tipo de dados de estoque, a capacidade de atualização automática é mais importante. Os usuários vão querer que suas planilhas obtenham o lat Est preços automaticamente, algo que os lançamentos de pré-visualização atuais não podem fazer. Em vez de ter um bom “ticker” automático, devemos clicar em “Atualizar” para obter os preços mais recentes.
Os tipos de dados Stock e Geo são feras curiosas. Eles são conexões de dados com fontes externas, mas NÃO aparecem como Conexões de Dados do Excel. Isso significa que você não pode configurar uma atualização automática de dados, como faria com conexões de dados normais. Na verdade, não há controles expostos para os tipos de dados Stock ou Geo.
NOW () e outras funções voláteis
A função NOW () é atualizada para a última data e hora sempre que o Excel recalcula a planilha. Mas se não há nada para fazer isso acontecer, Now () não muda o valor. Algum fator externo é necessário para fazer o Excel atualizar Now () e o resto da planilha. Em outras palavras, você deve ser capaz de dar uma olhada em uma planilha e saber que está até o segundo, mas isso não é possível com o Excel pronto para uso.
As funções personalizadas do VBA também podem ser marcadas como voláteis usando esta linha no código da função:
Application.Volatile
Essa linha faz com que a função seja executada sempre que o Excel atualizar / recalcular a planilha.
Cuidado extra
Talvez você queira que sua planilha seja atualizada automaticamente por precaução? Muitos veteranos do Excel se lembram de situações em que o Excel não foi atualizado adequadamente, então eles gostam da abordagem de cinto e suspensórios (pelo menos ocasionalmente).
Código
O método padrão de forçar automático atualização do Excel é um pequeno trecho de código VBA. Aqui está o que usamos, existem muitas variações do mesmo tema. O código completo está no final do artigo.
Existem três funções.
RefreshAllDataConn
faz a atualização real das conexões de dados (Workbooks (ThisWorkbook.Name) .RefreshAll) e adicionamos duas linhas opcionais para exibir a última atualização na barra de status inferior.
Se você quiser ser extremamente cuidadoso, adicione uma linha para forçar explicitamente o recálculo. ActiveSheet.Calculate ou o extremo Application.CalculateFull (isso tornaria uma planilha grande, use com moderação).
AutoRefresh
execute o sub RefreshAllDataConn a cada minuto ou qualquer valor que você definir a linha Application.OnTime Now + TimeValue (“00:01:00”), “AutoRefresh”
Workbook_Open
uma função embutida do Excel que é executada automaticamente quando a planilha é aberta . Nesse caso, ele inicia o AutoRefresh.
Solução alternativa para conexão de dados
A desvantagem da abordagem VBA é que uma planilha .xlsm é necessária (planilha Excel habilitada para macro). Pode haver problemas para compartilhar arquivos habilitados para macro devido a questões de segurança.
A chegada do PowerQuery / Get e Transform significa que há outra maneira de forçar o recálculo de uma planilha. É uma solução alternativa e não perfeita, mas é possível e não precisa de uma planilha habilitada para macro.
Em suma, certifique-se de que haja uma configuração de consulta de dados com atualização automática. Se não houver uma conexão de dados, adicione uma pequena à planilha.
Depois de fazer uma consulta de atualização automática, a planilha incluindo todas as funções voláteis também deve ser atualizada.
O ideal é que os tipos de dados Stock e Geo também sejam atualizados.
Qualquer consulta de dados do Excel vem com algumas opções de atualização nas propriedades da consulta. A maioria deles está DESATIVADO por padrão.
Atualizar a cada nnn minutos – o padrão é 60 minutos sugeridos.
Atualizar dados ao abrir o arquivo
Habilitar atualização em segundo plano
Atualizar esta conexão em Atualizar tudo
A solução de atualização automática é criar um pequeno e praticamente insignificante conexão de dados. Em seguida, configure essa conexão de dados para atualizar a cada minuto ou a qualquer hora que desejar. Isso deve forçar a atualização da planilha, incluindo as funções voláteis mencionadas acima.
Algumas versões desta solução alternativa adicionam um link para um pequeno arquivo csv no mesmo computador. Obtivemos o mesmo resultado usando uma conexão de dados de uma tabela na planilha.
Crie uma pequena tabela com uma única célula. A célula pode ter qualquer coisa, mas criamos uma célula com NOW () nela, por motivos que explicaremos mais tarde.
Selecione a tabela e escolha Dados | Obter dados | De outras fontes | da Tabela / Faixa. O item de menu exato pode ser diferente dependendo da sua versão do Excel.
Quando o Editor de consultas abrir, basta fechar e carregá-lo . No painel de conexões de dados, você verá uma consulta.
Clique com o botão direito na consulta, escolha as propriedades para ver o configurações de que precisamos.
Defina a taxa de atualização adequada para você.
Para ser organizado, nós mova a tabela de origem (direita) para a mesma planilha na consulta carregada (esquerda). Como usamos NOW () na célula da tabela de origem, será fácil ver quando / se a planilha foi atualizada.
Exemplo de código VBA
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Crie uma lista automática de planilhas do Excel ou índice analítico
Solução completa Excel NetworkDays () com feriados & férias