Anzahl verarbeiteter Datensätze mit VBA protokollieren

Heute stelle ich Ihnen eine Möglichkeit vor, die Anzahl der verarbeiteten Datensätze fortlaufend zu zählen. Das ist insbesondere dann sehr hilfreich, wenn Sie kostenpflichtige Webservices wie zum Beispiel die Google Maps API nutzen. Sie erhalten so einen sehr einfachen Überblick über die Anzahl der abgefragten Datensätze und können entsprechend die anfallenden Kosten kalkulieren. Auch ist es möglich einen monatliches Abfragebudget zu definieren, das nicht überschritten werden soll. Darüber hinausgehende Anfragen an die API-Schnittstelle können anschließend unterbunden werden.

Die nachfolgend vorgestellten VBA Routinen beinhalten allerdings nur die notwendigen Hilfsmittel um entsprechende Datenabfragen zu zählen und zu protokollieren. Das Beispiel beinhaltet kein Makro um tatsächlich eine Anfrage an die Google Maps API zu senden. Das sollte an dieser Stelle nur ein Hinweis für ein praktisches Anwendungsbeispiel sein.

VBA Routinen zum Messen der verarbeiteten Datensätze

Im Wesentlichen werden für das zählen und protokollieren der verarbeiteten Datensätze zwei VBA Routinen zusätzlich zur eigentlichen VBA Routine für die Verarbeitung der Datensätze benötigt. Bevor die Datensätze verarbeitet werden, wird zunächst der Stand der bereits verarbeiteten Datensätze aus einer externen Datei eingelesen. Hierfür wird im Beispiel eine einfache unformatierte Textdatei im TXT-Format genutzt. Reihenfolge der Verarbeitung:

1. Aktuellen Verarbeitungsstand aus Textdatei ermitteln (Prozedur: TXT_Lesen)
2. Verarbeitung der Datensätze durchführen (Prozedur: DatensaetzeVerarbeiten)
3. Neuen Verarbeitungsstand zurück in die Textdatei schreiben. (Prozedur: TXT_Schreiben)

Sub TXT_Lesen()

Dim strDateiname As String, strPfad As String, strDatei As String
Dim FSO As Object, Datei As Object
Dim fsoTS As TextStream

strPfad = "C:\Excel\" 'Speicherpfad eintragen
strDateiname = Year(Now) & "-" & Month(Now) & ".txt" 'Dateinamen mit Dateiendung eintragen
strDatei = strPfad & strDateiname

Set FSO = CreateObject("Scripting.FileSystemObject")

If Dir(strDatei) <> "" Then
    Set fsoTS = FSO.OpenTextFile(strDatei, ForReading, False)
    lngZaehler = fsoTS.ReadLine
    fsoTS.Close
Else
    Set Datei = FSO.CreateTextFile(strDatei, True)
    Datei.Close
    Open strDatei For Output As #1
    Print #1, "0"
    Close #1
    lngZaehler = 0
End If

End Sub

Im Code wird monatlich eine neue Textdatei erzeugt und nach dem Muster JJJJ-MM.txt benannt. So wird jeweils innerhalb eines Monats bei jeder Verarbeitung eines oder mehrerer Datensätze der Inhalt dieser Datei hochgezählt. Entsprechend können Sie auf diesem Weg durch eine Anpassung in der VBA Routine ein Monatslimit setzen, ab dem dann bis zum Monatsende keine weiteren Anfragen mehr an die genutzte Schnittstelle gesendet werden. Im nächsten Monat wird dann in der neuen Datei wieder von null gezählt. Natürlich kann alternativ auch täglich, wöchentlich, quartalsweise oder jahresweise eine neue Datei erstellt werden. Das hängt ganz vom jeweiligen Einsatzzweck ab.

Während der Verarbeitung der Datensätze, zum Beispiel beim Abfragen der Daten über die Google Maps API, soll dann der zuvor eingelesene Zähler je Datenanfrage um eins hochgesetzt werden. Dafür nutzen wir eine globale Variable, die über die einzelnen VBA Routinen hinweg den ihr zugewiesenen Wert speichert.

Global lngZaehler As Long

Nachdem die Datenabfragen oder Verarbeitungsschritte durchgeführt wurden, wird der aktuelle Wert der Variable lngZaehler über die abschließende VBA Routine mit dem neuen Wert zurück in die Textdatei geschrieben und gespeichert.

Sub TXT_Schreiben()

Dim strDateiname As String, strPfad As String, strDatei As String
Dim FSO As Object, Datei As Object
Dim fsoTS As TextStream

strPfad = "C:\Excel\" 'Speicherpfad eintragen
strDateiname = Year(Now) & "-" & Month(Now) & ".txt" 'Dateinamen mit Dateiendung eintragen
strDatei = strPfad & strDateiname

Set FSO = CreateObject("Scripting.FileSystemObject")

If Dir(strDatei) <> "" Then
    Set Datei = FSO.CreateTextFile(strDatei, True)
    Datei.Close
    Open strDatei For Output As #1
    Print #1, Trim(lngZaehler)
    Close #1
End If

End Sub

Verarbeitung der Datensätze

In der Mustermappe ist die Funktion in einer ganz einfachen Beispielverarbeitung integriert. Über den Button „Datensätze verarbeiten“ starten Sie den Prozess. Dabei wird zunächst die VBA Routine TXT_Lesen() gestartet und eine entsprechende Textdatei im Ordnerpfad C:\Excel\ gesucht. Bitte ändern Sie den Ordnerpfad ab, oder erstellen einen entsprechenden Ordner im Laufwerk C:\ bevor Sie das Makro ausführen.

Die Verarbeitung der vorhandenen Datensätze in den Spalten A und B beinhaltet hier nur das hochzählen der Verarbeitungen in Spalte B. Das heißt, bei jedem ausführen des Makros wird Zeile für Zeile der Wert in B2, B3 etc. um eins hochgezählt. Sie verarbeiten also 10 Datensätze je Ausführung. Nach dem ersten ausführen des Makros beinhaltet die Textdatei dann den Inhalt 10. Führen Sie das Makro ein zweites Mal aus, werden dieselben 10 Datensätze erneut „verarbeitet“. Der Inhalt der Textdatei enthält anschließend entsprechend den Wert 20. Je Klick auf den Button erhöht sich der Wert in der Textdatei also um 10 entsprechend der 10 enthaltenen Datensätze in der Mustermappe. Sie können zum Test auch gerne die Anzahl der Datensätze in Spalte A erweitern oder reduzieren und dann erneut das Makro ausführen. Sie werden sehen, dass sich dann der Wert in der Textdatei abweichend um die dann enthaltenen Datensätze erhöht. Und abschließend noch der Hinweis, dass die Bezeichnung der Datensätze in Spalte A keinen Einfluss auf die Zählung haben. Sie können hier beliebig andere Inhalte eintragen.

Excel Mustermappe:

verarbeitete_datensaetze_protokollieren.xlsm