E-Rechnungen per VBA erzeugen: SmartTools VBA-Bibliothek

Wer viele Rechnungen aus Excel erzeugt oder den ganzen Ablauf auf einen Klick reduzieren möchte, kommt an VBA nicht vorbei. Die SmartTools E-Rechnung VBA-Bibliothek 2.5 für Excel bringt dafür ein Objektmodell mit, mit dem Sie E-Rechnungen in allen ZUGFeRD-Profilen und als XRechnung erzeugen, einlesen, prüfen und anzeigen.

Wir setzen die Bibliothek in Kundenprojekten ein. Dieser Beitrag zeigt den Aufbau einer Lösung an praxisnahen Beispielen. Grundlagen zu Pflicht und Formaten stehen im Beitrag E-Rechnungspflicht: Zeitplan, Formate und Lösungen.

Kurz gesagt

  • Die gesamte Formatlogik steckt in der Bibliothek und wird von SmartTools gepflegt. Ihr Code weist nur noch die Daten aus Ihren Tabellen zu.
  • Erstellen: ZUGFeRD von Minimum bis Extended, XRechnung in CII und UBL.
  • Einlesen: empfangene E-Rechnungen vollständig in ein Objekt importieren.
  • Prüfen: offline nach den offiziellen Regeln. Anzeigen: mit einer einzigen Anweisung.
  • Eine ungeschützte Musterlösung ist dabei, die Sie als Vorlage nutzen oder in Ihre Mappe übernehmen.

Einrichtung in wenigen Minuten

  1. Installieren. Die Bibliothek wird automatisch installiert und in Windows registriert. Sie läuft unter Excel 2013 bis Microsoft 365, vollständig lokal.
  2. Verweis setzen. In der Rechnungsmappe Alt+F11, dann Extras > Verweise, die SmartTools-Bibliothek anhaken.
  3. Musterlösung ansehen. Dort stehen die genauen Klassennamen je Profil und vollständige Abläufe mit allen Pflichtfeldern.

Die folgenden Beispiele zeigen den typischen Aufbau. Sie sind bewusst schlank und ersetzen nicht die vollständige Befüllung aller Pflichtangaben, wie sie die Musterlösung zeigt.

Beispiel 1: Kopfdaten aus benannten Bereichen

Statt fester Zelladressen verwenden wir benannte Bereiche (Formeln > Namens-Manager). Verschiebt sich später eine Zeile in der Vorlage, bleibt der Code richtig:

Option Explicit

' Inhalt eines benannten Bereichs als Text liefern.
' .Text statt .Value: Postleitzahlen mit dem Format 00000 behalten ihre führende Null.
Function Wert(strName As String) As String
    Wert = Trim$(ThisWorkbook.Names(strName).RefersToRange.Text)
End Function

' XRechnung anlegen und Kopfdaten aus benannten Bereichen der Rechnungsmappe übernehmen
Function NeueXRechnung() As SmartTools_Invoicing.XRechnungInvoice
    Dim objInvoice As SmartTools_Invoicing.XRechnungInvoice
    Set objInvoice = New SmartTools_Invoicing.XRechnungInvoice

    With objInvoice.Seller                      ' Verkäufer, also Ihre Firma
        .Name = Wert("Verkaeufer_Name")
        .PostalAddress.AddressLine1 = Wert("Verkaeufer_Strasse")
        .PostalAddress.PostCode = Wert("Verkaeufer_PLZ")
        .PostalAddress.City = Wert("Verkaeufer_Ort")
        .PostalAddress.CountryCode = Wert("Verkaeufer_Land")
    End With

    With objInvoice.Buyer                       ' Käufer, also der Rechnungsempfänger
        .Name = Wert("Kunde_Name")
        .PostalAddress.AddressLine1 = Wert("Kunde_Strasse")
        .PostalAddress.PostCode = Wert("Kunde_PLZ")
        .PostalAddress.City = Wert("Kunde_Ort")
        .PostalAddress.CountryCode = Wert("Kunde_Land")
    End With

    Set NeueXRechnung = objInvoice
End Function

Den Ländercode tragen Sie nach ISO 3166 ein, für Deutschland DE. Die Funktion Wert liest bewusst .Text: Eine Postleitzahl mit dem Zahlenformat 00000 liefert über .Value nur 1234 statt 01234. Für Beträge und Mengen nehmen Sie dagegen .Value, damit Zahlen als Zahlen ankommen.

