Esecuzione di una macro in tempo

Un caso molto comune in pratica: devi eseguire una o più delle tue macro in un dato momento o con una certa frequenza. Ad esempio, si dispone di un rapporto ampio e pesante che si aggiorna mezz'ora e si desidera eseguire l'aggiornamento mezz'ora prima di arrivare al lavoro al mattino. Oppure hai una macro che dovrebbe inviare automaticamente e-mail ai dipendenti a una frequenza specificata. Oppure, quando si lavora con una tabella pivot, si desidera che si aggiorni al volo ogni 10 secondi e così via.

Diamo un'occhiata a ciò che Excel e Windows hanno la capacità di implementarlo.

Esecuzione di una macro a una determinata frequenza

Il modo più semplice per farlo è utilizzare il metodo VBA integrato Applicazione.OnTimeAn che esegue la macro specificata all'ora specificata. Capiamolo con un esempio pratico.

Aprire l'editor di Visual Basic con il pulsante con lo stesso nome nella scheda sviluppatore (Sviluppatore) o scorciatoia da tastiera altro+F11, inserire un nuovo modulo tramite il menu Inserisci – Modulo e copia lì il seguente codice:

Dim TimeToRun 'variabile globale in cui è memorizzato il tempo di esecuzione successivo 'questa è la macro principale Sub MyMacro() Application.Calculate 'ricalcola il libro Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cella A1 con un colore casuale :) Call NextRun 'esegui la macro NextRun per impostare il tempo di esecuzione successivo End Sub 'questa macro imposta l'ora per la prossima esecuzione della macro principale Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'aggiunge 3 secondi all'ora corrente Application.OnTime TimeToRun, "MyMacro" 'programma l'esecuzione successiva End Sub 'macro per avviare la sequenza di ripetizione Sub Start() Call NextRun End Sub 'macro per interrompere la sequenza di ripetizione Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Scopriamo cosa è cosa qui.

Innanzitutto, abbiamo bisogno di una variabile che memorizzerà l'ora della prossima esecuzione della nostra macro: l'ho chiamata TimeToRun. Tieni presente che il contenuto di questa variabile deve essere disponibile per tutte le nostre macro successive, quindi dobbiamo renderlo globale, cioè dichiarare all'inizio del modulo prima del primo Sub.

Poi viene la nostra macro principale La mia macro, che svolgerà il compito principale: ricalcolare il libro utilizzando il metodo Applicazione.Calcola. Per rendere più chiaro, ho aggiunto la formula =TDATE() al foglio nella cella A1, che mostra la data e l'ora: una volta ricalcolato, il suo contenuto verrà aggiornato proprio davanti ai nostri occhi (basta attivare la visualizzazione dei secondi nella cella formato). Per divertimento in più, ho anche aggiunto alla macro il comando per riempire la cella A1 con un colore selezionato casualmente (il codice colore è un numero intero nell'intervallo 0..56, che è generato dalla funzione Rnd e arrotonda a una funzione intera Int).

Macro Avanti Corri si aggiunge al valore precedente TimeToRun 3 secondi in più e quindi pianifica l'esecuzione successiva della macro principale La mia macro per questo nuovo tempo. Naturalmente, in pratica, puoi utilizzare qualsiasi altro intervallo di tempo di cui hai bisogno impostando gli argomenti della funzione ValoreOra nel formato hh:mm:ss.

E infine, solo per comodità, sono state aggiunte altre macro di avvio della sequenza. Casa e il suo completamento Fine. L'ultimo usa il quarto argomento del metodo per interrompere la sequenza. Puntuale pari Falso.

Totale se si esegue la macro Casa, quindi l'intera giostra girerà e vedremo la seguente immagine sul foglio:

È possibile interrompere la sequenza eseguendo, rispettivamente, la macro Fine. Per comodità, puoi assegnare scorciatoie da tastiera a entrambe le macro usando il comando Macro – Opzioni linguetta sviluppatore (Sviluppatore — Macro — Opzioni).

Esecuzione di una macro in base a una pianificazione

Ovviamente, tutto quanto sopra descritto è possibile solo se hai Microsoft Excel in esecuzione e il nostro file è aperto al suo interno. Ora esaminiamo un caso più complicato: è necessario eseguire Excel secondo una determinata pianificazione, ad esempio ogni giorno alle 5:00, aprire un report ampio e complesso e aggiornare tutte le connessioni e le query in esso contenute in modo che sii pronto quando arriviamo al lavoro 🙂

