Collections in VBA

Wer Daten von einer Quelle in eine Zieltabelle überträgt, kann mit den .Copy und .Paste-Methoden ein gewünschtes Resultat erzielen. Ist man jedoch häufig dazu gezwungen, zwischen Tabellenblättern oder Excel-Dateien hin und her zu wechseln, hat das Implikationen auf die Laufzeit des Makros. Eine performante Variante, um mehrere Datensätze zu übertragen, bietet der Einsatz von Collections. Beim einleitend beschriebenen Fall werden zuerst alle Datensätze der Collection hinzugefügt und daraufhin auf der Zieltabelle eingetragen. In diesem Blog wird dieses Beispiel illustriert und abschliessend eine weitere Möglichkeit aufgezeigt, wie Collections zusätzlich genutzt werden können.

Die nachfolgenden Tagesumsätze sollen auf eine zweite Tabelle übertragen werden:

CollectionUmsaetze

Natürlich könnte man alle Daten selektieren und mittels .Copy und .Paste fortschreiben, wir verwenden aber zur Übung und Illustration eine Collection dazu. Wir definieren demnach die Variable "UmsatzColl". Zudem ist die Collection zu initiieren. Verwende hierzu diese beiden Codezeilen:

Dim UmsatzColl As Collection
Set UmsatzColl = New Collection

Um beispielsweise den Wert in Zelle "C4" Deiner Collection hinzuzufügen, verwende:

UmsatzColl.Add Range("C4").Value

Man führt hinter der Collection-Variable also die Methode "Add" hinzu, gefolgt vom Bezug auf den Wert, den man zwischenspeichern möchte. Zusätzlich zum Wert könnte ein "Key" angegeben werden mit dem jederzeit auf den effektiven Wert referenziert werden kann. Hier könnte beispielsweise das Datum des Umsatzes hinzugefügt werden. Desweiteren kann man den Wert sortieren oder einordnen lassen, indem man die fakultativen "Before" oder "After"-Angaben mitgibt.

CollectionProperties

Neben "Add" stehen weitere Methoden zur Verfügung, sie werden Dir angezeigt, sobald Du "UmsatzColl." schreibst:

CollectionMethods

Man fügt Werte zur Sammlung hinzu, kann mit "Count" ausgeben, wie viele Werte in der Collection sind und mit "Remove" spezifische Einträge entfernen. Mit "Item" kann ein Wert ausgegeben werden (es ist jedoch die Standardeigenschaft und muss nicht zwingend angegeben werden) - ich werde das weiter unten aufführen.

Wir haben oben einen spezifischen Wert (Zelle "C4") hinzugefügt. Mit einer For-Schleife werden wir alle Umsätze durchgehen (Zeile 2 bis 24), die Integer-Variable "i" unterstützt diesen Loop:

For i = 2 To 24
UmsatzColl.Add Range("C" & i).Value
Next

 Um nun die Daten auf einer anderen Tabelle abzurufen, verwende ich wiederum einen For-Loop. Dabei wird wieder "i" verwendet, beginnend bei 1 bis hin zur Anzahl Items, die in der Collection sind (UmsatzColl.Count). Die Werte werden in eine Spalte A geschrieben:

For i = 1 To UmsatzColl.Count
Range("A" & i) = UmsatzColl(i)
Next

Alternativ könntest Du auch UmsatzColl.Item(i) verwenden, wie oben erwähnt - das Resultat ist dasselbe.

In der Praxis verwende ich teilweise auch mehrere Collections in Kombination. Eine "StartCopyRowColl" und "EndCopyRowColl" beispielsweise, wenn mehrere auseinanderliegende und unterschiedlich grosse Bereiche zu kopieren sind. Zusammen gibt es spannende und sehr performante Lösungen, da der Index ("i") in beiden Collections korreliert.

Ein weiterer Tipp: Du musst nicht alle Items mit "Remove" entfernen, wenn Du die Collection in der gleichen Prozedur erneut verwenden möchtest. Führe einfach die Codezeile "Set UmsatzColl = New Collection" erneut auf, dann beginnst Du mit Deiner Sammlung wieder von vorne.

Excel mit Robotic Process Automation (RPA)

