To Do mit VBA und Power Automate steuern

Lies in den Artikel rein und unten bekommst Du ein unschlagbares Angebot!

In einem weiteren Artikel namens “To Do-Aufgabe mit Power Automate und VBA anlegen” (www.vbentwickler.de/431) haben wir die Grundlagen zur Steuerung von Microsoft To Do mit VBA über Power Automate beschrieben und einen ersten Anwendungsfall vorgestellt – das Anlegen einer Aufgabe für eine vorgegebene Liste. In diesem Artikel haben wir die wichtigsten Vorbereitungen getroffen, nämlich das Anlegen eines Power Automate Kontos und das Freigeben des Zugriffs auf das To Do-Konto, dessen Listen und Aufgaben wir verwalten wollen. Dabei schauen wir uns in diesem Artikel an, wie wir die Aufrufe noch genauer gestalten können, um beispielsweise die Liste einzustellen, der wir eine neue Aufgabe hinzufügen. Außerdem schauen wir uns an, wie wir Aufgaben auslesen, bearbeiten oder löschen können und wie wir Listen auslesen, anlegen, bearbeiten oder löschen können. Es gibt viel zu tun!

Wir starten direkt dort, wo wir im oben genannten Artikel aufgehört haben, nämlich beim Anlegen einer Aufgabe. In dem dort verwendeten Code haben wir beispielsweise noch nicht die Liste dynamisch angegeben, in die wir die neue Aufgabe übertragen wollen. Wir konnten diese nur aus den vorhandenen Listen auswählen, was wir aber gern direkt von der VBA-Anwendung aus erledigen würden. Dazu müssen wir die Listen und ihre Eigenschaften auswählen, was wir nun erledigen.

Neuen Flow anlegen

Dazu legen wir einen neuen Flow mit der Vorlage Sofortiger Cloud-Flow (siehe Bild 1) und dem Typ Beim Empfang einer HTTP-Anforderung an.

Bild 1: Anlegen eines sofortigen Cloud-Flows

Hier brauchen wir nichts weiter festzulegen – wir wollen alle Listen von To Do abfragen, dazu benötigen wir keine Requestparameter. Danach fügen wir mit einem Klick auf das Plus-Zeichen und anschließender Auswahl von Aktion hinzufügen die Aktion zum Abfragen der To Do-Listen hinzu (siehe Bild 2). Hier sehen wir bereits das Ergebnis.

Bild 2: Hinzufügen einer Aktion

Vorher wählen wir jedoch noch aus dem Bereich Eine Aktion hinzufügen die gewünschte Aktion hinzu. Dazu geben wir als Suchbegriff Aufgabe ein und scrollen dann nach unten, bis wir den Connector Microsoft To-Do finden. Hier klicken wir auf Mehr anzeigen und finden alle Aktionen für Microsoft To-Do vor (siehe Bild 3).

Bild 3: Alle Microsoft To-Do-Aktionen

Hier wählen wir den Eintrag Alle Aufgabenlisten auflisten aus. Dessen Eigenschaften erscheinen nun auf der linken Seite. Auf der Seite Parameter erhalten wir wie bereits angedeutet den Hinweis, dass keine zusätzlichen Informationen nötig sind. Im unteren Bereich sollten wir, wenn wir bereits die Schritte aus dem eingangs erwähnten Artikel ausgeführt haben, die Information vorfinden, dass wir bereits mit Microsoft To-Do verbunden sind. Wir brauchen auch bei diesem Schritt nichts weiter zu tun (siehe Bild 4).

Bild 4: Einstellungen für die neue Aktion

Wir können den Flow nun speichern und das wird auch ohne Probleme gelingen. Damit können wir nun zur ersten Aktion beziehungsweise zum Trigger mit der Beschriftung manual zurückkehren. Klicken wir diesen an, sehen wir nun unter HTTP-URL die URL, die wir aufrufen müssen. Hier müssen wir außerdem noch prüfen, ob Wer kann den Flow auslösen den Wert Jeder aufweist (siehe Bild 5).

Bild 5: Prüfen des Auslösers und Abholen der URL

Im Gegensatz zum Beispiel aus dem eingangs erwähnten Artikel wollen wir hier eine Antwort erhalten, die über einen Status mit einer Erfolgsmeldung hinausgeht. Dazu fügen wir noch eine weitere Aktion hinzu. Diese heißt Antwort und wir finden diese wie in Bild 6.

