Execució d'una macro a temps

Un cas molt comú a la pràctica: cal executar una o més de les vostres macros en un moment determinat o amb una freqüència determinada. Per exemple, teniu un informe gran i pesat que s'actualitza mitja hora i us agradaria executar l'actualització mitja hora abans d'arribar a la feina al matí. O teniu una macro que hauria d'enviar correus electrònics automàticament als empleats amb una freqüència especificada. O, quan treballeu amb una taula dinàmica, voleu que s'actualitzi sobre la marxa cada 10 segons, i així successivament.

Vegem què tenen Excel i Windows per implementar-ho.

Execució d'una macro a una freqüència determinada

La manera més senzilla de fer-ho és utilitzar el mètode VBA integrat Aplicació.OnTimeUn que executa la macro especificada a l'hora especificada. Entenem-ho amb un exemple pràctic.

Obriu l'editor de Visual Basic amb el botó del mateix nom a la pestanya revelador (Desenvolupador) o drecera de teclat Alt+F11, inseriu un mòdul nou a través del menú Inserir – Mòdul i copieu-hi el codi següent:

Dim TimeToRun 'variable global on s'emmagatzema el següent temps d'execució' aquesta és la macro principal Sub MyMacro() Application.Calculate 'recalcular el llibre Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cel·la A1 amb un color aleatori :) Crida a NextRun "executa la macro NextRun per establir el següent temps d'execució End Sub "aquesta macro estableix l'hora per a la propera execució de la macro principal Sub NextRun() TimeToRun = Now + TimeValue ("00: 00:03") 'afegiu 3 segons a l'hora actual Application.OnTime TimeToRun, "MyMacro" 'programeu la següent execució End Sub 'macro per iniciar la seqüència de repetició Sub Start() Truqueu a NextRun End Sub 'macro per aturar la seqüència de repetició Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Anem a esbrinar què hi ha aquí.

En primer lloc, necessitem una variable que emmagatzemi l'hora de la propera execució de la nostra macro, jo la vaig cridar TimeToRun. Tingueu en compte que el contingut d'aquesta variable ha d'estar disponible per a totes les nostres macros posteriors, de manera que hem de fer-ho global, és a dir, declarar al principi del mòdul abans del primer Sub.

