Ein Makro rechtzeitig ausführen

Ein in der Praxis sehr häufiger Fall: Sie müssen eines oder mehrere Ihrer Makros zu einem bestimmten Zeitpunkt oder in einer bestimmten Häufigkeit ausführen. Sie haben beispielsweise einen großen und umfangreichen Bericht, der eine halbe Stunde aktualisiert wird, und Sie möchten die Aktualisierung eine halbe Stunde vor Ihrer Ankunft am Morgen ausführen. Oder Sie haben ein Makro, das in bestimmten Abständen automatisch E-Mails an Mitarbeiter versenden soll. Oder wenn Sie mit einer PivotTable arbeiten, möchten Sie, dass sie alle 10 Sekunden aktualisiert wird und so weiter.

Schauen wir uns an, welche Möglichkeiten Excel und Windows haben, dies zu implementieren.

Ausführen eines Makros mit einer bestimmten Frequenz

Der einfachste Weg, dies zu tun, ist die Verwendung der integrierten VBA-Methode Anwendung.OnTimeEin, das das angegebene Makro zur angegebenen Zeit ausführt. Lassen Sie uns dies anhand eines praktischen Beispiels verstehen.

Öffnen Sie den Visual Basic-Editor über die gleichnamige Schaltfläche auf der Registerkarte Entwickler (Entwickler) oder Tastenkürzel Andere+F11, fügen Sie ein neues Modul über das Menü ein Einfügen – Modul und kopiere dort folgenden Code:

Dim TimeToRun 'globale Variable, in der die nächste Laufzeit gespeichert wird 'das ist das Hauptmakro Sub MyMacro() Application.Calculate 'das Buch neu berechnen Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill Zelle A1 mit einer zufälligen Farbe :) Rufe NextRun auf 'führe das NextRun-Makro aus, um die nächste Laufzeit festzulegen End Sub 'dieses Makro setzt die Zeit für die nächste Ausführung des Hauptmakros Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") '3 Sekunden zur aktuellen Zeit hinzufügen Application.OnTime TimeToRun, "MyMacro" 'den nächsten Lauf planen End Sub 'Makro zum Starten der Wiederholungssequenz Sub Start() Call NextRun End Sub 'Makro zum Stoppen der Wiederholungssequenz Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Lassen Sie uns herausfinden, was hier was ist.

Zuerst brauchen wir eine Variable, die die Zeit der nächsten Ausführung unseres Makros speichert – ich habe sie genannt Zeit zu rennen. Bitte beachten Sie, dass der Inhalt dieser Variablen für alle unsere nachfolgenden Makros verfügbar sein muss, also müssen wir es schaffen globale , dh ganz am Anfang des Moduls vor dem ersten deklarieren Sub.

Als nächstes kommt unser Hauptmakro Mein Makro, das die Hauptaufgabe erfüllt – das Buch mit der Methode neu zu berechnen Anwendung.Berechnen. Um es klarer zu machen, habe ich dem Blatt in Zelle A1 die Formel =TDATE() hinzugefügt, die das Datum und die Uhrzeit anzeigt – bei der Neuberechnung wird der Inhalt direkt vor unseren Augen aktualisiert (schalten Sie einfach die Sekundenanzeige in der Zelle ein Format). Für zusätzlichen Spaß habe ich dem Makro auch den Befehl hinzugefügt, Zelle A1 mit einer zufällig ausgewählten Farbe zu füllen (der Farbcode ist eine Ganzzahl im Bereich 0..56, die von der Funktion generiert wird Rnd und rundet auf eine ganzzahlige Funktion auf Int).

Makro WeiterRun addiert sich zum vorherigen Wert Zeit zu rennen 3 weitere Sekunden und plant dann die nächste Ausführung des Hauptmakros Mein Makro für diese neue Zeit. In der Praxis können Sie natürlich alle anderen Zeitintervalle verwenden, die Sie benötigen, indem Sie die Funktionsargumente festlegen Zeitwert im Format hh:mm:ss.

