Ejecutar una macro a tiempo

Un caso muy común en la práctica: necesita ejecutar una o más de sus macros en un momento determinado o con una frecuencia determinada. Por ejemplo, tiene un informe grande y pesado que se actualiza media hora y le gustaría ejecutar la actualización media hora antes de llegar al trabajo por la mañana. O tiene una macro que debería enviar automáticamente correos electrónicos a los empleados con una frecuencia específica. O, cuando trabaja con una tabla dinámica, desea que se actualice sobre la marcha cada 10 segundos, y así sucesivamente.

Veamos qué Excel y Windows tienen la capacidad de implementar esto.

Ejecutar una macro a una frecuencia dada

La forma más fácil de hacer esto es usar el método VBA integrado. Aplicación.OnTimeque ejecuta la macro especificada en el momento especificado. Entendamos esto con un ejemplo práctico.

Abra el editor de Visual Basic con el botón del mismo nombre en la pestaña revelador (Desarrollador) o atajo de teclado otro+F11, inserte un nuevo módulo a través del menú Insertar – Módulo y copiar el siguiente código allí:

Dim TimeToRun 'variable global donde se almacena el siguiente tiempo de ejecución 'esta es la macro principal Sub MyMacro() Application.Calculate 'recalcular el rango del libro ("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill celda A1 con un color aleatorio :) Llamar a NextRun 'ejecutar la macro NextRun para establecer el tiempo de ejecución siguiente End Sub 'esta macro establece el tiempo para la próxima ejecución de la macro principal Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'añade 3 segundos a la hora actual Application.OnTime TimeToRun, "MyMacro" 'programa la siguiente ejecución End Sub 'macro para iniciar la secuencia de repetición Sub Start() Call NextRun End Sub 'macro para detener la secuencia de repetición Sub Finish () Aplicación. OnTime TimeToRun, "MyMacro", , False End Sub  

Averigüemos qué es qué aquí.

Primero, necesitamos una variable que almacene la hora de la próxima ejecución de nuestra macro. La llamé Hora de correr. Tenga en cuenta que el contenido de esta variable debe estar disponible para todas nuestras macros posteriores, por lo que debemos hacerlo global, es decir, declarar al principio del módulo antes del primero Sub.

Luego viene nuestra macro principal MiMacro, que realizará la tarea principal: volver a calcular el libro utilizando el método Aplicación.Calcular. Para que quede más claro, agregué la fórmula =TDATE() a la hoja en la celda A1, que muestra la fecha y la hora; cuando se vuelve a calcular, su contenido se actualizará ante nuestros ojos (simplemente encienda la visualización de segundos en la celda formato). Para mayor diversión, también agregué a la macro el comando para llenar la celda A1 con un color seleccionado al azar (el código de color es un número entero en el rango de 0 a 56, que es generado por la función Rnd y redondea a una función entera Int.).

Macro SiguienteEjecutar se suma al valor anterior Hora de correr 3 segundos más y luego programa la próxima ejecución de la macro principal MiMacro para este nuevo tiempo. Por supuesto, en la práctica, puede usar cualquier otro intervalo de tiempo que necesite configurando los argumentos de la función Valor del tiempo en el formato hh:mm:ss.

Y finalmente, solo por conveniencia, se han agregado más macros de inicio de secuencia. Inicio y su finalización Acabado. El último usa el argumento del cuarto método para romper la secuencia. A tiempo igual Falso.

Total si ejecutas la macro Inicio, entonces todo este carrusel girará, y veremos la siguiente imagen en la hoja:

Puede detener la secuencia ejecutando, respectivamente, la macro Acabado. Para mayor comodidad, puede asignar métodos abreviados de teclado a ambas macros con el comando Macros – Opciones de la pestaña. revelador (Desarrollador — Macros — Opciones).

Ejecutar una macro en un horario

Por supuesto, todo lo descrito anteriormente solo es posible si tiene Microsoft Excel ejecutándose y nuestro archivo está abierto en él. Ahora veamos un caso más complicado: debe ejecutar Excel de acuerdo con un horario determinado, por ejemplo, todos los días a las 5:00, abrir un informe grande y complejo en él y actualizar todas las conexiones y consultas en él para que estar listo para cuando lleguemos al trabajo 🙂

