Executando uma macro na hora

Um caso muito comum na prática: você precisa executar uma ou mais de suas macros em um determinado momento ou em uma determinada frequência. Por exemplo, você tem um relatório grande e pesado que é atualizado meia hora e gostaria de executar a atualização meia hora antes de chegar ao trabalho pela manhã. Ou você tem uma macro que deve enviar e-mails automaticamente para os funcionários em uma frequência especificada. Ou, ao trabalhar com uma tabela dinâmica, você deseja que ela seja atualizada rapidamente a cada 10 segundos e assim por diante.

Vejamos o que o Excel e o Windows têm a capacidade de implementar isso.

Executando uma macro em uma determinada frequência

A maneira mais fácil de fazer isso é usar o método VBA integrado Aplicação.OnTimeUm que executa a macro especificada na hora especificada. Vamos entender isso com um exemplo prático.

Abra o editor Visual Basic com o botão de mesmo nome na guia revelador (Desenvolvedor) ou atalho de teclado outro+F11, insira um novo módulo através do menu Inserir - Módulo e copie o seguinte código lá:

Dim TimeToRun 'variável global onde o próximo tempo de execução é armazenado 'esta é a macro principal Sub MyMacro() Application.Calculate 'recalcula o livro Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'preencher célula A1 com uma cor aleatória :) Chama NextRun 'executa a macro NextRun para definir o próximo tempo de execução End Sub 'esta macro define o tempo para a próxima execução da macro principal Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'adiciona 3 segundos ao tempo atual Application.OnTime TimeToRun, "MyMacro" 'agenda a próxima execução End Sub 'macro para iniciar a sequência de repetição Sub Start() Chama NextRun End Sub 'macro para parar a sequência de repetição Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Vamos descobrir o que é o que aqui.

Primeiro, precisamos de uma variável que armazene a hora da próxima execução de nossa macro – eu a chamei Hora de correr. Observe que o conteúdo dessa variável deve estar disponível para todas as nossas macros subsequentes, portanto, precisamos torná-lo global, ou seja, declarar no início do módulo antes do primeiro Sub.

Em seguida vem nossa macro principal Minha Macro, que realizará a tarefa principal - recalcular o livro usando o método Aplicação.Calcular. Para deixar mais claro, adicionei a fórmula =TDATE() à planilha na célula A1, que exibe a data e hora – quando recalculado, seu conteúdo será atualizado bem diante de nossos olhos (basta ativar a exibição de segundos na célula formato). Para diversão extra, também adicionei à macro o comando para preencher a célula A1 com uma cor selecionada aleatoriamente (o código da cor é um inteiro no intervalo 0..56, que é gerado pela função Rnd e arredonda para uma função inteira int).

Macro PróximoExecutar adiciona ao valor anterior Hora de correr 3 segundos a mais e, em seguida, agenda a próxima execução da macro principal Minha Macro para este novo tempo. É claro que, na prática, você pode usar qualquer outro intervalo de tempo necessário definindo os argumentos da função Valor do tempo no formato hh:mm:ss.

E, finalmente, apenas por conveniência, mais macros de lançamento de sequência foram adicionadas. INÍCIO e sua conclusão Acabamento. O último usa o quarto argumento do método para quebrar a sequência. Na hora igual Falso.

Total se você executar a macro INÍCIO , então todo este carrossel irá girar e veremos a seguinte imagem na folha:

Você pode parar a sequência executando, respectivamente, a macro Acabamento. Por conveniência, você pode atribuir atalhos de teclado a ambas as macros usando o comando Macros – Opções aba revelador (Desenvolvedor — Macros — Opções).

Executando uma macro em uma programação

Claro, tudo descrito acima só é possível se você tiver o Microsoft Excel em execução e nosso arquivo estiver aberto nele. Agora vamos ver um caso mais complicado: você precisa executar o Excel de acordo com um determinado horário, por exemplo, todos os dias às 5h, abrir um relatório grande e complexo nele e atualizar todas as conexões e consultas nele para que ele esteja pronto quando chegarmos ao trabalho 🙂