Und schließlich wurden der Einfachheit halber weitere Makros zum Starten von Sequenzen hinzugefügt. Home und seine Vollendung Endziel. Der letzte verwendet das vierte Methodenargument, um die Sequenz zu unterbrechen. Pünktlich gleich falsch.

Gesamt, wenn Sie das Makro ausführen Home, dann dreht sich dieses ganze Karussell und wir sehen das folgende Bild auf dem Blatt:

Sie können die Sequenz stoppen, indem Sie das entsprechende Makro ausführen Endziel. Der Einfachheit halber können Sie beiden Makros mit dem Befehl Tastenkombinationen zuweisen Makros – Optionen Tab Entwickler (Entwickler – Makros – Optionen).

Ausführen eines Makros nach einem Zeitplan

Natürlich ist alles oben Beschriebene nur möglich, wenn Microsoft Excel läuft und unsere Datei darin geöffnet ist. Betrachten wir nun einen komplizierteren Fall: Sie müssen Excel nach einem bestimmten Zeitplan ausführen, z. B. jeden Tag um 5:00 Uhr, einen großen und komplexen Bericht darin öffnen und alle darin enthaltenen Verbindungen und Abfragen aktualisieren, damit dies der Fall ist Sei bereit, wenn wir bei der Arbeit ankommen 🙂

In einer solchen Situation ist es besser zu verwenden Windows-Planer – ein Programm, das speziell in jede Windows-Version integriert ist und bestimmte Aktionen nach einem Zeitplan ausführen kann. Tatsächlich verwenden Sie es bereits, ohne es zu wissen, da Ihr PC regelmäßig nach Updates sucht, neue Antiviren-Datenbanken herunterlädt, Cloud-Ordner synchronisiert usw. Es ist die ganze Arbeit des Schedulers. Unsere Aufgabe besteht also darin, zu den vorhandenen Aufgaben eine weitere hinzuzufügen, die Excel startet und die darin angegebene Datei öffnet. Und wir werden unser Makro an das Ereignis hängen Arbeitsmappe_Öffnen diese Datei – und das Problem ist gelöst.

Ich möchte Sie gleich warnen, dass die Arbeit mit dem Scheduler möglicherweise erweiterte Benutzerrechte erfordert. Wenn Sie also die unten beschriebenen Befehle und Funktionen nicht auf Ihrem Arbeitscomputer im Büro finden, wenden Sie sich an Ihren IT-Spezialisten, um Hilfe zu erhalten.

Starten des Planers

Starten wir also den Scheduler. Dazu können Sie entweder:

• Klicken Sie mit der rechten Maustaste auf die Schaltfläche Startseite und wählen Sie Computer-Management (Computermanagement)
• Wählen Sie in der Systemsteuerung: Verwaltung – Taskplaner (Systemsteuerung – Verwaltung – Taskplaner)
• Wählen Sie aus dem Hauptmenü Start – Zubehör – Systemprogramme – Taskplaner
• Drücken Sie die Tastenkombination Win+R, eingeben taskschd.msc und drücken Sie Enter

Das folgende Fenster sollte auf dem Bildschirm erscheinen (ich habe eine englische Version, Sie können aber auch eine Version haben):

Erstellen Sie eine Aufgabe

Um eine neue Aufgabe mit einem einfachen Schritt-für-Schritt-Assistenten zu erstellen, klicken Sie auf den Link Erstellen Sie eine einfache Aufgabe (Basisaufgabe erstellen) in der rechten Leiste.

Geben Sie im ersten Schritt des Assistenten den Namen und die Beschreibung der zu erstellenden Aufgabe ein:

Klicken Sie auf die Schaltfläche Weiter (Nächster) und im nächsten Schritt wählen wir einen Auslöser – die Startfrequenz oder ein Ereignis, das unsere Aufgabe startet (z. B. das Einschalten des Computers):