Bild 6: Anlegen der Antwort-Aktion

Antwort als Eigenschaft hinzufügen

Hier müssen wir allerdings Hand anlegen. Die Parameter der Antwort-Aktion sehen wir in Bild 7. Wichtig sind hier die Eigenschaften Status Code und Body. Wenn wir den Flow aufrufen, ohne diese letzte Aktion hinzuzufügen, bekommen wir immer den Status 202 zurück – ohne einen Response-Text. Hier legen wir nun fest, dass wir den Status 200 erhalten, wenn dieser Schritt erreicht wird, und mit der Eigenschaft Body definieren wir, welchen Inhalt wir zurückerhalten wollen. Deshalb klicken wir auf die Schaltfläche mit dem Blitz und dem Text fx, um die verfügbaren Elemente anzuzeigen.

Bild 7: Eintragen des Body-Wertes

Diese sehen wir nun in Bild 8. Hier sehen wir alle möglichen Ergebnisse der Aktion Alle Aufgabenlisten auflisten. Wir wählen den Eintrag Textkörper aus. Mit allen anderen Einträgen konnten wir den Flow nicht ohne Fehlermeldungen speichern. Nun gelingt dies jedoch, und wir können uns dem Aufruf per VBA zuwenden.

Bild 8: Hinzufügen des Elements Textkörper zur Eigenschaft Body der Antwort

Alle Aufgabenlisten per VBA ermitteln

Nachdem wir uns so praktisch unseren eigenen maßgeschneiderten Webservice gebaut haben, können wir diesen per VBA aufrufen. Um diese optimal verarbeiten zu können, wollen wir jedoch noch zwei Voraussetzungen schaffen. Als Erstes benötigen wir einen Verweis auf die Bibliothek Microsoft Scripting Runtime. Außerdem fügen wir zwei Module namens mdlJSON und mdlJSONDOM zum VBA-Projekt hinzu. Diese enthalten wichtige Funktionen zum Auswerten der Antwort des Webservices.

In der Prozedur GetToDoLists aus Listing 1 rufen wir den Webservice auf. Hier deklarieren wir einige Variablen und weisen dann der Variablen strURL die URL zu, die wir weiter oben dem Element manual entnommen haben. Dann rufen wir diese URL. Liefert diese den Wert 200 als Status zurück, war der Aufruf erfolgreich. Dann können wir den Inhalt der Eigenschaft responseText des XMLHTTP-Objekts in die Variable strResponse schreiben. Da es sich hierbei um ein JSON-Dokument handelt, verwenden wir die Funktion GetJSONDOM, um die Referenzen zu den einzelnen Inhalten des Dokuments im Direktbereich auszugeben. Dieser sieht für das erste Element wie folgt aus:

Sub GetToDoLists()
     Dim objXMLHTTP As MSXML2.ServerXMLHTTP60
     Dim strUrl As String
     Dim strResponse As String
     Dim objJSON As Object
     Set objXMLHTTP = New MSXML2.ServerXMLHTTP60
     strUrl = "https://prod2-25.germanywestcentral.logic.azure.com:443/workflows/..."
     With objXMLHTTP
         .Open "POST", strUrl, False
         .setRequestHeader "Content-Type", "application/json"
         .send "{}"
     End With
     Select Case objXMLHTTP.status
         Case 200
             strResponse = objXMLHTTP.responseText
             Set objJSON = ParseJson(strResponse)
             Debug.Print GetJSONDOM(strResponse, True)
         Case Else
             MsgBox "Fehler: " & objXMLHTTP.status & " - " & objXMLHTTP.statusText
     End Select
     Set objXMLHTTP = Nothing
End Sub

Listing 1: Prozedur zum Ermitteln aller Aufgabenlisten von To Do

objJSON.Item(1).Item("@odata.etag"): W/"5kiLK5VJy0..."
objJSON.Item(1).Item("displayName"): Tasks
objJSON.Item(1).Item("isOwner"): Wahr
objJSON.Item(1).Item("isShared"): Falsch
objJSON.Item(1).Item("wellknownListName"): defaultList
objJSON.Item(1).Item("id"): AQMkADAwATNiZmYAZC05M2I2LTRiYTgtMDACLTAwCgAuAAADKTYDS538L0CXniiMkbeMfQEA5kiLK5VJy0Cml8itxoUMYQAAAgESAAAA