In una situazione del genere, è meglio usare Utilità di pianificazione di Windows – un programma appositamente integrato in qualsiasi versione di Windows in grado di eseguire azioni specificate in base a una pianificazione. In effetti, lo stai già utilizzando senza saperlo, perché il tuo PC controlla regolarmente gli aggiornamenti, scarica nuovi database antivirus, sincronizza le cartelle cloud, ecc. È tutto lavoro dello Scheduler. Quindi il nostro compito è aggiungere alle attività esistenti un altro che avvierà Excel e aprirà il file specificato al suo interno. E appenderemo la nostra macro all'evento Cartella di lavoro_Apri questo file – e il problema è risolto.

Voglio avvisarti subito che lavorare con lo Scheduler potrebbe richiedere diritti utente avanzati, quindi se non riesci a trovare i comandi e le funzioni descritte di seguito sul tuo computer di lavoro in ufficio, contatta i tuoi specialisti IT per assistenza.

Avvio dell'utilità di pianificazione

Quindi avviamo lo Scheduler. Per fare ciò, puoi:

• Fare clic con il tasto destro del mouse sul pulsante Inizio e scegli Gestione computer (Gestione informatica)
• Seleziona nel Pannello di controllo: Amministrazione – Utilità di pianificazione (Pannello di controllo — Strumenti di amministrazione — Utilità di pianificazione)
• Seleziona dal menu principale Start – Accessori – Utilità di sistema – Utilità di pianificazione
• Premi la scorciatoia da tastiera Win+R, accedere taskschd.msc e premere entrare

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

Crea un'attività

Per creare una nuova attività utilizzando una semplice procedura guidata passo passo, fare clic sul collegamento Crea un compito semplice (Crea attività di base) nel pannello di destra.

Al primo passaggio della procedura guidata, inserisci il nome e la descrizione dell'attività da creare:

Fare clic sul pulsante Avanti (Il prossimo) e nel passaggio successivo selezioniamo un trigger: la frequenza di avvio o un evento che avvierà la nostra attività (ad esempio, accendere il computer):

Se si sceglie Dailytrad (Quotidiano), quindi nel passaggio successivo dovrai selezionare un'ora, una data di inizio della sequenza e un passaggio specifici (ogni 2° giorno, 5° giorno, ecc.):

Il prossimo passo è scegliere un'azione - Eseguire il programma (Avvia un programma):

E infine, la cosa più interessante è cosa esattamente deve essere aperto:

Nel Programma o script (Programma/script) è necessario inserire il percorso di Microsoft Excel come programma, ovvero direttamente all'eseguibile di Excel. Su computer diversi con versioni diverse di Windows e Office, questo file potrebbe trovarsi in cartelle diverse, quindi ecco alcuni modi per scoprirne la posizione:

• Fare clic con il pulsante destro del mouse sull'icona (scorciatoia) per avviare Excel sul desktop o nella barra delle applicazioni e selezionare il comando Materiali (Proprietà), quindi nella finestra che si apre, copia il percorso dalla riga Target:

                       

• Apri qualsiasi cartella di lavoro di Excel, quindi apri Gestione attività (Responsabile delle attività) spingendo Ctrl+altro+Da e cliccando con il tasto destro sulla riga Microsoft Excel, scegli un comando Materiali (Proprietà). Nella finestra che si apre, puoi copiare il percorso, senza dimenticare di aggiungere una barra rovesciata e EXCEL.EXE alla fine:

               

• Apri Excel, apri l'editor di Visual Basic con la scorciatoia da tastiera altro+F11, aprire il pannello immediato una combinazione di Ctrl+G, inserisci il comando al suo interno:

  ? Percorso.Applicazione

  ... e fare clic su entrare

  

  Copia il percorso risultante, senza dimenticare di aggiungere una barra rovesciata e EXCEL.EXE alla fine.

Nel Aggiungi argomenti (facoltativo) (Aggiungi argomenti (facoltativo)) è necessario inserire il percorso completo del libro con la macro che vogliamo aprire.

Quando tutto è inserito, quindi fare clic Avanti e poi Fine (Finire). L'attività deve essere aggiunta all'elenco generale:

È conveniente gestire l'attività creata utilizzando i pulsanti a destra. Qui puoi testare l'attività eseguendola immediatamente (correre)senza aspettare il tempo specificato. È possibile disattivare temporaneamente un'attività (Disattivare)in modo che smetta di funzionare per un periodo di tempo, come le tue vacanze. Bene, puoi sempre modificare i parametri (date, ora, nome file) tramite il pulsante Materiali (Proprietà).

Aggiungi una macro per aprire un file

Ora resta da appendere nel nostro libro il lancio della macro di cui abbiamo bisogno sull'evento di apertura del file. Per fare ciò, apri il libro e vai all'editor di Visual Basic usando la scorciatoia da tastiera altro+F11 o pulsanti Visual Basic linguetta sviluppatore (Sviluppatore). Nella finestra che si apre nell'angolo in alto a sinistra, devi trovare il nostro file sull'albero e fare doppio clic per aprire il modulo Questo libro (Questo quaderno di lavoro).

Se non vedi questa finestra nell'editor di Visual Basic, puoi aprirla tramite il menu Visualizza: Esplora progetti.

Nella finestra del modulo che si apre, aggiungi un gestore di eventi di apertura libro selezionandolo dagli elenchi a discesa in alto Cartella di lavoro и Apri, rispettivamente:

Sullo schermo dovrebbe apparire un modello di procedura. Cartella di lavoro_Apri, dove tra le righe Sottotitoli privati и End Sub ed è necessario inserire quei comandi VBA che dovrebbero essere eseguiti automaticamente all'apertura di questa cartella di lavoro di Excel, quando l'Utilità di pianificazione la apre in base alla pianificazione. Ecco alcune opzioni utili per l'overclock:

• ThisWorkbook.RefreshAll – Aggiorna tutte le query di dati esterni, le query Power Query e le tabelle pivot. L'opzione più versatile. Non dimenticare di consentire le connessioni a dati esterni per impostazione predefinita e di aggiornare i collegamenti tramite File – Opzioni – Centro protezione – Opzioni Centro protezione – Contenuto esterno, altrimenti, quando apri il libro, apparirà un avviso standard ed Excel, senza aggiornare nulla, attenderà la tua benedizione sotto forma di clic sul pulsante Abilitare il contenuto (Abilitare il contenuto):

  

• ActiveWorkbook.Connections ("Nome_connessione"). Aggiorna — aggiornamento dei dati sulla connessione Connection_Name.
• Fogli(“Foglio5“.Tabelle pivot(“Tabella pivot1«).PivotCache.Aggiorna – aggiornamento di una singola tabella pivot denominata Tabella pivot 1 sul foglio Sheet5.
• Applicazione.Calcola – ricalcolo di tutte le cartelle di lavoro Excel aperte.
• Application.CalculateFullRebuild – ricalcolo forzato di tutte le formule e ricostruzione di tutte le dipendenze tra le celle in tutte le cartelle di lavoro aperte (equivale a reinserire tutte le formule).
• Fogli di lavoro ("Report").Stampa – stampa foglio Foto.
• Chiama MyMacro – eseguire una macro denominata La mia macro.
• ThisWorkbook.Salva – salva il libro corrente
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Sostituisci(Ora, “:”, “-“) & “.xlsx” – salva il libro in una cartella D: Archivio sotto il nome Foto con data e ora aggiunte al nome.

Se si desidera che la macro venga eseguita solo quando il file viene aperto dallo Scheduler alle 5:00 e non ogni volta che l'utente apre la cartella di lavoro durante la giornata lavorativa, è opportuno aggiungere un controllo dell'ora, ad esempio:

Se Format(Now, "hh:mm") = "05:00" Allora ThisWorkbook.RefreshAll  

È tutto. Non dimenticare di salvare la cartella di lavoro in un formato abilitato alle macro (xlsm o xlsb) e puoi chiudere Excel in sicurezza e tornare a casa, lasciando il computer acceso. Ad un certo momento (anche se il PC è bloccato), lo Scheduler avvierà Excel e aprirà il file specificato in esso, e la nostra macro eseguirà le azioni programmate. E ti crogiolerai a letto mentre il tuo pesante rapporto viene automaticamente ricalcolato: bellezza! 🙂

• Cosa sono le macro, come usarle, dove inserire codice Visual Basic in Excel
• Come creare il tuo componente aggiuntivo macro per Excel
• Come utilizzare la cartella di lavoro delle macro personali come libreria per le macro in Excel