Cron Job für Excel mit VBA

Mithilfe des Befehls Application.OnTime stellt VBA eine Möglichkeit bereit, eine definierte Aktion nach einer definierten Zeitspanne wieder und wieder zu wiederholen.

Anwendungsbeispiel

Eine geöffnete Arbeitsmappe soll nach einer Zeitspanne, z.B. 15 Minuten gespeichert werden, ohne das der Anwender den Speichern-Button betätigt.

Code

Der Code ist in mehrere Makros aufgeteilt. Die Erklärung der einzelnen Code-Blöcke findet sich weiter unten.

Option Explicit
'
' Dieses Modul ist für das automatische Speichern der Arbeitsmappe verantwortlich
'
Dim iTimerSet As Double

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EndeUhr
End Sub

Public Sub SekundenZaehler()
    ThisWorkbook.Worksheets(1).Range("a1").Value = Format(Now, "hh:nn:ss")
    iTimerSet = Now + TimeValue("00:15:00")
    Application.OnTime iTimerSet, "SekundenZaehler" 'Selbstaufruf
    Application.OnTime iTimerSet, "SpeichernHinweis"
End Sub

Private Sub SpeichernHinweis()
Dim intX As Integer

intX = MsgBox("Lagerverwaltung speichern?", vbQuestion + vbYesNo, "Speichern?")
    If intX = vbYes Then
        Call DateiDoppeltSpeichern
        MsgBox "Dokument gespeichert " & Format(Now, "hh:nn:ss"), vbInformation
    End If

End Sub


Public Sub EndeUhr()
    On Error Resume Next 
    Application.OnTime iTimerSet, "SekundenZaehler", , False
End Sub

Erklärung der einzelnen Code-Blöcke

Das Makro beendet nach dem Schließen die Zählfunktion des Makros SekundenZaehler(). Es ruft beim Schließen der Arbeitsmappe das Makro EndeUhr() auf.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Beim Schließen der Arbeitsmappe dafür, dass unsere Stop Prozedur ausgeführt wird.
'
    EndeUhr
End Sub

Public Sub EndeUhr()
    On Error Resume Next 
    Application.OnTime iTimerSet, "SekundenZaehler", , False
End Sub

Das eigentliche Makro das Application.OnTime enthält und für das wiederholte Auslösen einer definierten Aktion nach einer bestimmten Zeit verantwortlich ist, lautet:

Dim iTimerSet As Double
Public Sub SekundenZaehler()
    ThisWorkbook.Worksheets(1).Range("a1").Value = Format(Now, "hh:nn:ss")
    iTimerSet = Now + TimeValue("00:00:20")
    Application.OnTime iTimerSet, "SekundenZaehler" 'Selbstaufruf
    Application.OnTime iTimerSet, "SpeichernHinweis"
End Sub

• Die Variable Dim iTimerSet As Double ist zwingend direkt nach Option Explicit zu setzen, da sie von zwei Makros verwendet wird.
• ThisWorkbook.Worksheets(1).Range("a1").Value = Format(Now, "hh:nn:ss"): Dieser Befehlt schreibt in die erste Tabelle in Zelle A1 die Zeit, wann das Makro aufgerufen wird. Also es gibt nach dem ersten Speichern der Arbeitsmappe die Zeit des letzten Speicherzeitpunkts aus. Die Funktion hat nichts mit der Funktion des Makros zu tun, sie dient lediglich der Benutzerfreundlichkeit.
• iTimerSet = Now + TimeValue("00:15:00"): Hier wird festgelegt, wann das Makro ausgeführt werden soll. Das Zeitformat ist Stunden:Minuten:Sekunden. Hier im Beispiel wird das Makro also alle 15 Minuten aufgerufen und ausgeführt.
• Application.OnTime iTimerSet, "SekundenZaehler": Dieser Befehl ruft sorgt dafür, dass sich das Makro selbst aufruft und somit alle 15 Minuten wieder ausgeführt wird. Wird der Befehl auskommatiert, dann wird das Makro nur 1x aufgerufen.
• Application.OnTime iTimerSet, "SpeichernHinweis": Ruft das Makro SpeicherHinweis auf, dass die weiteren Befehle enthält.

Das letzte Makro im Bunde trägt dafür Sorge, dass die Arbeitsmappe auch gespeichert wird.

Private Sub SpeichernHinweis()
Dim intX As Integer

intX = MsgBox("Lagerverwaltung speichern?", vbQuestion + vbYesNo, "Speichern?")
    If intX = vbYes Then
        Call DateiDoppeltSpeichern
        MsgBox "Dokument gespeichert " & Format(Now, "hh:nn:ss"), vbInformation
    End If

End Sub

Was macht das Makro? Es wird von SekundenZaehler durch den Befehl Application.OnTime iTimerSet, "SpeichernHinweis" aufgerufen und dann wird die definierte Prozedur durchlaufen

• Es erscheint eine Messagebox, die den Anwender frägt, ob er das Dokument speichern möchte. Der Dialog bietet zwei Buttons an, einmal die Bestätigung mit Ja und den Abbruch mit Nein.
• Drückt der Anwender Ja, dann wird das Makro DateiDoppeltSpeichern aufgerufen (Das Makro DateiDoppeltSpeichern wird im Beitrag ausführlich erklärt).
• Wurde das Makro DateiDoppeltSichern korrekt durchlaufen, dann wird dem Anwender eine Hiweisbox ausgegeben, die ihn über das erfolgreiche Speichern der Arbeitsmappe informiert.
• Drückt der Anwender aber den Nein-Button, dann wird der Speichervorgang abgebrochen und die Wiederholung erfolgt nach 15 Minuten

Möchte man dem Anwender nicht die Entscheidung überlassen, ob er das Dokument speichern möchte oder nicht, dann ist das Makro SpeichernHinweis entsprechend abzuändern:

Sub SpeichernHinweis()
Call DateiDoppeltSpeichern
MsgBox "Dokument gespeichert " & Format(Now, "hh:nn:ss")
End Sub

Wie sorgt man aber nun dafür, dass das Makro auch bei jedem Öffnen der Arbeitsmappe aufgerufen und ausgeführt wird? Mann will ja schließlich, dass alles automatisch abläuft. Die Lösung, man fügt das Makro mit der Call-Aufforderung in das Makro Sub Auto_Open() ein (Das Makro DateiDoppeltSpeichern wird im Beitrag ausführlich erklärt).

siehe auch

Eine sehr gute Beschreibung der Application.OnTime findet sich auf :arrow: http://www.online-excel.de/excel/singsel_vba.php?f=133

Gib mir gerne einen Kaffee ☕ aus ❗️

Wenn dir meine Beiträge gefallen und geholfen haben, dann kannst du mir gerne einen Kaffee ☕️ ausgeben.

•

Bitcoin Address: bc1qfuz93hw2fhdvfuxf6mlxlk8zdadvnktppkzqzj

Follow Me❗️

Mastodon • Mastodon RSS • codeberg.org/sst • RSS