Wenn Sie die Option Daily (Daily), dann müssen Sie im nächsten Schritt eine bestimmte Zeit, ein Startdatum der Sequenz und einen Schritt (jeden 2. Tag, 5. Tag usw.) auswählen:

Der nächste Schritt ist die Auswahl einer Aktion – Führen Sie das Programm (Programm starten):

Und schließlich ist das Interessanteste, was genau geöffnet werden muss:

Im Programm oder Skript (Programm/Skript) Sie müssen den Pfad zu Microsoft Excel als Programm eingeben, dh direkt zur ausführbaren Excel-Datei. Auf verschiedenen Computern mit unterschiedlichen Versionen von Windows und Office befindet sich diese Datei möglicherweise in unterschiedlichen Ordnern. Hier sind einige Möglichkeiten, wie Sie ihren Speicherort herausfinden können:

• Klicken Sie mit der rechten Maustaste auf das Symbol (Verknüpfung), um Excel auf dem Desktop oder in der Taskleiste zu starten, und wählen Sie den Befehl aus Materialien (Eigenschaften), und kopieren Sie dann in dem sich öffnenden Fenster den Pfad aus der Zeile Target:

                       

• Öffnen Sie eine beliebige Excel-Arbeitsmappe und öffnen Sie sie Task Manager (Taskmanager) Drücken Ctrl+Andere+Aus und durch Rechtsklick auf die Zeile Microsoft Excel, wählen Sie einen Befehl Materialien (Eigenschaften). In dem sich öffnenden Fenster können Sie den Pfad kopieren, Vergessen Sie nicht, einen umgekehrten Schrägstrich und EXCEL.EXE am Ende hinzuzufügen:

               

• Öffnen Sie Excel, öffnen Sie den Visual Basic-Editor mit einer Tastenkombination Andere+F11, offene Tafel unmittelbar eine Kombination aus Ctrl+G, geben Sie den Befehl ein:

  ? Anwendungs.Pfad

  … und klicken Sie auf Enter

  

  Kopieren Sie den resultierenden Pfad, Vergessen Sie nicht, einen umgekehrten Schrägstrich und EXCEL.EXE am Ende hinzuzufügen.

Im Argumente hinzufügen (optional) (Argumente hinzufügen (optional)) Sie müssen den vollständigen Pfad zum Buch mit dem Makro einfügen, das wir öffnen möchten.

Wenn alles eingetragen ist, dann klicken Weiter und dann Endziel (Fertig). Die Aufgabe sollte der allgemeinen Liste hinzugefügt werden:

Es ist bequem, die erstellte Aufgabe mit den Schaltflächen auf der rechten Seite zu verwalten. Hier können Sie die Aufgabe testen, indem Sie sie sofort ausführen (Lauf)ohne auf die angegebene Zeit zu warten. Sie können eine Aufgabe vorübergehend deaktivieren (Deaktivieren)damit es für einen bestimmten Zeitraum, z. B. Ihren Urlaub, nicht mehr ausgeführt wird. Nun, Sie können die Parameter (Datum, Uhrzeit, Dateiname) jederzeit über die Schaltfläche ändern Materialien (Eigenschaften).

Fügen Sie ein Makro hinzu, um eine Datei zu öffnen

Jetzt müssen wir noch den Start des Makros, das wir für das Ereignis zum Öffnen der Datei benötigen, in unser Buch aufnehmen. Öffnen Sie dazu das Buch und wechseln Sie über die Tastenkombination zum Visual Basic-Editor Andere+F11 oder Knöpfe Visual Basic Tab Entwickler (Entwickler). In dem Fenster, das sich in der oberen linken Ecke öffnet, müssen Sie unsere Datei im Baum finden und doppelklicken, um das Modul zu öffnen Dieses Buch (DieseArbeitsmappe).

Wenn Sie dieses Fenster im Visual Basic-Editor nicht sehen, können Sie es über das Menü öffnen Ansicht – Projekt-Explorer.