Beispiel 2: Positionen in einer Schleife

Die Positionen stehen untereinander. Eine Schleife legt je Zeile ein Positionsobjekt an und hängt es an die Rechnung. Das Beispiel nutzt die Positionsklasse für das Profil Extended, das auch Unterpositionen erlaubt:

' Rechnungspositionen ab Zeile 20 (Spalten A bis E) in die E-Rechnung übertragen
Sub PositionenUebertragen(objInvoice As Object, wsRechnung As Worksheet)
    Dim objLine As SmartTools_Invoicing_EN16931_ZUGFeRD_Extended_InvoiceLine
    Dim lngZeile As Long

    lngZeile = 20
    Do While Len(wsRechnung.Cells(lngZeile, 1).Value) > 0     ' bis zur ersten leeren Zeile
        Set objLine = New SmartTools_Invoicing_EN16931_ZUGFeRD_Extended_InvoiceLine
        With objLine
            .Id = CStr(wsRechnung.Cells(lngZeile, 1).Value)
            .ItemInformation.Name = wsRechnung.Cells(lngZeile, 2).Value
            .InvoicedQuantity = wsRechnung.Cells(lngZeile, 3).Value
            ' Einheitencode, etwa H87 für Stück oder HUR für Stunde
            .InvoicedQuantityUnitOfMeasureCode = wsRechnung.Cells(lngZeile, 4).Value
            ' Kaufmännisch auf zwei Stellen runden: Tabellenfunktion statt VBA-Round
            .PriceDetails.NetPrice = Application.WorksheetFunction.Round(wsRechnung.Cells(lngZeile, 5).Value, 2)
        End With
        objInvoice.AddLine objLine
        lngZeile = lngZeile + 1
    Loop
End Sub

Achten Sie auf die Rundung. Die VBA-Funktion Round rundet nach der Bankierregel zur geraden Ziffer: Round(0.125, 2) ergibt 0,12, die Tabellenfunktion über Application.WorksheetFunction.Round dagegen 0,13 wie auf jeder Rechnung. Weichen Positionen und Summe um einen Cent ab, meldet die Prüfung einen Fehler.

Beispiel 3: ZUGFeRD aus der gewohnten PDF

Für ZUGFeRD geben Sie das Rechnungsblatt zuerst wie gewohnt als PDF aus. Danach erhält Save den Pfad dieser PDF. Die Bibliothek erzeugt die XML-Daten, bettet sie ein und wandelt die Datei in PDF/A-3:

' Rechnungsblatt als PDF ausgeben und als ZUGFeRD-Rechnung speichern
Sub ZUGFeRDErzeugen(objZFRechnung As Object, wsRechnung As Worksheet, strPdf As String)
    ' Schritt 1: das gewohnte Rechnungsbild als PDF ausgeben
    wsRechnung.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPdf, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

    ' Schritt 2: XML erzeugen, einbetten und in PDF/A-3 umwandeln
    objZFRechnung.Save strPdf
End Sub

Das Objekt legen Sie mit der ZUGFeRD-Klasse Ihres Profils an, die Daten weisen Sie wie in den Beispielen 1 und 2 zu.

Beispiel 4: Vor dem Versand prüfen

Validate prüft die E-Rechnung nach den offiziellen Regeln und liefert ein Array der Verstöße. Die Hilfsfunktion zählt sie auch dann richtig, wenn das Ergebnis leer ist. Ist alles in Ordnung, öffnet sich die Übersicht der Bibliothek zur Sichtkontrolle:

' Anzahl der Verstöße aus dem Ergebnis von Validate, auch wenn es leer ist
Function AnzahlVerstoesse(arrVerstoesse As Variant) As Long
    On Error GoTo KeinErgebnis
    AnzahlVerstoesse = UBound(arrVerstoesse) - LBound(arrVerstoesse) + 1
    Exit Function
KeinErgebnis:
    AnzahlVerstoesse = 0
End Function