En tal situación, es mejor usar Programador de Windows – un programa especialmente integrado en cualquier versión de Windows que puede realizar acciones específicas en un horario. De hecho, ya lo está usando sin saberlo, porque su PC busca actualizaciones regularmente, descarga nuevas bases de datos antivirus, sincroniza carpetas en la nube, etc. Todo es trabajo del Programador. Entonces, nuestra tarea es agregar a las tareas existentes otra que iniciará Excel y abrirá el archivo especificado en él. Y colgaremos nuestra macro en el evento. Libro de trabajo_abierto este archivo, y el problema está resuelto.

Quiero advertirle de inmediato que trabajar con el Programador puede requerir derechos de usuario avanzados, por lo que si no puede encontrar los comandos y funciones que se describen a continuación en su computadora de trabajo en la oficina, comuníquese con sus especialistas de TI para obtener ayuda.

Lanzamiento del Programador

Así que vamos a iniciar el Programador. Para hacer esto, puede:

• Haga clic derecho en el botón Inicio y elige Administración de equipos (Gestión informática)
• Seleccione en el Panel de control: Administración - Programador de tareas (Panel de control — Herramientas administrativas — Programador de tareas)
• Seleccione del menú principal Inicio – Accesorios – Herramientas del sistema – Programador de tareas
• Presione el atajo de teclado Win+R, introduzca taskschd.msc y pulse Participar

Debería aparecer en pantalla la siguiente ventana (tengo una versión en inglés, pero tú también puedes tener una versión):

Crear una tarea

Para crear una nueva tarea utilizando un sencillo asistente paso a paso, haga clic en el enlace Crea una tarea sencilla (Crear tarea básica) en el panel derecho.

En el primer paso del asistente, ingrese el nombre y la descripción de la tarea que se creará:

Haga clic en el botón Siguiente (Próximo) y en el siguiente paso seleccionamos un activador: la frecuencia de inicio o un evento que iniciará nuestra tarea (por ejemplo, encender la computadora):

Si ha elegido Diario (Diario), luego, en el siguiente paso, deberá seleccionar una hora específica, la fecha de inicio de la secuencia y el paso (cada 2.° día, 5.° día, etc.):

El siguiente paso es elegir una acción: Ejecuta el programa (Iniciar un programa):

Y finalmente, lo más interesante es qué es exactamente lo que hay que abrir:

En programa o guion (Programa/guión) debe ingresar la ruta a Microsoft Excel como un programa, es decir, directamente al ejecutable de Excel. En diferentes computadoras con diferentes versiones de Windows y Office, este archivo puede estar en diferentes carpetas, así que aquí hay algunas formas de averiguar su ubicación:

• Haga clic con el botón derecho en el icono (acceso directo) para iniciar Excel en el escritorio o en la barra de tareas y seleccione el comando Materiales (Propiedades), y luego en la ventana que se abre, copie la ruta de la línea Target:

                       

• Abra cualquier libro de Excel, luego abra Administrador de tareas (Administrador de tareas) empujando Ctrl+otro+Desde y haciendo clic derecho en la línea Microsoft Excel, elige un comando Materiales (Propiedades). En la ventana que se abre, puede copiar la ruta, sin olvidar agregarle una barra invertida y EXCEL.EXE al final:

               

• Abra Excel, abra el editor de Visual Basic con el método abreviado de teclado otro+F11, panel abierto inmediata Una combinación de Ctrl+G, ingrese el comando en él:

  ? Aplicación.Ruta

  … y haga clic en Participar

  

  Copie la ruta resultante, sin olvidar agregarle una barra invertida y EXCEL.EXE al final.

En Agregar argumentos (opcional) (Añadir argumentos (opcional)) debe insertar la ruta completa al libro con la macro que queremos abrir.

Cuando haya ingresado todo, haga clic en Siguiente y luego Acabado (Terminar). La tarea debe agregarse a la lista general:

