VBA-Makros und RPA kombinieren

Mit Robotic Process Automation (RPA) lassen sich wiederkehrende Tätigkeiten automatisieren. Dieser Aspekt ist stark verwandt mit Makros - und RPA wird durchaus als nächste Stufe von Makros angesehen (insbesondere durch den Umstand, dass jede Applikation oder Datenquelle angebunden werden kann, nicht selten mittels Interaktion über das User Interface).

Die gängigen RPA-Tools verfügen über integrierte Excel-Aktivitäten. Auf einige Möglichkeiten mit der Lösung von "Kofax" wurde in diesem Beitrag bereits eingegangen. Die Abbildung unten zeigt einige Standard-Aktivitäten in der Software "UiPath", mit der das Beispiel in diesem Beitrag erarbeitet wird:

UiPathExcelActivities

Diese Optionen lassen bereits enorm viele Tätigkeiten zu - man kann also repetitive Excel-Schritte, die ein Benutzer ausführt, auch direkt aus der RPA-Software heraus in den gesamten Arbeitsablauf integrieren. Wer jedoch bereits mit VBA vertraut ist, setzt eher auf dieses Pferd, da man sich vom (eigenen) VBA-Code beispielsweise mehr Flexibilität und weitere Vorteile verspricht. Die Entscheidung ist abhängig vom Anwendungsfall und sollte jeweils evaluiert werden. Meine Empfehlung ist jederzeit, dass man auf Stärken von angebundenen Tools setzt und RPA als Bindeglied einsetzt.

Die Daten-Extraktion

Wir bearbeiten in diesem Beitrag folgendes Beispiel: Über das Web greifen wir auf ein internes "Sales Portal" zu. Darauf sind unter anderem in Tabellen-Form bestimmte Informationen und Aktivitäten unserer Aussendienstmitarbeiter einzusehen. Die Angaben in der Tabelle "Prospecting Activities per Sales Manager" möchten wir in einem eigenen Excel-Bericht besser darstellen. Die angesprochene Tabelle sieht wie folgt aus:

ProspectingActivitiesTable

Du kannst gerne ebenfalls mit unseren Demo-Daten experimentieren, die Test-Umgebung ist unter folgenden Links abzurufen:

https://www.routinuum.ch/demo-sites/MAB/SalesPortal.html