Uns interessieren primär der Anzeigename (displayName) und die id. Letztere benötigen wir, um gezielt die Aufgaben einer Liste zu bearbeiten.

Wir haben den Inhalt mit der Funktion ParseJSON ausgelesen und in eine Objektstruktur geschrieben, die wir mit der Variablen objJSON referenzieren. Über diese können wir die enthaltene Werte nun mit den von GetJSONDOM gelieferten Referenzen ermitteln.

Wenn wir GetJSONDOM nun durch die folgende Schleife ersetzen, erhalten wir alle Anzeigenamen plus ID im Direktbereich:

Dim i As Integer
For i = 1 To objJSON.Count
     Debug.Print objJSON.Item(i).Item("displayName"), _
        objJSON.Item(i).Item("id")
Next i

Damit können wir arbeiten – wir können die Informationen nun beispielsweise in eine Access- oder Excel-Tabelle schreiben, um weiter damit zu arbeiten.

Aufgaben einer Aufgabenliste abrufen

Im nächsten Schritt wollen wir zu einer dieser Aufgabenlisten alle Aufgaben ausgeben. Dazu legen wir einen neuen Flow an. Das ist bereits deutlich anspruchsvoller, wie wir gleich sehen werden.

Grundsätzlich ist der Aufbau des Flows ähnlich: Wir verwenden wieder das manual-Objekt zum Start, dann eines, das die Einträge liefert und am Ende die Antwort. Allerdings können wir die Einträge nicht so einfach wie zuvor als Antwort zurückschicken, sondern müssen diese zwischendurch noch prozessieren. Das erscheint gleich in der Beschreibung leicht, aber da wir im Web kein Beispiel dafür gefunden haben und alles selbst herausfinden mussten, sind dafür einige Stunden draufgegangen.

Der vollständige Flow wird später wie in Bild 9 aussehen. Wir starten mit dem Request-Element, dem wir mitteilen, welche Liste ausgelesen werden soll und wie viele Einträge wir maximal erhalten wollen. Dann folgt das eigentliche Auslesen durch die Aktion Aufgaben nach Ordner auflisten. Die damit erhaltenen Informationen müssen wir allerdings noch verarbeiten. Dazu erstellen wir eine Array-Variable, die wir in der folgenden Schleife mit den Elementen der gefundenen Aufgaben füllen. Mit der Verfassen-Aktion bereiten wir die Inhalte zur Rückgabe als Response vor und geben diese schließlich mit dem Response-Element zurück.

Bild 9: Vollständiger Flow zum Abfragen der Aufgaben einer Aufgabenliste

Dies schauen wir uns nun im Detail an.

Trigger für den Request hinzufügen

Wir starten mit einem neuen Flow, für den wir wieder den Typ Beim Empfang einer HTTP-Aufforderung. Für diesen legen wir unter Wer kann den Flow auslösen wieder Jeder fest.

Für JSON-Schema des Anforderungstexts hinterlegen wir das folgende JSON-Dokument, mit dem wir diesmal nicht nur die listId abfragen, sondern mit count auch die maximale Anzahl der zurückzugebenden Elemente:

{
   "properties": {
     "listId": {
       "type": "string"
     },
     "count": {
       "type": "integer"
     }
   },
   "type": "object"
}

Aktion zum Abfragen der Aufgaben hinzufügen

Über das Pluszeichen fügen wir nun ein neues Element hinzu. Leider ist die Suche nach Aktionen so schlecht, dass wir uns behelfen, indem wir das Suchwort Aufgaben eingeben und etwas nach unten scrollen, bis wir die Gruppe Microsoft To-Do finden. Diese enthält auch die Aktion Aufgaben nach Ordner auflisten, die wir durch Anklicken hinzufügen.

Hier finden wir in den Eigenschaften unter Parameter die beiden Parameter To-Do-List und Höchstzahl. Wenn wir das Nachschlagfeld für To-Do-Liste öffnen, sehen wir alle To-Do-Listen für das angegebene Konto und ganz unten den Eintrag Benutzerdefinierten Wert eingeben. Dies blendet die Schaltfläche aus Bild 10 ein.

Bild 10: Weitere Auswahlmöglichkeiten