Unter Robotic Process Automation (RPA) versteht man das Ausführen von administrativen Tätigkeiten mithilfe von "virtuellen Mitarbeitern" - einem "Software-Roboter". Dabei kann mit sämtlichen Applikationen (auch Legacy-Systemen) und dem Internet interagiert werden. Die Technologie an sich haben wir hier bereits im Jahr 2017 (siehe hier) vorgestellt. In diesem Beitrag geht es nun darum, einige Interaktionsmöglichkeiten zwischen der RPA-Software von Kofax (Kapow / Kofax RPA) und Excel aufzuzeigen.

Als Einstieg werden folgende Bereiche in diesem Blog vorgestellt:

  • Excel-Datei in Kapow einbinden
  • Selektion von Zellen, Zeilen & Spalten
  • Loop durch eine Spalte und Extraktion von Werten (Hinterlegen in einer Variable)

Der vorliegende Prozess beinhaltet eine interne Artikelliste, deren Inhalt durchlaufen und extrahiert werden soll. Weiterführend könnten diese Daten verwendet werden, um beispielsweise online Preise zu vergleichen.

Excel-Datei in Kapow einbinden

In Kofax Kapow wird eine Excel-Datei direkt als Tabellenblatt angezeigt, wie man sich das in Excel selbst gewöhnt ist. Die Abbildung unten zeigt diese Ansicht:

KapowExcelWindow

Um dies zu erlangen, erstellen wir als erstes eine Variable des Typs "Excel" und wählen die Excel-Datei aus - für dieses Beispiel habe ich die Variable "artikelliste" angelegt:

VariableArtikelliste

Als nächstes geben wir dem Workflow die Aktion "Open Variable" hinzu und wählen da die angelegte "artikelliste"-Variable.

KapowActionOpenVariable

Selektion von Zellen, Zeilen und Spalten

Innerhalb des Fensters können nun beliebige Zellen und Bereiche, Spalten oder Zeilen ausgewählt werden. Ein Klick auf die Box links oben (oberhalb von Zeile 1, links von Spalte A) lässt wie in Excel selbst alle Zellen selektieren.

KapowExcelSelect

Loop durch eine Spalte & Extraktion der Werte

Das Ziel dieses Beispielprozesses besteht darin, sämtliche ISBN-13 zu extrahieren und diese weiterzuverarbeiten. Hierzu geht man mittels Rechtsklick auf die gewünschte Spalte (A), "Loop" → "Loop Rows in Selection" und wir wählen zusätzlich, dass die erste Zeile (aufgrund der Überschriften) ignoriert wird:

LoopThroughSelection

Nachdem der Loop angelegt ist, definieren wir eine zusätzliche Variable "isbn13". Der nächste Schritt besteht darin, den Text (die ISBN-13) zu extrahieren. Dazu erfolgt ein Rechtsklick auf die Zelle "A2", gefolgt von "Extract" → "Text" und die erstellte Variable "isbn13".

ExtractTextISBN13

Im Variablen-Fenster wird je nach Prozess-Schritt die entsprechende ISBN angezeigt, im Beispiel unten die allererste ISBN aus Zelle "A2":

VariableISBN13

Um diesen Punkt weiter zu illustrieren, zeige ich Dir hier eine Abbildung mit dem bisherigen Workflow (Artikelliste öffnen, Loop & Extraktion des Wertes). Zudem stellen wir den Loop auf die zweite Iteration - so wird die Zelle "A3" selektiert und dessen Wert extrahiert. Rechts unten im Variablen-Bereich ist zudem diese ISBN hinterlegt, wohingegen in der Abbildung oben noch die erste ISBN des Titels "Archipel" hinterlegt war.

ExtractTextLoopValue

Weitere Excel-Aktionen in Kapow

Abschliessend füge ich hier eine Abbildung sämtlicher Aktionen ein, die im Aktivitäten-Fenster ausgewählt werden können. Diese Liste ist jedoch nicht abschliessend, da beispielsweise mit Device Automation viele weitere Möglichkeiten im Zusammenhang mit Excel erschlossen werden können.

KapowSelectActionExcel

Loop durch Tabellenblätter mit Sheets.Index (VBA)

