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.

Ganze Zeile einfärben (bedingte Formatierung)

Bedingte Formatierungen bieten die Möglichkeit, Zellen basierend auf einem Wert zu formatieren (beispielsweise farblich hervorheben). Im Normalfall wird dies auf eine einzelne Zelle angewandt, um darzustellen, dass ein bestimmter Parameter unter- oder überschritten wurde. Ein konkretes Beispiel zeigt der Beitrag "Einen eingefärbten Kalender erstellen". Symbolsätze bieten die Möglichkeit, Trends und Entwicklungen (Pfeile) oder Ampeln und Indikatoren für den - oder gar anstelle des - entsprechenden Wert anzuzeigen. Dies kann in Übersichten und Berichten spannend sein, um auf einen Blick relevante Gegebenheiten erkennen zu können. Dies sind die Symbolsätze, die man unter dem Menüpunkt "Start" → "Bedingte Formatierung" findet:

bedingteFormatierungSymbols

In diesem Blog zeige ich Dir einen Weg, wie Du nicht nur eine einzelne Zelle hervorheben kannst, sondern sämtliche dazugehörigen Informationen in derselben Zeile werden ebenfalls formatiert. Somit werden erfasste Einträge besser ersichtlich und Du kannst Deine Daten beispielsweise nach Farbe filtern (siehe unten). Für unser Beispiel haben wir einen übersichtlichen Datensatz mit Kundeninformationen:

bedingteFormatierungRohdaten

Wir beabsichtigen nun, sämtliche Kunden farblich hervorzuheben, die über eine Kundenkarte verfügen (Spalte G). Um ein erstes Resultat zu erzielen, klicke in die Zelle "G2" und gehe via "Start" → "Bedingte Formatierung" auf "Regeln zum Hervorheben von Zellen" und dort auf "Textinhalt...":

bedingteFormatierungTextinhalt

Damit wird der bestehende Inhalt ("Ja") standardmässig rot hervorgehoben. Mit "OK" lässt sich die Auswahl bestätigen. Man kann jedoch die Formatierung beliebig anpassen, indem man in der Auswahl auf der rechten Seite "benutzerdefiniertem Format..." auswählt. Die Schritte da sind gleich, wie wenn man eine normale Zelle formatiert. Wenn Du die Auswahl bestätigt hast, gehe erneut auf "bedingte Formatierung" im Menüband und wähle dieses Mal zu unterst "Regeln verwalten...". Du siehst diese Option auch im Screenshot oben, wo wir "Regeln zum Hervorheben von Zellen" gefolgt von "Textinhalt..." angeklickt haben. Nun siehst Du die eine Regel, die Du erstellt hast. Gehe oberhalb des einzelnen Eintrags auf "Regel bearbeiten" und wähle anstelle von "Nur Zellen formatieren, die enthalten" die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kannst Du weiter unten eine Formel/Kondition eingeben, um bedingte Formatierung auszulösen. Verwende nachfolgende Formel:

=$G2="Ja"

(Das Dollarzeichen ist notwendig, um die Formatierung auf andere Spalten zu übertragen, aber dass die Spalte G und der Wert darin nach wie vor die Bedingung vorgibt.)

bedingteFormatierungFormatierungsregel

Bestätige die Formel und folglich die neue Formatierungsregel mit "OK". In der Übersicht passe nun noch den Bereich der Regel an -  dies ist die Spalte mit der Überschrift "Wird angewendet auf". Wie erweitern da den Bereich auf den gesamten Datenbereich (A2 bis G5). Dies führt dazu, dass alle Werte darin eingefärbt werden, sofern der jeweilige Wert in Spalte G "Ja" entspricht. Weitere Spalten rechts davon (z.B. H und fortfolgende) sind davon nicht betroffen. Damit der Bereich erweitert wird, gib folgende Formel an (die Dollarzeichen sind auch hier zwingend notwendig!):

=$A$2:$G$5

bedingteFormatierungRegelManager

Du solltest die Änderungen bereits aktiv sehen, wenn Du rechts unten auf "Übernehmen" klickst. Du kannst aber auch direkt auf "OK" drücken, um den Regel-Manager zu schliessen. Nun hast Du zwei Einträge komplett eingefärbt, weil der jeweilige Kunde über eine Kundenkarte verfügt:

bedingteFormatierungResultat

Resultate filtern (Farbfilter)

Einleitend habe ich erwähnt, dass das Resultat nun aufgrund der bedingten Formatierung deutlich besser ersichtlich ist. Das Ergebnis ist auch ansprechender, als wenn nur die Zelle in der Spalte G eingefärbt wäre. In jeder beliebigen Spalte kann nun nach der Farbe gefiltert werden, um nur die eingefärbten Zeilen anzuzeigen:

bedingteFormatierungFarbfilter

"Überlauf" - Formel-Ausgabe mehrerer Werte

In früheren Excel-Versionen hat man teilweise trotz korrekter Eingabe einer Formel die Fehlermeldung "#WERT!" erhalten. Dies war beispielsweise bei "=BEREICH.VERSCHIEBEN" der Fall, wenn das Ergebnis der Formel mehrere Werte beinhaltete. Dabei kommt es zu einem "Überlauf", da innerhalb einer Zelle nur ein Wert dargestellt werden kann. Angewendet hat man die erwähnte Formel unter anderem, um dynamische Bereiche zu definieren - damit Pivot-Tabellen oder Drop-Down-Listen (allenfalls nach dem Aktualisieren) die angepassten Datenquellen berücksichtigen. Dabei wird mit dem Namensmanager gearbeitet, wie bereits auf Excelblog.ch im Beitrag "Pivot-Datenquelle automatisch erweitern" sowie "Bereich eines Namens automatisch erweitern" erläutert wurde.

In diesem Beitrag gehen wir erneut auf die Formel "BEREICH.VERSCHIEBEN" ein und stellen damit innerhalb einer Excel-Tabelle eine dynamische Liste erneut dar - ohne den Namensmanager zu verwenden. Dabei simulieren wir erneut den angesprochenen "Überlauf" und zeigen, wie der Ergebnisbereich neuerdings in Excel dargestellt wird. Um dieses Beispiel zu erläutern, verwenden wir nachfolgende Länder-Liste auf dem Tabellenblatt "Parameter":

Laenderliste_Ausgangslage

Eine Drop-Down-Liste könnte ganz einfach auf diesen Bereich verweisen und nur diese Werte wären zulässig. Falls man jedoch weitere Länder in die Liste eintragen sollte, bleibt der Bezug der Drop-Down-Liste statisch. Weitere Erläuterungen zu diesem dynamischen Bereich findest Du im bereits verlinkten Beitrag "Bereich eines Namens automatisch erweitern". Die Formel, die wir verwenden, um den Bereich in unserem Beispiel dynamisch zu gestalten, lautet wie folgt:

=BEREICH.VERSCHIEBEN(Parameter!$A$2;;;ANZAHL2(Parameter!$A:$A)-1;)

Ausgehend von der Zelle "A2" (erstes Land) wird gemessen, wie viele Einträge in der Spalte A vorhanden sind (abzüglich 1 für die Überschrift). Dies ergibt unseren dynamischen Bereich, welchen wir in einer Drop-Down-Liste verwenden können:

Laenderliste_DropDown

Falls man obige Formel jedoch nicht im Namen eingibt, sondern wie ansonsten üblich in einer einfachen Zelle (im Screenshot unten Zelle "B2"), dann erhalten wir folgendes Resultat:

Laenderliste_BereichVerschieben

Die Liste wird exakt gleich dargestellt und die Werte werden in den Zellen unterhalb von B2 weitergeführt - die Zelle B2 ist "überlaufen". Wenn Du in die Zelle B2 klickst, siehst Du die Formel in der Bearbeitungsleiste normal angezeigt. Klickst Du jedoch beispielsweise in die Zelle "B3", dann ist die Formel zwar angezeigt, aber ausgegraut. Du kannst sie da auch nicht bearbeiten - nur eben in Zelle "B2". Falls Du einen Wert in den "Überlaufbereich" einfügst (z.B. in Zelle "B3"), dann erhältst Du neu die Fehlermeldung "#ÜBERLAUF!". Dies ist ein Hinweis, dass der Überlaufbereich nicht leer ist. Über die Fehlermeldung kann man auch direkt in die "blockierende Zelle" navigieren - dies kann vor allem bei grösseren Listen hilfreich sein:

Laenderliste_Ueberlauf

=SORTIEREN - ein weiterer Anwendungsfall

Eine weitere Formel, die nun ein interessantes Ergebnis mit Überlauf liefert, ist "=SORTIEREN". Dies kann erneut mit der obigen Länder-Liste illustriert werden. Angenommen die Länder sind einfach über die Zeit in die Liste eingetragen worden, ohne einer Reihenfolge oder Sortierung zu unterliegen. Mit der Formel "=SORTIEREN" kann einfach die gesamte Liste markiert werden, um die Länder im Zielbereich alphabetisch zu sortieren - dafür benötigt es in der Formel keine weiteren Angabe, der Bereich (z.B. "A2:A11") genügt! Die Formel wird in einer Zelle eingetragen, sämtliche Länder werden mithilfe des Überlaufs als Bereich ausgegeben. Dies ist das Resultat, nebeneinander dargestellt:

Laenderliste_SORTIEREN