Tutorial: Vergleichen von Excel-Dateien und Senden von Abstimmungsfehlern per E-Mail

In diesem Tutorial erstellen wir eine Automatisierung, um die Beträge in einer Kalkulationstabelle, die Rechnungen enthält, mit den Beträgen aus einer CSV-Datei zu vergleichen, die eine Zusammenfassung pro Lieferant enthält. Wenn Abstimmungsfehler zwischen den Dateien vorhanden sind, erstellen wir eine CSV-Datei mit Details und senden die Datei per E-Mail.

Wir erstellen ein Projekt mit folgenden Aktivitäten:

1. Zwei Excel-Datei verwenden Aktivitäten um die Excel-Datei mit den Rechnungen und die Datei anzugeben, in der wir die Daten abstimmen möchten.

2. Eine Reihe von Zelle schreiben- Aktivitäten, um der Excel-Abstimmungsdatei zwei Spaltenüberschriften hinzuzufügen und die Summe der Rechnungen für einen Lieferanten und die Differenz zwischen dieser Summe und dem Betrag in der ursprünglichen Zusammenfassung zu berechnen. Wir fügen eine Aktivität vom Typ Bereich kopieren hinzu, um die Formeln zu kopieren, die wir für einen Lieferanten zu allen Zeilen im Blatt hinzugefügt haben, damit wir die gleichen Berechnungen für alle Lieferanten durchführen können. Dann fügen wir eine weitere Aktivität vom Typ „Zelle schreiben“ hinzu, um die Gesamtdifferenz zu addieren einer Zelle.

3. Eine Aktivität vom Typ Wenn , bei der wir eine Bedingung hinzufügen, durch die bei Abstimmungsfehlern (die Summe der Differenzen wird größer als 0) eine CSV-Datei mit Details erstellt und per E-Mail an eine angegebene Adresse gesendet wird. Für E-Mail-Aufgaben fügen wir in der Aktivität „Wenn“ eine Aktivität vom Typ Outlook-Desktop-App verwenden hinzu, um das Outlook-Konto anzugeben, von dem wir eine E-Mail senden möchten, und eine Aktivität vom Typ E-Mail senden in „Outlook-Desktop-App verwenden“.

4. Schritt 1: Richten Sie das Projekt ein und erhalten Sie die erforderlichen Dateien.

   1. Create a new blank project using the default settings.
   2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.

5. Schritt 2: Fügen Sie die Excel-Dateien zum Projekt hinzu.

   1. Click Add activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.

   2. In der Aktivität:

      • Click Browse next to the Excel file field, and then browse to and select the file invoices.xlsx

      • Geben Sie im Feld Referenzieren als Invoices ein.

        Sie haben angegeben, dass Sie mit der Datei Rechnungen.xlsx arbeiten werden, die in Ihrer Automatisierung als „Rechnungen“ bekannt ist.

   3. Click Add activity inside the Use Excel File activity, and then add a another Use Excel File activity.

   4. In der zweiten Aktivität:

      • Click Browse next to the Excel file field, and then browse to and select the file reconcile.xlsx

      • Geben Sie im Feld Referenzieren als Rec ein.

        Sie haben angegeben, dass Sie mit der Datei Abgleich.xlsx arbeiten werden, die in Ihrer Automatisierung als „Abg.“ bekannt ist.

        

6. Schritt 3: Kopieren Sie die Daten in die Abstimmungsdatei.

   1. Click Add activity inside the second Use Excel File, and then find the Read CSV activity in the search box at the top of the screen and select it. The activity is added inside the Use Excel File activity.

   2. In the Read CSV activity:

      • Click Browse next to the Read from file field, and then browse to and select the file summary.csv.

      • Click Plus on the right side of the Output to field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Invoices.

        Sie haben angegeben, dass Sie die Daten aus der CSV-Datei „Zusammenfassung.csv“ Datei „Abg.“ in das Blatt „Zusammenfassung“ kopieren möchten.

   3. Click Add activity inside the second Use Excel File below the Read CSV activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added inside the Use Excel File activity.

   4. In der Aktivität Bereich kopieren (Copy Range):

      • Click Plus on the right side of the Source range field, and then select Invoices > Invoices [Sheet]. Your selection is displayed in the field as [Invoices] Invoices.

        Sie haben angegeben, dass Sie das Blatt „Rechnungen“ aus der Datei „Rechnungen“ kopieren möchten.

      • Click Plus on the right side of the Destination range field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.

        Sie haben angegeben, dass Sie den kopierten Bereich in das Blatt „Rechnungen“ in der Datei „Abg.“ einfügen möchten.

        