Es ist ein Login eingerichtet, wobei man einfach mittels Klick auf den "Login"-Button auf die Seite mit den Sales Informationen gelangt (alternativ direkt auf diesen Link navigieren: https://www.routinuum.ch/demo-sites/MAB/SalesData.html).

Bei einem manuellen Prozess würden diese Angaben in eine Excel-Tabelle eingetragen oder kopiert werden. Mit einer Automatisierung in UiPath loggen wir uns direkt in das Sales Portal ein und extrahieren die Daten entsprechend. Die Sequenz in UiPath kann wie folgt gestaltet werden:

DataExtractionSequence

Mit "Write Range"-Aktivitäten in UiPath kann dann diese extrahierte Tabelle an einen beliebigen Ort in Excel geschrieben werden (siehe unten).

Kombination mit VBA zu einem gesamtheitlichen Ablauf

Nach der Daten-Extraktion wären wiederum repetitive Arbeitsschritte notwendig, um den Bericht entsprechend aufzubereiten. Insbesondere hier wurde bislang bereits oft mit Makros (VBA) angesetzt. Auch ich habe mir für dieses Beispiel ein kurzes VBA-Skript aufbereitet, das die Rohdaten und die "Actual-Werte" in einer Pivot-Tabelle aggregiert und darstellt. Das Endergebnis sieht wie folgt aus:

ProspectingActivitiesPivot

Doch wie kann man nun RPA (UiPath) und VBA miteinander kombinieren? Dem aufmerksamen Leser sind möglicherweise zwei Aktivitäten in der allerersten Abbildung aufgefallen: "Execute Macro" oder "Invoke VBA". Mit erstgenannter Aktivität werden wir hier arbeiten. Nach der "Write Range"-Aktivität, um die extrahierten Daten in die Excel-Tabelle zu schreiben, fügen wir den Schritt "Execute Macro" ein. In Anführungszeichen setzen wir den Namen des Makros ein - in meinem Fall "PrepareData". Dies sind die Aktivitäten innerhalb der Sequenz "Excel Activities / Macro" in UiPath:

ExcelActivitiesSequence

Das ist es bereits! Der Roboter-Prozess öffnet eigenständig den Browser, loggt sich in das Sales Portal ein, extrahiert die Daten aus der "Prospecting Activities"-Tabelle, transferiert sie in das Excel-File und führt das separat angelegte Makro aus. Der vollständige Ablauf kann wie folgt strukturiert sein:

RPAVBAKombinationProzess

Die erste Sequenz "Get Credentials (Login)" fragt mögliche Login-Angaben aus dem Windows Credentials Manager (Anmeldeinformationsverwaltung) ab und zieht diese Angaben sicher in den Roboter-Prozess mit ein. Ein ausführlicher Beitrag dazu haben wir als Post auf der Website der Routinuum GmbH wie auch auf LinkedIn (Englisch) veröffentlicht.

Weiterführende Überlegungen zum Beispielprozess

Ein grosser Vorteil von RPA besteht darin, dass man jede denkbare Datenquelle erschliessen kann. Angenommen, eine Ländergesellschaft ist noch nicht an das vorgestellte "Sales Portal" angebunden. Periodengerecht erhalten wir die Informationen per Email, beispielsweise mit einem beiliegenden Excel-File. Der illustrierte Ablauf kann also durch Email-Aktivitäten (z.B. Outlook) beliebig erweitert werden, sodass der Dateianhang abgespeichert wird. Mit der "Append Range"-Aktivität können diese zusätzlichen Informationen ganz einfach der bestehenden Tabelle aus der Sales Portal-Datenextraktion angehängt werden. Durch dynamische Bereiche im erstellten Makro - beispielsweise mit der Codezeile  LastRow = Range("A1").End(xlDown).Row zur Ermittlung der letzten Zeile - inkludiert das Makro auch weitere Einträge in die entsprechende Pivot-Tabelle.

Outlook-Termine aus Excel erstellen

Mit VBA können nicht nur in Excel selbst Arbeitsschritte automatisiert werden, sondern es lassen sich auch andere MS Office-Produkte wie Outlook anbinden. Dieser Beitrag illustriert, wie aus einer Excel-Tabelle basierend auf einzelnen Zellinhalten ein Outlook-Termin erstellt werden kann.

Um die Funktionalitäten zu erschliessen, ist die Microsoft Outlook Object Library in VBA zu aktivieren. Gehe dafür wie folgt vor:
Öffne die VBA-Umgebung, indem Du entweder einen Rechtsklick auf ein bestehendes Tabellenblatt machst → "Code anzeigen..."oder mit der Tastenkombination Alt + F11. Unter "Extras" findest Du dann "Verweise", wie im Screenshot unten illustriert.

VBAVerweise

Suche da nach "Microsoft Outlook 16.0 Object Library" und aktiviere diesen Eintrag. Beachte: Die Version kann abhängig von Deiner MS Office Installation sein.

MOfficeObjLibrary

Die Excel-Tabelle zur Erstellung von Outlook-Terminen habe ich wie folgt aufgebaut:
OutlookTerminFile

Dem Benutzer stehen im oberen Bereich Eingabefelder zur Verfügung, welche sowohl als Steuerungsfelder für den effektiven Outlook-Termin dienen, aber auch die Texte unterhalb wie die "Anschrift" beeinflussen (Du = Lieber/Liebe; Sie = Sehr geehrter Herr/sehr geehrte Frau). Dies kann mit Wenn-Dann-Formeln erreicht werden und den Ablauf weiter optimieren, ist aber nicht zwingend. Zudem habe ich drei Sektionen (Einleitung - A; Hauptteil - B; Abschluss - C) in den Zellen 21, 23 und 25 eingepflegt, um die Email weiter zu strukturieren. Sämtliche Angaben werden durch das Makro weiterverarbeitet und an die entsprechenden Stellen des Termins als Variablen weitergereicht.

Lege nun in der VBA-Umgebung (siehe oben) ein neues Modul an und setze folgenden Beispielcode ein:

Sub TerminErstellen()

Dim OL As Outlook.Application, Appoint As Outlook.AppointmentItem, ES As Worksheet, WB As Workbook

Set WB = ThisWorkbook
Set TP = WB.Sheets("Terminplanung")
Set OL = New Outlook.Application

'Load Variables
Recipient = TP.Cells(3, 2).Value
DayMeeting = TP.Cells(9, 2).Value
StartTime = TP.Cells(10, 6).Value
EndTime = TP.Cells(11, 6).Value
Location = TP.Cells(12, 2).Value
Project = TP.Cells(13, 2).Value
Subject = TP.Cells(17, 2).Value
Greeting = TP.Cells(19, 2).Value
BodyA = TP.Cells(21, 2).Value
BodyB = TP.Cells(23, 2).Value
BodyC = TP.Cells(25, 2).Value
FinishA = TP.Cells(27, 2).Value
FinishB = TP.Cells(28, 2).Value

Set Appoint = OL.CreateItem(olAppointmentItem)
With Appoint
.Subject = Subject
.Start = StartTime
.End = EndTime
.Location = Location
.AllDayEvent = False
.Body = Greeting & Chr(10) & Chr(10) & BodyA & Chr(10) & Chr(10) & BodyB & Chr(10) & Chr(10) & BodyC & Chr(10) & Chr(10) & FinishA & Chr(10) & FinishB
.Save
End With

Set OL = Nothing

End Sub

Zuoberst werden die Objekte bestimmt und die Variablen geladen (das Tabellenblatt "Terminplanung" wird als "TP" deklariert, die Inhalte für beispielsweise den Empfänger oder den Betreff werden aus den Zellen B3 respektive B17 ausgelesen).
Im unteren Bereich (ab "Set Appoint") wird das effektive Outlook-Objekt - der Termin - erstellt, mit sämtlichen Angaben. Beachte: Hier ist aktuell kein Empfänger eingefügt, was für Testzwecke dazu führt, dass der Termin in Deinem eigenen Kalender angelegt wird. Du findest zu diesem AppointItem-Objekt weitere Eigenschaften unter diesem Link:

https://docs.microsoft.com/en-us/office/vba/api/outlook.appointmentitem#properties

Unter .Body siehst Du eine Zusammensetzung aus diversen Elementen. Mit "Chr(10)" werden Zeilenumbrüche eingefügt, um die Email besser zu strukturieren, das Resultat ist in der letzten Abbildung in diesem Blog ersichtlich. Die beiden Abbildungen unten illustrieren den erstellten Termin mit diesem Makro - sowohl für die Kalenderübersicht als auch wie erwähnt die zusätzlichen Informationen in der Email selbst.

OutlookTerminOverview

OutlookTerminDetails

If-Statement mit Platzhalter & Like-Operator (VBA)

Bei einer Suche - beispielsweise auf Google - kann mit Platzhaltern gearbeitet werden. Das Fragezeichen (?) ersetzt dabei ein einzelnes Zeichen, das Sternchen (*) ist ein Platzhalter/eine Wildcard für eine beliebige Zeichenfolge vor, innerhalb oder nach dem Suchbegriff. Verwendet man in VBA ein If-Statement, können ebenfalls solche Platzhalter zur Anwendung gebracht werden. Nehmen wir an, basierend auf einem User-Input im Format "Qx-JAHR" (bspw. Q2-2019) soll eine bestimmte Prozedur für eben dieses Quartal gesteuert werden. Normalerweise ist ein If-Statement oder besser gesagt ein If-Then-Else-Statement wie folgt aufgebaut:

If MeinBegriff = "Q2-2019" Then
Weiterer Code für diesen Fall

ElseIf Weitere Prüfungen
Weiterer Code

End If

Im obigen Fall wird Bei "MeinBegriff" handelt es sich um eine Variable, die vorhin in der Prozedur initialisiert wird. Sie kann das Resultat eines User-Inputs sein oder sich auf einen bestimmten Zellwert beziehen. Generell gilt jedoch: Da sich die Jahreszahl über die Dauer verändern wird, benötigen wir eine dynamische Variante mit Platzhaltern im Format "Q2-20*". Das Gleichheitszeichen (=) wird Operator genannt. Weitere Möglichkeiten sind "<>", ">" oder "<=" etc. Desweiteren gibt es den "Like"-Operator, den wir in diesem Fall verwenden.

Der Code für ein If-Statement mit "Like-Operator" lautet:

If MeinBegriff Like "Q1-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q2-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q3-20*" Then
Weiterer Code für diesen Fall

ElseIf MeinBegriff Like "Q4-20*" Then
Weiterer Code für diesen Fall

End If

Wenn der Wert der Variable "MeinBegriff" also ähnlich (like) ist, wird der entsprechende Fall ausgeübt.