Executar unha macro a tempo

Un caso moi común na práctica: cómpre executar unha ou máis das túas macros nun momento determinado ou cunha determinada frecuencia. Por exemplo, tes un informe grande e pesado que se actualiza media hora e queres executar a actualización media hora antes de chegar ao traballo pola mañá. Ou tes unha macro que debería enviar automaticamente correos electrónicos aos empregados cunha frecuencia especificada. Ou, cando se traballa cunha táboa dinámica, quere que se actualice sobre a marcha cada 10 segundos, etc.

Vexamos que Excel e Windows teñen a capacidade de implementar isto.

Execución dunha macro a unha frecuencia determinada

A forma máis sinxela de facelo é usar o método VBA integrado Aplicación.OnTimeUn que executa a macro especificada no momento especificado. Imos entender isto cun exemplo práctico.

Abre o editor de Visual Basic co botón do mesmo nome na pestana revelador (Desenvolvedor) ou atallo de teclado alt+F11, insira un novo módulo a través do menú Inserir – Módulo e copia alí o seguinte código:

Dim TimeToRun 'variable global onde se almacena o seguinte tempo de execución' esta é a macro principal Sub MyMacro() Application.Calculate 'recalcular the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cela A1 cunha cor aleatoria :) Chama a NextRun 'executa a macro NextRun para definir o seguinte tempo de execución End Sub 'esta macro establece o tempo para a próxima execución da macro principal Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'engade 3 segundos á hora actual Application.OnTime TimeToRun, "MyMacro" 'programe a seguinte macro End Sub 'para iniciar a secuencia de repetición Sub Start() Chame a NextRun End Sub 'macro para deter a secuencia de repetición Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Imos descubrir que é o que aquí.

En primeiro lugar, necesitamos unha variable que almacene a hora da seguinte execución da nosa macro, chameino TimeToRun. Teña en conta que o contido desta variable debe estar dispoñible para todas as nosas macros posteriores, polo que debemos facelo global, é dicir, declarar ao principio do módulo antes do primeiro submarino.

A continuación vén a nosa macro principal MyMacro, que realizará a tarefa principal: recalcular o libro mediante o método Aplicación.Calcular. Para que quede máis claro, engadín a fórmula = TDATE() á folla da cela A1, que mostra a data e a hora; cando se recalcule, o seu contido actualizarase xusto ante os nosos ollos (só hai que activar a visualización de segundos na cela). formato). Para divertirse, tamén engadín á macro o comando para encher a cela A1 cunha cor seleccionada ao azar (o código de cor é un número enteiro no rango 0..56, que é xerado pola función). Rnd e redondea a unha función enteira Int).

Macro NextRun engádese ao valor anterior TimeToRun 3 segundos máis e despois programa a seguinte execución da macro principal MyMacro para este novo tempo. Por suposto, na práctica, pode usar calquera outro intervalo de tempo que necesite configurando os argumentos da función TimeValue no formato hh:mm:ss.

E, finalmente, só por comodidade, engadíronse máis macros de inicio de secuencia. casa e o seu remate Rematar. O último usa o argumento do cuarto método para romper a secuencia. Na hora igual Falso.

Total se executas a macro casa, entón todo este carrusel xirará, e veremos a seguinte imaxe na folla:

Pode deter a secuencia executando, respectivamente, a macro Rematar. Para maior comodidade, pode asignar atallos de teclado a ambas macros mediante o comando Macros - Opcións aba revelador (Desenvolvedor - Macros - Opcións).

Execución dunha macro nunha programación

Por suposto, todo o descrito anteriormente só é posible se tes Microsoft Excel en execución e o noso ficheiro está aberto nel. Agora vexamos un caso máis complicado: cómpre executar Excel segundo un horario determinado, por exemplo, todos os días ás 5:00, abrir nel un informe grande e complexo e actualizar todas as conexións e consultas nel para que se estar preparado para cando cheguemos ao traballo 🙂