' E-Rechnung prüfen, Ergebnis melden und die gültige Rechnung zur Kontrolle anzeigen
Sub RechnungPruefen(objInvoice As Object, strDatei As String)
    Dim lngAnzahl As Long
    lngAnzahl = AnzahlVerstoesse(objInvoice.Validate())
    If lngAnzahl = 0 Then
        xlE_Rechnung.ShowInvoiceOverview strDatei
    Else
        MsgBox "Die E-Rechnung enthält " & lngAnzahl & _
            " Regelverstöße und sollte nicht versendet werden.", _
            vbExclamation, "Prüfung E-Rechnung"
    End If
End Sub

In der Musterlösung sehen Sie, wie die Einträge des Arrays aufgebaut sind. So zeigen Sie die Meldungen im Klartext an oder speichern sie als Protokoll neben der Rechnung.

Eingangsrechnungen einlesen

Die Bibliothek arbeitet auch in die andere Richtung. Import liest eine empfangene E-Rechnung vollständig ein, danach greifen Sie auf Rechnungssteller, Betrag oder Fälligkeit zu und schreiben sie etwa in eine Budgettabelle:

' Empfangene E-Rechnung einlesen und den Rechnungssteller in die nächste freie Zeile schreiben
Sub EingangsrechnungLesen(objInvoice As Object, strDatei As String, wsEingang As Worksheet)
    Dim lngZeile As Long

    objInvoice.Import strDatei                   ' komplette Datenstruktur einlesen
    lngZeile = wsEingang.Cells(wsEingang.Rows.Count, 1).End(xlUp).Row + 1
    wsEingang.Cells(lngZeile, 1).Value = objInvoice.Seller.Name
    wsEingang.Cells(lngZeile, 2).Value = strDatei
    ' weitere Felder wie Betrag und Fälligkeit analog
End Sub

Tipps aus unseren Projekten

  • Benannte Bereiche statt Zelladressen. Der Code bleibt unabhängig vom Layout der Vorlage.
  • Eine Prozedur je Aufgabe. Kopfdaten, Positionen, Ausgabe und Prüfung getrennt. So wechseln Sie das Format, ohne alles anzupassen.
  • Kaufmännisch runden. Mit der Tabellenfunktion, siehe Beispiel 2.
  • Einheitencodes aus einer Liste. H87 für Stück, HUR für Stunde, als Auswahlliste hinterlegt.
  • Immer prüfen. Bauen Sie die Prüfung fest ein und verhindern Sie den Versand, solange Verstöße gemeldet werden.
  • Protokoll und Rechnung zusammen ablegen. Mit der Rechnungsnummer im Dateinamen findet sich beides sofort.
  • Teil- und Schlussrechnungen testen. Die Bibliothek verrechnet auch vorherige Teilrechnungen in der Schlussrechnung. Testen Sie mit echten Projektdaten.
  • Lizenzen planen. Eine Lizenz je Arbeitsplatz. Für Entwickler, die Lösungen an viele Anwender verteilen, hat SmartTools besondere Konditionen.

Aus der Praxis: drei Schaltflächen statt Formatwissen

Für ein Planungsbüro haben wir die Bibliothek in eine bestehende Rechnungsmappe mit bis zu 50 Teilrechnungen und einer Schlussrechnung je Projekt eingebaut. Die Anwender sehen drei Schaltflächen für XRechnung, ZUGFeRD und die klassische PDF. Datenübernahme, Verrechnung der Teilrechnungen, Prüfung und Ablage laufen im Hintergrund. Aktualisierungen der Formate liefert SmartTools mit neuen Versionen der Bibliothek. Anpassungen in der Mappe sind nur nötig, wenn eine neue Vorgabe zusätzliche Pflichtangaben verlangt.

Bibliothek bestellen oder einbauen lassen

Die SmartTools E-Rechnung VBA-Bibliothek für Excel bestellen Sie direkt bei uns, Preis und Lizenzstaffel finden Sie bei den Werkzeugen von SmartTools. Sie möchten nicht selbst programmieren? Wir bauen die Bibliothek in Ihre Rechnungsmappe ein, mit Schaltflächen, Prüfung und Ablage. Mehr auf der Seite E-Rechnung aus Excel, Word und Access.

Passende Beiträge

Lieber machen lassen?

E-Rechnungen direkt aus Excel, Word und Access.

Wir richten ZUGFeRD und XRechnung in Ihren vorhandenen Vorlagen ein, damit Ihre Abläufe bleiben, wie sie sind.