7. Schritt 4: Erforderliche Berechtigungen für den Datenvergleich erstellen.

   1. Wählen Sie im Aktivitätenbereich die Registerkarte Excel aus, und ziehen Sie die Aktivität Zelle schreiben (Write Cell) und legen Sie sie unter der zweiten Aktivität „Bereich kopieren“ (Copy Range) ab. Dem Entwurfsbereich wird eine „Zelle schreiben“-Aktivität hinzugefügt.

   2. In der Aktivität „Zelle schreiben“ (Write Cell):

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Calculated Amount.

      • Klicken Sie auf Plus rechts neben dem Feld Wo geschrieben werden soll und wählen Sie dann Abg. > In Excel angeben aus. Die Kalkulationstabelle wird in Excel geöffnet. Wählen Sie die Zelle C1 aus und klicken Sie dann auf Bestätigen. Das Feld wird mit Ihrer Auswahl [Abg.] Zusammenfassung!C1 aktualisiert.

        Sie haben angegeben, dass die erste Zelle in Spalte C aus dem Blatt „Zusammenfassung“ der Abg.-Datei den Text Calculated Amount enthalten soll.

   3. Fügen Sie eine zweite Aktivität „Zelle schreiben (Write Cell)“ unter der vorherigen hinzu und in der Aktivität:

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").

      • Klicken Sie auf Plus rechts neben dem Feld Wo geschrieben werden soll und wählen Sie dann Abg. > In Excel angeben aus. Die Kalkulationstabelle wird in Excel geöffnet. Wählen Sie die Zelle C2 aus und klicken Sie dann auf Bestätigen. Das Feld wird mit Ihrer Auswahl [Abg.] Zusammenfassung!C2 aktualisiert.

        Sie haben angegeben, dass C2 aus dem Blatt „Zusammenfassung“ der Abg.-Datei die Formel =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "") enthalten soll. Diese Formel überprüft, dass die Lieferantenzelle (A2) nicht leer ist, und summiert die Werte in der Spalte C des Blatts „Rechnungen“ für den Lieferanten in der Zelle A2 des Blatts „Zusammenfassung“.

   4. Fügen Sie eine dritte Aktivität „Zelle schreiben (Write Cell)“ unter der vorherigen hinzu und in der Aktivität:

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Difference.

      • Klicken Sie auf Plus rechts neben dem Feld Wo geschrieben werden soll und wählen Sie dann Abg. > In Excel angeben aus. Die Kalkulationstabelle wird in Excel geöffnet. Wählen Sie die Zelle D1 aus und klicken Sie dann auf Bestätigen. Das Feld wird mit Ihrer Auswahl [Abg.] Zusammenfassung!D1 aktualisiert.

        Sie haben angegeben, dass die erste Zelle in Spalte D aus dem Blatt „Zusammenfassung“ der Abg.-Datei den Text Difference enthalten soll.

   5. Fügen Sie eine vierte Aktivität „Zelle schreiben (Write Cell)“ unter der vorherigen hinzu und in der Aktivität:

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", C2-B2, "").

      • Klicken Sie auf Plus rechts neben dem Feld Wo geschrieben werden soll und wählen Sie dann Abg. > In Excel angeben aus. Die Kalkulationstabelle wird in Excel geöffnet. Wählen Sie die Zelle D2 aus und klicken Sie dann auf Bestätigen. Das Feld wird mit Ihrer Auswahl [Abg.] Zusammenfassung!D2 aktualisiert.

        Sie haben angegeben, dass D2 aus dem Blatt „Zusammenfassung“ der Abg.-Datei die Formel =IF(A2 <> "", C2-B2, "") enthalten soll. Diese Formel überprüft, dass die Lieferantenzelle (A2) nicht leer ist, und berechnet die Differenz zwischen den Werten in den Zellen C2 und B2 des Blatts „Zusammenfassung“.

   6. Click Add activity below the last Write Cell activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added to the Designer panel.

   7. In der Aktivität Bereich kopieren (Copy Range):

      • Click Plus on the right side of the Source range field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C2:D2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2:D2.

      • Click Plus on the right side of the Destination range field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C3-D20, and then click Confirm. The field is updated with your selection [Rec] Summary!C3-D20.

      • Wählen Sie im Dropdown-Menü Was kopiert werden soll die Option Formeln aus.

        Sie haben angegeben, dass Sie die Formel im kopierten Bereich C2:D2 in dieselben Spalten im Blatt einfügen möchten.

   8. Wählen Sie im Aktivitätenbereich die Registerkarte Excel aus, und ziehen Sie die Aktivität Zelle schreiben (Write Cell) und legen Sie sie unter der letzten Aktivität „Bereich kopieren“ (Copy Range) ab. Dem Entwurfsbereich wird eine „Zelle schreiben“-Aktivität hinzugefügt.

   9. In der Aktivität „Zelle schreiben“ (Write Cell):

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the formula =SUM(Summary!D:D).

      • Klicken Sie auf Plus rechts neben dem Feld Wo geschrieben werden soll und wählen Sie dann Abg. > In Excel angeben aus. Die Kalkulationstabelle wird in Excel geöffnet. Wählen Sie das Blatt mit Rechnungen, dann die Zelle F1 aus und klicken Sie dann auf Bestätigen. Das Feld wird mit Ihrer Auswahl [Abg.] Rechnungen!F1 aktualisiert.

        Sie haben angegeben, dass die erste Zelle in Spalte F aus dem Blatt „Rechnungen“ der Datei „Abg.“ die Formel =SUM(Summary!D:D) enthalten soll. Die Formel fügt die Summe der Differenzen in der Spalte D des Blatts „Zusammenfassung“ zur F1-Zelle im Blatt „Rechnungen“ hinzu.

        