Em tal situação, é melhor usar Agendador do Windows – um programa especialmente incorporado em qualquer versão do Windows que pode executar ações especificadas em um cronograma. Na verdade, você já o está usando sem saber, porque seu PC verifica regularmente se há atualizações, baixa novos bancos de dados de antivírus, sincroniza pastas na nuvem, etc. É todo o trabalho do Agendador. Portanto, nossa tarefa é adicionar às tarefas existentes outra que iniciará o Excel e abrirá o arquivo especificado nele. E vamos pendurar nossa macro no evento Pasta de trabalho_Abrir este arquivo – e o problema está resolvido.

Quero avisá-lo imediatamente que trabalhar com o Agendador pode exigir direitos de usuário avançados, portanto, se você não conseguir encontrar os comandos e funções descritos abaixo em seu computador de trabalho no escritório, entre em contato com seus especialistas de TI para obter ajuda.

Iniciando o Agendador

Então vamos iniciar o Agendador. Para fazer isso, você pode:

• Clique com o botão direito no botão Início e escolha Gerenciamento do computador (Gerenciamento de computadores)
• Selecione no Painel de Controle: Administração - Agendador de Tarefas (Painel de Controle — Ferramentas Administrativas — Agendador de Tarefas)
• Selecione no menu principal Iniciar – Acessórios – Ferramentas do Sistema – Agendador de Tarefas
• Pressione o atalho de teclado Wine Folly+R, entrar taskschd.msc e imprensa Entrar

The following window should appear on the screen (I have an English version, but you can also have a version):

Crie uma tarefa

Para criar uma nova tarefa usando um simples assistente passo a passo, clique no link Crie uma tarefa simples (Criar Tarefa Básica) no painel direito.

Na primeira etapa do assistente, insira o nome e a descrição da tarefa a ser criada:

Clique no botão Próximo (Próximo) e na próxima etapa, selecionamos um gatilho – a frequência de lançamento ou um evento que iniciará nossa tarefa (por exemplo, ligar o computador):

Se você escolheu Diário (Diário), então na próxima etapa você precisará selecionar um horário específico, data de início da sequência e etapa (todo 2º dia, 5º dia, etc.):

O próximo passo é escolher uma ação – Execute o programa (Iniciar um programa):

E, finalmente, o mais interessante é o que exatamente precisa ser aberto:

No Programa ou roteiro (Programa/roteiro) você precisa inserir o caminho para o Microsoft Excel como um programa, ou seja, diretamente para o executável do Excel. Em computadores diferentes com versões diferentes do Windows e do Office, esse arquivo pode estar em pastas diferentes, então aqui estão algumas maneiras de descobrir sua localização:

• Clique com o botão direito do mouse no ícone (atalho) para iniciar o Excel na área de trabalho ou na barra de tarefas e selecione o comando Materiais (Propriedades)e, na janela que se abre, copie o caminho da linha Target:

                       

• Abra qualquer pasta de trabalho do Excel e, em seguida, abra Gerenciador de tarefas (Gerenciador de tarefas) empurrando Ctrl+outro+De e clicando com o botão direito na linha Microsoft Excel, escolha um comando Materiais (Propriedades). Na janela que se abre, você pode copiar o caminho, não esquecendo de adicionar uma barra invertida e EXCEL.EXE no final:

               

• Abra o Excel, abra o editor Visual Basic com atalho de teclado outro+F11, painel aberto Imediato uma combinação de Ctrl+G, digite o comando nele:

  ? Aplicativo.Caminho

  … e clique em Entrar

  

  Copie o caminho resultante, não esquecendo de adicionar uma barra invertida e EXCEL.EXE no final.

No Adicione argumentos (opcional) (Adicione argumentos (opcional)) você precisa inserir o caminho completo para o livro com a macro que queremos abrir.

Quando tudo estiver inserido, clique em Próximo e depois Acabamento (Terminar). A tarefa deve ser adicionada à lista geral:

É conveniente gerenciar a tarefa criada usando os botões à direita. Aqui você pode testar a tarefa executando-a imediatamente (corre)sem esperar o tempo especificado. Você pode desativar temporariamente uma tarefa (Desabilitar)para que ele pare de funcionar por um período de tempo, como suas férias. Bem, você sempre pode alterar os parâmetros (datas, hora, nome do arquivo) através do botão Materiais (Propriedades).

Adicionar uma macro para abrir um arquivo

Agora resta pendurar em nosso livro o lançamento da macro que precisamos no evento de abertura do arquivo. Para fazer isso, abra o livro e vá para o editor Visual Basic usando o atalho de teclado outro+F11 ou botões Visual Basic aba revelador (Desenvolvedor). Na janela que se abre no canto superior esquerdo, você precisa encontrar nosso arquivo na árvore e clicar duas vezes para abrir o módulo Este livro (Esta pasta de trabalho).

Se você não vir esta janela no editor do Visual Basic, poderá abri-la através do menu Visualizar — Explorador de Projetos.

Na janela do módulo que se abre, adicione um manipulador de eventos de abertura de livro selecionando-o nas listas suspensas na parte superior livro и Abra, respectivamente:

Um modelo de procedimento deve aparecer na tela. Pasta de trabalho_Abrir, onde entre as linhas Private Sub и End Sub e você precisa inserir os comandos VBA que devem ser executados automaticamente quando esta pasta de trabalho do Excel for aberta, quando o Agendador a abrir de acordo com o agendamento. Aqui estão algumas opções úteis para overclock:

• ThisWorkbook.RefreshAll – Atualiza todas as consultas de dados externos, consultas do Power Query e tabelas dinâmicas. A opção mais versátil. Só não se esqueça de permitir conexões com dados externos por padrão e atualizar links via Arquivo – Opções – Central de Confiabilidade – Opções da Central de Confiabilidade – Conteúdo Externo, caso contrário, ao abrir o livro, aparecerá um aviso padrão e o Excel, sem atualizar nada, aguardará sua bênção na forma de clicar no botão Habilitar conteúdo (Ativar conteúdo):

  

• ActiveWorkbook.Connections(“Connection_Name”).Atualizar — atualizando dados na conexão Connection_Name.
• Planilhas(“Planilha5“).Tabelas Dinâmicas(“Tabela Dinâmica1«).PivotCache.Refresh – atualizando uma única tabela dinâmica chamada Tabela Dinâmica 1 na folha Sheet5.
• Aplicação.Calcular – recálculo de todas as pastas de trabalho do Excel abertas.
• Aplicativo.CalculateFullRebuild – recálculo forçado de todas as fórmulas e reconstrução de todas as dependências entre células em todas as pastas de trabalho abertas (equivalente a reinserir todas as fórmulas).
• Planilhas ("Relatório").PrintOut - imprimir folha Fotos.
• Chamar MyMacro – execute uma macro chamada Minha Macro.
• Esta pasta de trabalho. Salvar - salvar o livro atual
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” - salve o livro em uma pasta D:Arquivo sob o nome Fotos com data e hora anexadas ao nome.

Se você deseja que a macro seja executada apenas quando o arquivo for aberto pelo Agendador às 5h, e não toda vez que o usuário abrir a pasta de trabalho durante o dia útil, faz sentido adicionar uma verificação de horário, por exemplo:

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

Isso é tudo. Não se esqueça de salvar sua pasta de trabalho em um formato habilitado para macro (xlsm ou xlsb) e você poderá fechar o Excel com segurança e voltar para casa, deixando seu computador ligado. Em um determinado momento (mesmo que o PC esteja bloqueado), o Agendador iniciará o Excel e abrirá o arquivo especificado nele, e nossa macro executará as ações programadas. E você vai se deleitar na cama enquanto seu relatório pesado é recalculado automaticamente – beleza! 🙂

• O que são macros, como usá-las, onde inserir código Visual Basic no Excel
• Como criar seu próprio suplemento de macro para Excel
• Como usar o Personal Macro Workbook como uma biblioteca para suas macros no Excel