En tal situación, é mellor usar Programador de Windows – un programa integrado especialmente en calquera versión de Windows que pode realizar accións especificadas nunha programación. De feito, xa o estás a usar sen sabelo, porque o teu PC verifica regularmente as actualizacións, descarga novas bases de datos antivirus, sincroniza cartafoles na nube, etc. Todo é traballo do Scheduler. Polo tanto, a nosa tarefa é engadir ás tarefas existentes outra que iniciará Excel e abrirá o ficheiro especificado nel. E colgaremos a nosa macro no evento Caderno de traballo_Aberto este ficheiro e o problema está resolto.

Quero avisarche de inmediato de que traballar co Programador pode requirir dereitos de usuario avanzados, polo que se non podes atopar os comandos e funcións descritos a continuación no teu ordenador de traballo na oficina, ponte en contacto cos teus especialistas en TI para obter axuda.

Iniciando o Scheduler

Entón, imos comezar o Scheduler. Para facelo, pode:

• Fai clic co botón dereito no botón comezo e elixe Xestión de computadores (Xestión informática)
• Seleccione no panel de control: Administración - Programador de tarefas (Panel de control — Ferramentas administrativas — Programador de tarefas)
• Seleccione no menú principal Inicio – Accesorios – Ferramentas do sistema – Programador de tarefas
• Preme o atallo de teclado gañar+R, entra taskschd.msc e prensa Entrar

Na pantalla debería aparecer a seguinte xanela (teño unha versión en inglés, pero tamén podes ter unha versión):

Crea unha tarefa

Para crear unha nova tarefa usando un asistente sinxelo paso a paso, fai clic na ligazón Crea unha tarefa sinxela (Crear tarefa básica) no panel dereito.

No primeiro paso do asistente, introduza o nome e a descrición da tarefa que se vai crear:

Fai clic no botón seguinte (Seguinte) e no seguinte paso seleccionamos un disparador: a frecuencia de lanzamento ou un evento que iniciará a nosa tarefa (por exemplo, encender o ordenador):

Se escolleu diario (Diario), entón no seguinte paso terás que seleccionar unha hora específica, a data de inicio da secuencia e o paso (cada 2º día, 5º día, etc.):

O seguinte paso é escoller unha acción: Executa o programa (Iniciar un programa):

E, finalmente, o máis interesante é o que hai que abrir exactamente:

No Programa ou guión (Programa/Guión) cómpre introducir o camiño a Microsoft Excel como programa, é dicir, directamente ao executable de Excel. En diferentes ordenadores con diferentes versións de Windows e Office, este ficheiro pode estar en diferentes cartafoles, polo que aquí tes algunhas formas de descubrir a súa localización:

• Fai clic co botón dereito na icona (atallo) para iniciar Excel no escritorio ou na barra de tarefas e selecciona o comando Obras (Propiedades), e despois na xanela que se abre, copie o camiño desde a liña Branco:

                       

• Abre calquera caderno de Excel e despois ábreo Xestor de tarefas (Xestor de tarefas) empurrando Ctrl+alt+de e facendo clic co botón dereito na liña Microsoft Excel, escolla un comando Obras (Propiedades). Na xanela que se abre, pode copiar o camiño, sen esquecerlle engadir unha barra invertida e EXCEL.EXE ao final:

               

• Abra Excel, abra o editor de Visual Basic co atallo de teclado alt+F11, panel aberto inmediato unha combinación de Ctrl+G, introduza o comando:

  ? Aplicación.Camiño

  ... e prema Entrar

  

  Copia o camiño resultante, sen esquecerlle engadir unha barra invertida e EXCEL.EXE ao final.

No Engadir argumentos (opcional) (Engadir argumentos (opcional)) cómpre inserir o camiño completo ao libro coa macro que queremos abrir.

Cando estea introducido todo, prema seguinte e despois Rematar (Finalizar). A tarefa debe engadirse á lista xeral:

É conveniente xestionar a tarefa creada usando os botóns da dereita. Aquí podes probar a tarefa executándoa inmediatamente (correr)sen esperar o tempo indicado. Podes desactivar temporalmente unha tarefa (Desactivar)para que deixe de funcionar durante un período de tempo, como as túas vacacións. Ben, sempre pode cambiar os parámetros (datas, hora, nome do ficheiro) a través do botón Obras (Propiedades).

Engade unha macro para abrir un ficheiro

Agora queda colgar no noso libro o lanzamento da macro que necesitamos no evento aberto ficheiro. Para iso, abra o libro e vai ao editor de Visual Basic usando o atallo de teclado alt+F11 ou botóns Visual Basic aba revelador (Desenvolvedor). Na xanela que se abre na esquina superior esquerda, cómpre atopar o noso ficheiro na árbore e facer dobre clic para abrir o módulo Este libro (Este Caderno de traballo).

Se non ves esta xanela no editor de Visual Basic, pode abrila a través do menú Ver — Explorador de proxectos.

Na xanela do módulo que se abre, engade un controlador de eventos de aberto de libro seleccionándoo nas listas despregábeis da parte superior Cartafol de traballo и aberto, respectivamente:

Na pantalla debería aparecer un modelo de procedemento. Caderno de traballo_Aberto, onde entre as liñas Sub privado и End Sub e cómpre inserir aqueles comandos VBA que deberían executarse automaticamente cando se abra este libro de Excel, cando o Programador o abra segundo a programación. Aquí tes algunhas opcións útiles para overclocking:

• Este libro de traballo.RefreshAll – Actualiza todas as consultas de datos externas, consultas de Power Query e táboas dinámicas. A opción máis versátil. Non esquezas permitir conexións a datos externos de forma predeterminada e actualizar as ligazóns mediante Ficheiro – Opcións – Centro de confianza – Opcións do centro de confianza – Contido externo, se non, ao abrir o libro, aparecerá un aviso estándar e Excel, sen actualizar nada, agardará a túa bendición en forma de premer no botón Activa o contido (Activar contido):

  

• ActiveWorkbook.Connections(“Nome_conexión”).Actualizar — actualizando datos na conexión Connection_Name.
• Follas ("Ficha 5“).Táboas dinámicas(“Táboa dinámica1«).PivotCache.Actualizar – actualizar unha única táboa dinámica denominada Táboa dinámica 1 na folla Sheet5.
• Aplicación.Calcular – recálculo de todos os libros de Excel abertos.
• Aplicación.CalculateFullRebuild – o recálculo forzado de todas as fórmulas e a reconstrución de todas as dependencias entre celas en todos os libros de traballo abertos (equivalente a volver introducir todas as fórmulas).
• Follas de traballo ("Informe").Imprimir - folla de impresión fotos.
• Chama a MyMacro - executar unha macro chamada MyMacro.
• Este libro de traballo. Gardar - gardar o libro actual
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Substituír(Agora, “:”, “-“) e “.xlsx” - gardar o libro nun cartafol D: Arquivo baixo o nome fotos coa data e a hora engadidas ao nome.

Se queres que a macro se execute só cando o programador abre o ficheiro ás 5:00 am, e non cada vez que o usuario abre o libro de traballo durante o día de traballo, ten sentido engadir unha comprobación de tempo, por exemplo:

Se Formato(Agora, "hh:mm") = "05:00" Entón ThisWorkbook.RefreshAll  

Iso é todo. Non esquezas gardar o teu caderno de traballo nun formato habilitado para macros (xlsm ou xlsb) e poderás pechar Excel con seguridade e volver a casa, deixando o ordenador aceso. Nun momento determinado (aínda que o PC estea bloqueado), o Programador iniciará Excel e abrirá o ficheiro especificado nel, e a nosa macro realizará as accións programadas. E gozarás de luxo na cama mentres o teu pesado informe se recalcula automaticamente: beleza! 🙂

• Que son as macros, como usalas, onde inserir o código Visual Basic en Excel
• Como crear o teu propio complemento de macros para Excel
• Como usar o libro de traballo de macro persoal como biblioteca para as túas macros en Excel