Klicken wir auf den Blitz oben, erhalten wir die aus den bisherigen Elementen verfügbaren Parameter, hier listId und count aus dem manual-Element (siehe Bild 11). Hier wählen wir folglich das Element listId für To-Do-Liste aus und count für Höchstanzahl und haben so schon alle Einstellungen für diese Aktion festgelegt.

Bild 11: Hinzufügen der Parameter

Du solltest an dieser Stelle auch noch prüfen, ob du mit einem Microsoft To-Do-Konto verbunden bist und dies gegebenenfalls an der Stelle aus Bild 12 erledigst.

Bild 12: Prüfen der Verbindung mit Microsoft To-Do

Array-Variable initialisieren

Im vorherigen Beispiel konnten wir an dieser Stelle bereits die Antwort-Aktion hinzufügen. Das ist hier noch nicht möglich, denn wir müssen die Elemente des Ergebnisses der Aufgaben nach Ordner auflisten-Aktion noch aufbereiten.

Dazu geben wir als Suchbegriff Variable an und wählen dort den Eintrag Variable initialisieren aus (siehe Bild 13).

Bild 13: Hinzufügen der Aktion Variable initialisieren

Für dieses neue Variable-Element stellen wir nun die Parameter ein (siehe Bild 14). Hier finden wir den Parameter Name, dem wir den Wert tasksArray zuweisen. Der Parameter Type erhält den Wert Array und für Value stellen wir mit einem eckigen Klammernpaar ein leeres Array ein.

Bild 14: Einstellen der Parameter

Schleife über alle Aufgaben

Nun benötigen wir eine Schleife, in der wir alle Aufgaben in die Array-Variable eintragen. Diese finden wir unter dem Namen Auf alle anwenden im Bereich Control (siehe Bild 15).

Bild 15: Hinzufügen der Aktion Auf alle anwenden

In den Parametern dieses neuen Elements legen wir nun fest, welchen Output eines vorherigen Elements wie verarbeiten wollen. Hier wählen wir den Eintrag Textkörper aus Aufgaben nach Ordner auflisten aus (siehe Bild 16).

Bild 16: Auswählen des Outputs, der in der Schleife verarbeitet werden soll

Danach müssen wir noch festlegen, was genau mit diesen Inhalten geschehen soll. Dazu klicken wir auf die Plus-Schaltfläche innerhalb des Auf alle anwenden-Elements (siehe Bild 17) und wählen die Aktion hinzufügen-Anweisung aus.

Bild 17: Hier fügen wir noch ein Element hinzu.

Hier suchen wir im Suchen-Feld wieder nach dem Schlüsselwort Variable und wählen diesmal den Eintrag An Arrayvariable anfügen aus.

Hier sind nun zwei Parameter festzulegen. Für Name wählen wir den Namen der Variable aus, also tasksArray. Einen anderen Eintrag gibt es auch nicht.

Für Value klicken wir wieder auf das Blitz-Symbol und finden dann eine ganze Reihe von Elementen vor, wenn wir für Aufgaben nach Ordner auflisten auf Mehr anzeigen (19) klicken (siehe Bild 18).

Bild 18: Auswahl der Eigenschaften der Aufgabe

Aber können wir hier nun nur ein Element auswählen? Was ist, wenn wir beispielsweise die ID und den Titel selektieren wollen? Das ist kein Problem: Wir können beliebig viele Elemente auswählen und diese nacheinander per Mausklick zur Eigenschaft Value hinzufügen.

In diesem Fall wollen wir uns jedoch mit ID und Titel begnügen, damit die Rückgabe überschaubar bleibt.

Das reicht allerdings noch nicht aus, denn so werden die Elemente einfach in einem JSON-Dokument aneinandergehängt. Wir bearbeiten also die beiden Elemente in der Eigenschaft Value, indem wir die Struktur eines JSON-Dokuments hinzufügen – hier in Form von geschweiften Klammern und den Namen für die Elemente, hier id und title (siehe Bild 19).

Bild 19: Strukturieren der Eigenschaften der Aufgabe in einem JSON-Dokument

Verfassen der Antwort

Nach der Schleife fügen wir eine weitere Aktion hinzu. Diese finden wir über die Suche nach dem Begriff Verfassen sehr schnell und fügen sie hinzu.