Es conveniente gestionar la tarea creada mediante los botones de la derecha. Aquí puede probar la tarea ejecutándola inmediatamente (correr)sin esperar el tiempo especificado. Puedes desactivar temporalmente una tarea (Desactivar)para que deje de funcionar por un período de tiempo, como sus vacaciones. Bueno, siempre puedes cambiar los parámetros (fechas, hora, nombre del archivo) a través del botón Materiales (Propiedades).

Agregar una macro para abrir un archivo

Ahora queda colgar en nuestro libro el lanzamiento de la macro que necesitamos en el evento de apertura de archivo. Para hacer esto, abra el libro y vaya al editor de Visual Basic usando el atajo de teclado otro+F11 o botones Visual Basic de la pestaña. revelador (Desarrollador). En la ventana que se abre en la esquina superior izquierda, debe encontrar nuestro archivo en el árbol y hacer doble clic para abrir el módulo. Este libro (Este libro de trabajo).

Si no ve esta ventana en el editor de Visual Basic, puede abrirla a través del menú Ver — Explorador de proyectos.

En la ventana del módulo que se abre, agregue un controlador de eventos de apertura de libro seleccionándolo de las listas desplegables en la parte superior Libro de trabajo и Abierto, respectivamente:

Debería aparecer una plantilla de procedimiento en la pantalla. Libro de trabajo_abierto, donde entre líneas Sub privado и End Sub y debe insertar los comandos de VBA que deben ejecutarse automáticamente cuando se abre este libro de Excel, cuando el Programador lo abre de acuerdo con el cronograma. Aquí hay algunas opciones útiles para el overclocking:

• Este libro de trabajo. Actualizar todo – Actualiza todas las consultas de datos externos, consultas de Power Query y tablas dinámicas. La opción más versátil. Simplemente no olvide permitir las conexiones a datos externos de forma predeterminada y actualizar los enlaces a través de Archivo – Opciones – Centro de confianza – Opciones del Centro de confianza – Contenido externo, de lo contrario, cuando abra el libro, aparecerá una advertencia estándar y Excel, sin actualizar nada, esperará su bendición en forma de hacer clic en el botón Contenido disponible (Contenido disponible):

  

• ActiveWorkbook.Connections(“Nombre_conexión”).Actualizar — actualizar datos en la conexión Connection_Name.
• Hojas(“Hoja5“).TablasDinámicas(“TablaDinámica1«).PivotCache.Actualizar – actualizar una sola tabla dinámica llamada PivotTable1 en la hoja Sheet5.
• Aplicación.Calcular – Recálculo de todos los libros de Excel abiertos.
• Aplicación.CalculateFullRebuild – recálculo forzado de todas las fórmulas y reconstrucción de todas las dependencias entre celdas en todos los libros abiertos (equivalente a volver a ingresar todas las fórmulas).
• Hojas de trabajo ("Informe"). PrintOut – hoja de impresión Fotos.
• Llamar a MiMacro – ejecutar una macro llamada MiMacro.
• Este libro de trabajo. Guardar - guardar el libro actual
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" y Reemplazar (Ahora, ":", "-") y ".xlsx" – guardar el libro en una carpeta D:Archivo bajo el nombre Fotos con la fecha y la hora añadidas al nombre.

Si desea que la macro se ejecute solo cuando el programador abre el archivo a las 5:00 am, y no cada vez que el usuario abre el libro durante la jornada laboral, entonces tiene sentido agregar una verificación de tiempo, por ejemplo:

Si Format(Now, "hh:mm") = "05:00" Entonces ThisWorkbook.RefreshAll  

Eso es todo. No olvide guardar su libro de trabajo en un formato habilitado para macros (xlsm o xlsb) y puede cerrar Excel de manera segura e irse a casa, dejando su computadora encendida. En un momento dado (incluso si la PC está bloqueada), el Programador iniciará Excel y abrirá el archivo especificado en él, y nuestra macro realizará las acciones programadas. Y te deleitarás en la cama mientras tu informe pesado se recalcula automáticamente: ¡hermosa! 🙂

• Qué son las macros, cómo usarlas, dónde insertar código de Visual Basic en Excel
• Cómo crear su propio complemento de macros para Excel
• Cómo utilizar el Libro de macros personal como biblioteca para sus macros en Excel