Fügen Sie im sich öffnenden Modulfenster einen Handler für das Öffnen eines Buches hinzu, indem Sie ihn aus den Dropdown-Listen oben auswählen Workbook и Offen, beziehungsweise:

Auf dem Bildschirm sollte eine Verfahrensvorlage erscheinen. Arbeitsmappe_Öffnen, wo zwischen den Zeilen Privatunter и End Sub und Sie müssen die VBA-Befehle einfügen, die automatisch ausgeführt werden sollen, wenn diese Excel-Arbeitsmappe geöffnet wird, wenn der Planer sie gemäß dem Zeitplan öffnet. Hier sind einige nützliche Optionen zum Übertakten:

• DieseArbeitsmappe.RefreshAll – Aktualisiert alle externen Datenabfragen, Power Query-Abfragen und PivotTables. Die vielseitigste Option. Vergessen Sie nur nicht, Verbindungen zu externen Daten standardmäßig zuzulassen und Links über zu aktualisieren Datei – Optionen – Trust Center – Trust Center-Optionen – Externer Inhalt, andernfalls erscheint beim Öffnen des Buches eine Standardwarnung und Excel wartet, ohne etwas zu aktualisieren, auf Ihren Segen in Form eines Klicks auf die Schaltfläche Inhalt aktivieren (Inhalt aktivieren):

  

• ActiveWorkbook.Connections("Verbindungsname").Aktualisieren — Aktualisieren von Daten auf der Connection_Name-Verbindung.
• Blätter („Blatt5“).PivotTables(“PivotTable1«).PivotCache.Aktualisieren – Aktualisieren einer einzelnen Pivot-Tabelle mit dem Namen PivotTable1 auf dem Blatt Sheet5.
• Anwendung.Berechnen – Neuberechnung aller geöffneten Excel-Arbeitsmappen.
• Application.CalculateFullRebuild – erzwungene Neuberechnung aller Formeln und Wiederherstellung aller Abhängigkeiten zwischen Zellen in allen geöffneten Arbeitsmappen (entspricht der Neueingabe aller Formeln).
• Arbeitsblätter („Bericht“).Ausdruck – Blatt drucken Fotos.
• Rufen Sie MyMacro an – Ausführen eines Makros mit dem Namen Mein Makro.
• DieseArbeitsmappe.Speichern – Aktuelles Buch speichern
  
• ThisWorkbooks.SaveAs „D:ArchiveReport“ & Replace(Now, „:“, „-“) & „.xlsx“ – Speichern Sie das Buch in einem Ordner D: Archiv unter dem Namen Fotos mit Datum und Uhrzeit an den Namen angehängt.

Wenn Sie möchten, dass das Makro nur ausgeführt wird, wenn die Datei um 5:00 Uhr vom Scheduler geöffnet wird, und nicht jedes Mal, wenn der Benutzer die Arbeitsmappe während des Arbeitstages öffnet, dann ist es sinnvoll, eine Zeitprüfung hinzuzufügen, zum Beispiel:

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

Das ist alles. Vergessen Sie nicht, Ihre Arbeitsmappe in einem makrofähigen Format (xlsm oder xlsb) zu speichern, und Sie können Excel sicher schließen und nach Hause gehen und Ihren Computer eingeschaltet lassen. Zu einem bestimmten Zeitpunkt (auch wenn der PC gesperrt ist) startet der Planer Excel und öffnet die angegebene Datei darin, und unser Makro führt die programmierten Aktionen aus. Und Sie werden im Bett schwelgen, während Ihr schwerer Bericht automatisch neu berechnet wird – Schönheit! 🙂

• Was sind Makros, wie werden sie verwendet, wo wird Visual Basic-Code in Excel eingefügt?
• So erstellen Sie Ihr eigenes Makro-Add-In für Excel
• So verwenden Sie die persönliche Makroarbeitsmappe als Bibliothek für Ihre Makros in Excel