A continuació ve la nostra macro principal MyMacro, que realitzarà la tasca principal: tornar a calcular el llibre mitjançant el mètode Aplicació.Calcular. Per fer-ho més clar, he afegit la fórmula = TDATE() al full de la cel·la A1, que mostra la data i l'hora; quan es recalculeu, el seu contingut s'actualitzarà just davant dels nostres ulls (només heu d'activar la visualització dels segons a la cel·la). format). Per a una diversió addicional, també vaig afegir a la macro l'ordre per omplir la cel·la A1 amb un color seleccionat aleatòriament (el codi de color és un nombre enter en el rang 0..56, que genera la funció rnd i arrodoneix a una funció entera Int).

Macro NextRun afegeix el valor anterior TimeToRun 3 segons més i després programa la següent execució de la macro principal MyMacro per aquest nou temps. Per descomptat, a la pràctica, podeu utilitzar qualsevol altre interval de temps que necessiteu establint els arguments de la funció TimeValue en el format hh:mm:ss.

I, finalment, només per comoditat, s'han afegit més macros de llançament de seqüències. Home i la seva finalització Finish. L'últim utilitza el quart argument del mètode per trencar la seqüència. A temps igual Fals.

Total si executeu la macro Home, llavors tot aquest carrusel girarà i veurem la següent imatge al full:

Podeu aturar la seqüència executant, respectivament, la macro Finish. Per comoditat, podeu assignar dreceres de teclat a ambdues macros mitjançant l'ordre Macros - Opcions llengüeta revelador (Desenvolupador — Macros — Opcions).

Execució d'una macro segons una programació

Per descomptat, tot el que s'ha descrit anteriorment només és possible si teniu Microsoft Excel en execució i el nostre fitxer hi està obert. Vegem ara un cas més complicat: cal executar Excel segons un horari determinat, per exemple, cada dia a les 5:00, obrir-hi un informe gran i complex i actualitzar-hi totes les connexions i consultes per tal que estigueu preparats quan arribem a la feina 🙂

En aquesta situació, és millor utilitzar Programador de Windows – un programa especialment integrat en qualsevol versió de Windows que pot realitzar accions especificades en una programació. De fet, ja l'estàs utilitzant sense saber-ho, perquè el teu ordinador consulta regularment si hi ha actualitzacions, descarrega noves bases de dades antivirus, sincronitza carpetes al núvol, etc. Tot és feina del Scheduler. Per tant, la nostra tasca és afegir a les tasques existents una altra que iniciarà Excel i obrirà el fitxer especificat. I penjarem la nostra macro a l'esdeveniment Quadern de treball_Obert aquest fitxer i el problema està resolt.

Vull advertir-vos immediatament que treballar amb el Programador pot requerir drets d'usuari avançats, de manera que si no trobeu les ordres i les funcions que es descriuen a continuació a l'ordinador de treball de l'oficina, poseu-vos en contacte amb els vostres especialistes informàtics per obtenir ajuda.

Llançament del programador

Així que comencem el programador. Per fer-ho, podeu:

• Feu clic dret al botó Començar I tria Gestió d'ordinadors (Gestió informàtica)
• Seleccioneu al tauler de control: Administració - Programador de tasques (Tauler de control — Eines administratives — Programador de tasques)
• Seleccioneu al menú principal Inici – Accessoris – Eines del sistema – Programador de tasques
• Premeu la drecera de teclat Guanyar+R, entra taskschd.msc i premi Enter

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

Crear una tasca

Per crear una tasca nova amb un senzill assistent pas a pas, feu clic a l'enllaç Crea una tasca senzilla (Crea una tasca bàsica) al panell dret.

En el primer pas de l'assistent, introduïu el nom i la descripció de la tasca a crear:

Feu clic al botó Següent (Pròxim) i al següent pas seleccionem un activador: la freqüència de llançament o un esdeveniment que iniciarà la nostra tasca (per exemple, encendre l'ordinador):

Si ho vau triar diari (Diàriament), aleshores, al pas següent, haureu de seleccionar una hora específica, la data d'inici de la seqüència i el pas (cada 2n dia, 5è dia, etc.):

El següent pas és triar una acció: Executeu el programa (Iniciar un programa):

I finalment, el més interessant és què s'ha d'obrir exactament:

A la Programa o guió (Programa/guió) cal que introduïu el camí a Microsoft Excel com a programa, és a dir, directament a l'executable d'Excel. En diferents ordinadors amb diferents versions de Windows i Office, aquest fitxer pot estar en carpetes diferents, així que aquí teniu algunes maneres d'esbrinar-ne la ubicació:

• Feu clic amb el botó dret a la icona (drecera) per iniciar Excel a l'escriptori o a la barra de tasques i seleccioneu l'ordre materials (Propietats), i després a la finestra que s'obre, copieu el camí de la línia Objectiu:

                       

• Obriu qualsevol llibre d'Excel i, a continuació, obriu Cap de tasques (Cap de tasques) Empènyer Ctrl+Alt+de i fent clic dret a la línia Microsoft Excel, trieu una ordre materials (Propietats). A la finestra que s'obre, podeu copiar el camí, sense oblidar-hi d'afegir-hi una barra invertida i EXCEL.EXE al final:

               

• Obriu Excel, obriu l'editor Visual Basic amb la drecera de teclat Alt+F11, panell obert Immediat una combinació de Ctrl+G, introduïu-hi l'ordre:

  ? Aplicació.Camí

  … i feu clic a sobre Enter

  

  Copieu el camí resultant, sense oblidar-hi d'afegir-hi una barra invertida i EXCEL.EXE al final.

A la Afegeix arguments (opcional) (Afegiu arguments (opcional)) cal inserir el camí complet al llibre amb la macro que volem obrir.

Quan s'hagi introduït tot, feu clic Següent i després Finish (Acabar). La tasca s'ha d'afegir a la llista general:

És convenient gestionar la tasca creada mitjançant els botons de la dreta. Aquí podeu provar la tasca executant-la immediatament (correr)sense esperar el temps indicat. Podeu desactivar temporalment una tasca (Inhabilitar)perquè deixi de funcionar durant un període de temps, com ara les vacances. Bé, sempre podeu canviar els paràmetres (dates, hora, nom del fitxer) mitjançant el botó materials (Propietats).

Afegiu una macro per obrir un fitxer

Ara queda penjar al nostre llibre el llançament de la macro que necessitem a l'esdeveniment d'obertura de fitxers. Per fer-ho, obriu el llibre i aneu a l'editor de Visual Basic mitjançant la drecera de teclat Alt+F11 o botons Visual Basic llengüeta revelador (Desenvolupador). A la finestra que s'obre a la cantonada superior esquerra, heu de trobar el nostre fitxer a l'arbre i fer doble clic per obrir el mòdul. Aquest llibre (Aquest llibre de treball).

Si no veieu aquesta finestra a l'editor de Visual Basic, podeu obrir-la a través del menú Visualització — Explorador de projectes.

A la finestra del mòdul que s'obre, afegiu un gestor d'esdeveniments oberts al llibre seleccionant-lo de les llistes desplegables de la part superior Quadern de treball и obert, respectivament:

A la pantalla hauria d'aparèixer una plantilla de procediment. Quadern de treball_Obert, on entre les línies Sub privat и End Sub i cal inserir aquelles ordres VBA que s'haurien d'executar automàticament quan s'obre aquest llibre de treball d'Excel, quan el Programador l'obre segons la programació. Aquí hi ha algunes opcions útils per fer overclocking:

• Aquest quadern de treball.RefreshAll – Actualitza totes les consultes de dades externes, consultes de Power Query i taules dinàmiques. L'opció més versàtil. Només no oblideu permetre connexions a dades externes de manera predeterminada i actualitzar els enllaços mitjançant Fitxer – Opcions – Centre de confiança – Opcions del centre de confiança – Contingut extern, en cas contrari, quan obriu el llibre, apareixerà un avís estàndard i Excel, sense actualitzar res, esperarà la vostra benedicció en forma de clic al botó Activa el contingut (Activa el contingut):

  

• ActiveWorkbook.Connections ("Nom_Connexió"). Actualitzar — actualització de dades a la connexió Connection_Name.
• Fulls ("Fitxa 5“).PivotTaules(“PivotTable1«).PivotCache.Actualitzar – actualització d'una única taula dinàmica anomenada Taula dinàmica 1 al full Sheet5.
• Aplicació.Calcular – recàlcul de tots els llibres de treball d'Excel oberts.
• Aplicació.CalculateFullRebuild – Recàlcul forçat de totes les fórmules i reconstrucció de totes les dependències entre cel·les de tots els llibres de treball oberts (equivalent a tornar a introduir totes les fórmules).
• Fulls de treball ("Informe"). Imprimir - full d'impressió fotos.
• Truqueu a MyMacro - executar una macro anomenada MyMacro.
• Aquest llibre de treball. Desa - desar el llibre actual
  
• ThisWorkbooks.SaveAs “D:ArxiveReport” i substituir(Ara, “:”, “-“) i “.xlsx” - desar el llibre en una carpeta D: Arxiu sota el nom fotos amb data i hora annexa al nom.

Si voleu que la macro s'executi només quan el programador obre el fitxer a les 5:00 a.m. i no cada cop que l'usuari obre el llibre de treball durant el dia laboral, té sentit afegir una comprovació de temps, per exemple:

Si Format(Ara, "hh:mm") = "05:00" Aleshores ThisWorkbook.RefreshAll  

Això és tot. No oblideu desar el vostre quadern de treball en un format habilitat per a macros (xlsm o xlsb) i podreu tancar Excel amb seguretat i tornar a casa, deixant l'ordinador encès. En un moment determinat (encara que el PC estigui bloquejat), el Programador iniciarà Excel i obrirà el fitxer especificat en ell, i la nostra macro realitzarà les accions programades. I et relaxaràs al llit mentre el teu informe pesat es recalcula automàticament: bellesa! 🙂

• Què són les macros, com utilitzar-les, on inserir codi Visual Basic a Excel
• Com crear el vostre propi complement de macro per a Excel
• Com utilitzar el llibre de treball de macros personals com a biblioteca per a les vostres macros a Excel