Angeknüpft an den letzten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter, behandelt dieser Beitrag die Thematik, wie mittels einer VBA-Schleife (Loop) durch mehrere Tabellenblätter navigiert werden kann, ohne dass der Code jeweils anzupassen ist. [Eine Einführung zum hier angewandten "For"-Loop - einer VBA-Schleife - findest Du in diesem Beitrag.] Ziel ist es also, weitere Tabellenblätter einfügen zu können, ohne auf diese spezifisch im Code verweisen zu müssen.

Unser Beispielunternehmen weist Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Kantone ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total"):

SheetIndexSheetsA

Wir gehen davon aus, dass jede einzelne Region die gleiche Struktur aufweist und wir demnach denselben Makro-Code auf alle Tabellenblätter anwenden können - ein perfekter Anwendungsfall also für einen Loop! In einem ersten Schritt sollten die notwendigen Befehle für ein einzelnes Tabellenblatt aufgezeichnet, geschrieben und getestet werden. Auf jedem Blatt sollen beispielsweise Spalten ausgeblendet sowie Formatierungen vorgenommen werden. Es bieten sich nun unterschiedliche Schleifen an, die sich über alle gewünschten Tabellenblätter erstrecken. In unserem Fall soll der Loop jedoch dynamisch sein, damit wir jederzeit neue Regionen einfügen können. Verweisen wir im Code demnach explizit auf die aktuell erste Region "Basel", hat dies später allenfalls keine Gültigkeit mehr (Beispiel: Wenn wir "Aargau" einfügen, rückt dieser Kanton an erste Stelle). Im anfangs verlinkten Beitrag zu dynamischen Formeln über mehrere Tabellenblätter haben wir Hilfstabellenblätter eingefügt ("Kanton_AA" und "Kanton_ZZ") und diese am Ende ausgeblendet:

SheetIndexSheetsZ

Durch diesen Mechanismus kann nun ein "For"-Loop angewandt werden. Unser Makro ermittelt den "Sheet.Index" dieser Hilfsblätter; das heisst, an welcher Stelle in der Datei diese Tabellenblätter stehen. Wir definieren hier einerseits die Variablen des Typs "Integer" (vereinfacht: Zahlen) im Makro:

Dim SheetIndexStart As Integer
Dim SheetIndexEnd As Integer

Danach kann der Indexwert dieser beiden Tabellenblätter ermittelt werden:

SheetIndexStart = Sheets("Kanton_AA").Index
SheetIndexEnd = Sheets("Kanton_ZZ").Index

In unserem Beispiel wird für "Kanton_AA" der Wert "2" als Variable abgespeichert, für "Kanton_ZZ" ist dies der Wert "6". Nun kann der "For"-Loop eingesetzt werden, der den Code mit den Befehlen für die Tabellenblätter umfasst:

For x = SheetIndexStart + 1 To SheetIndexEnd - 1
Sheets(x).Activate
'Befehle zur Bearbeitung der Tabellenblätter
Next

Da wir die Hilfsblätter nicht zwingend bearbeiten müssen, kannst Du entweder direkt bei der Definition der Variablen "+1" addieren respektive "-1" subtrahieren, oder wie im Code oben beim Beginn des "For"-Loop die Index-Zahl um eins erhöhen und reduzieren. Mit "Sheets(x).Activate" wird dann in jedem Durchlauf der Schleife jeweils das entsprechende Tabellenblatt aktiviert und bearbeitet. Beachte, dass Du die Hilfstabellenblätter "Kanton_AA" und "Kanton_ZZ" allenfalls zuerst durch das Makro einblenden musst. Füge hier zu Beginn Deines Makros einfach ein:

Sheets("Kanton_AA").Visible = True
Sheets("Kanton_ZZ").Visible = True

... und am Ende dasselbe, mit dem Boolean-Wert "False", damit die Blätter direkt wieder ausgeblendet sind.

Fügst Du nun eine neue Region (z.B. "Genf" oder "Aargau") ein, wird das Makro dank der Ermittlung des "Index-Wertes" auch diese Tabellenblätter in der Loop-Bearbeitung berücksichtigen können. Entsprechend reagiert die Selektion, falls Du weitere Tabellenblätter weiter vorne in der Datei einfügst (beispielsweise eine weitere Auswertung neben dem "Total") oder die Kantone mitsamt den beiden Hilfsblätter an eine andere Stelle innerhalb der Datei verschiebst.