8. Schritt 5: E-Mails mit Details zur Abstimmung senden, falls die Summe der Differenzen größer als 0 ist.

   Die Rechnungen und Zusammenfassungsdaten werden in die Datei reconcile.xlsx kopiert, die Berechnungen werden durchgeführt und da der Gesamtbetrag in den Rechnungen nicht mit dem Betrag in der Zusammenfassung für einen der Lieferanten übereinstimmt, wird das Zusammenfassungsblatt der Datei „reconcile.xsls“ in eine CSV-Datei kopiert, deren Name das heutige Datum enthält. Die Datei wird an eine E-Mail angehängt und an die angegebene Adresse gesendet. In der Datei können Sie den Lieferanten mit Abstimmungsfehlern anhand des Werts in Zelle D3 identifizieren, der größer als 0 ist.

   Beispiel herunterladen

   1. Click Add activity below the last Write Cell activity, and then, in the search box at the top of the screen, locate and select If. An If activity is added to the Designer panel.

   2. In the If activity, click Plus on the right side of the Condition field, and then select Condition Builder.

   3. Fügen Sie im Bedingungsgenerator die Bedingung "wenn die Summe der Differenzen größer als 0 ist" hinzu:

      • Click Plus on the right side of the field on the left, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.

      • From the drop-down menu in the middle, select greater than.

      • Klicken Sie auf Plus rechts neben dem Feld auf der rechten Seite, wählen Sie Zahl aus, geben Sie 0 ein und klicken Sie zweimal auf Speichern.

        Die Aktivitäten, die Sie im Dann-Zweig der Wenn-Aktivität hinzufügen, werden ausgeführt, wenn diese Bedingung erfüllt ist.

   4. Wählen Sie im Aktivitätenbereich die Registerkarte CSV aus, und ziehen Sie dann die Aktivität CSV schreiben in den Dann-Zweig der Aktivität „Wenn“. Dem Entwurfsbereich wird eine Aktivität vom Typ „CSV schreiben“ hinzugefügt.

   5. In der Aktivität „CSV schreiben (Write CSV)“:

      • Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter reconcile, and then from the Plus menu on the right side of the Text Builder select Project Notebook (Notes) > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile Excel Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile Excel Date!YYYYMMDD.csv.

      • Klicken Sie auf Plus rechts neben dem Feld Schreiben aus und wählen Sie dann Abg. > Zusammenfassung [Blatt] aus. Das Feld wird mit Ihrer Auswahl [Abg.] Zusammenfassung aktualisiert.

        Sie haben angegeben, dass Sie eine CSV-Datei im Projektordner erstellen möchten, deren Name das Wort „Abgleich“ und das heutige Datum enthält, und das Zusammenfassungsblatt der Abg.-Datei hineinkopieren möchten.

   6. Wählen Sie im Aktivitätenbereich die Registerkarte E-Mail aus, und ziehen Sie die Aktivität Outlook-Desktop-App verwenden (Use Desktop Outlook App) in den Dann-Zweig der Aktivität „Wenn“ (If) unter der Aktivität „CSV schreiben“ (Write CSV).

   7. In der Aktivität ist das Standard-E-Mail-Konto bereits im Feld Konto ausgewählt. Wenn Sie ein anderes Konto verwenden möchten, wählen Sie es im Dropdown-Menü aus.

      Lassen Sie im Feld Referenzieren als den Standardwert Outlook als den Namen stehen, mit dem in der Automatisierung auf das Konto verwiesen werden soll.

   8. Ziehen Sie die Aktivität E-Mail senden (Send Email) aus dem Aktivitätenbereich, und legen Sie sie in der Aktivität „Outlook-Desktop-App verwenden“ (Use Desktop Outlook App) ab.

   9. In der Aktivität „E-Mail senden“ (Send Email):

      • Click Plus on the right side of the From account field, and then select Outlook.

      • Click Plus on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself. If you leave the Is draft option selected, the automation does not send the email, it instead saves the email to the Outlook Drafts folder.

      • Click Plus on the right side of the Subject field, and then select Text. In the Text Builder window, enter Reconciliation errors for, and then from the Plus menu on the right side of the Text Builder select Project Notebook (Notes) > Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for Date!Today.

      • Click Plus on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example One or more suppliers have reconciliation errors. See attached CSV file for details..

      • Wählen Sie für Anlagen Dateien aus, und geben Sie dann denselben Dateinamen ein wie in der Aktivität „CSV schreiben“ (Write CSV) in Schritt 5 ein, reconcile Excel Date!YYYYMMDD.csv. Sie können z. B. den Textgenerator für das Feld In welche Datei schreiben der Aktivität „CSV schreiben“ (Write CSV) öffnen, den gesamten Text kopieren und dann in den Textgenerator für das Dateianlagenfeld einfügen.

        

   10. Klicken Sie im StudioX-Menüband auf Speichern, um die Automatisierung zu speichern, und klicken Sie dann auf Ausführen, um die Automatisierung auszuführen.