Für den Parameter Eingaben wählen wir über die durch Anklicken des Blitz-Icons erscheinende Variable tasksArray aus.

Hinzufügen des Antwort-Elements

Schließlich benötigen wir noch das Antwort-Element selbst, das wir nach Eingabe des Suchbegriffs Antwort im Bereich Anforderung finden.

Hier behalten wir den Wert 200 für den Parameter Status Code bei und fügen für Body den Wert Verfassen|Ausgaben hinzu.

Speichern des Flows

Ob wir alles korrekt angelegt haben, erfahren wir, wenn wir den Flow mit einem Klick auf die entsprechende Schaltfläche speichern. Wenn dies gelingt, können wir ihn mit der im Anschluss vorgestellten VBA-Prozedur testen.

Diese finden wir in Listing 2. Die Prozedur GetTaskOfAList nimmt mit dem Parameter strListID die ID der Liste entgegen, deren To Do-Aufgaben ermittelt werden sollen. Folglich benötigen wir noch einen Aufruf dieser Prozedur, mit der wir die ID ermitteln und übergeben. Hier haben wir die ID bereits vorab ermittelt und übergeben diese als Parameter:

Sub GetTasksOfAList(strListID As String)
     Dim objXMLHTTP As MSXML2.ServerXMLHTTP60
     Dim strUrl As String
     Dim strRequest As String
     Dim strResponse As String
     Dim objJSON As Object
     Dim i As Integer
     Set objXMLHTTP = New MSXML2.ServerXMLHTTP60
     strUrl = "https://prod2-11.germanywestcentral.logic.azure.com:443/workflows/2641fd9330094a83bf60f40088c..."
     With objXMLHTTP
         .Open "POST", strUrl, False
         .setRequestHeader "Content-Type", "application/json"
         strRequest = "{""listId"": """ & strListID & """, ""count"":999}"
         .send strRequest
     End With
     Select Case objXMLHTTP.status
         Case 200
             strResponse = objXMLHTTP.responseText
             Set objJSON = ParseJson(strResponse)
             For i = 1 To objJSON.Count
                 Debug.Print objJSON.Item(i).Item("id"), objJSON.Item(i).Item("title")
             Next i
         Case Else
             MsgBox "Fehler: " & objXMLHTTP.status & " - " & objXMLHTTP.statusText
     End Select
     Set objXMLHTTP = Nothing
End Sub

Listing 2: Prozedur zum Ermitteln aller Aufgaben einer Liste in To Do

Public Sub Test_GetTasksOfAList()
     Dim strListID As String
     strListID = "AQMkADAwATNiZmYAZC05M2I2LTRiYTgtMD..."
     GetTasksOfAList strListID
End Sub 

Die Prozedur verwendet eine Variable namens strURL, der wir noch die URL zuweisen müssen, die wir für den Aufruf des Flows verwenden können. Diese wurde beim Speichern des Flows im Element manual im Parameter HTTP-URL hinterlegt. Dort können wir sie herauskopieren und für strUrl einfügen. Dann rufen wir die URL mit dem objXMLHTTP-Objekt auf und übergeben neben der Methode POST die URL. Außerdem setzen wir den üblichen Request-Header. Für die Send-Methode stellen wir vorab in der Variablen strRequest ein JSON-Dokument mit der listId und der maximalen Anzahl (count) zusammen.

Danach werten wir das Ergebnis, zuerst die status-Eigenschaft. Diese liefert optimalerweise den Wert 200, dann hat alles funktioniert. In diesem Fall lesen wir den Inhalt der Eigenschaft responseText in die Variable strResponse ein. Wir verwenden die ParseJSON-Funktion, um aus dem zurückgelieferten JSON-Dokument eine Objektstruktur zu machen, die wir nach bewährter Manier über das mit GetJSONDOM einzusehende Objektmodell auslesen können.

In diesem Fall durchlaufen wir in einer Schleife von 1 bis zur Anzahl der Element von objJSON die mit objJSON.Item(i).Item(“id”) und objJSON.Item(i).Item(“title”) referenzierten IDs und Titel der Aufgaben. Diese geben wir hier der Einfachheit halber im Direktbereich des VBA-Editors aus.

Sollte ein Fehler auftreten und der Flow liefert nicht den Wert 200 zurück, geben wir den gelieferten Status plus Text in einem Meldungsfenster aus.