Dateien mit Excel VBA erzeugen – Teil 3 XML-Format

Dateien aus Excel können vielfach direkt in andere Anwendungen importiert werden. Manche Systeme/Anwendungen benötigen jedoch speziell aufbereitete Daten in bestimmten Speicherformaten um diese weiterverarbeiten oder nutzen zu können. An einem einfachen Beispiel zeige ich Ihnen in drei Teilen, wie Sie mit einem Excel-Makro Daten aus einer Exceltabelle in unterschiedlichen Speicherformaten erzeugen.

Teil 1: CSV/TXT-Format
Teil 2: HTML-Format
Teil 3: XML-Format

Bei XML-Dateien handelt es sich um eine erweiterbare Auszeichnungssprache (Englisch: Extensible Markup Language), abgekürzt XML, für die Darstellung von hierarchisch strukturierten Daten in Format einer Textdatei. Der Vorteil von XML-Dateien: Dieses Format kann sowohl von Menschen als auch von Maschinen gelesen werden. Die Syntax von XML wird von vielen formalen Programmiersprachen verwendet und ist ein Instrument um W3C konform eine offene verständliche Informationslandschaft für Mensch und Maschine zu gewährleisten. Auch die gängige Dokumentensprache HTML wurde als XHTML in dieses Konzept integriert, so dass XML als Definitionsbasis zu Grunde liegt. XML-Dateien werden für die Weitergabe und Weiterverarbeitung von Informationen genutzt und werden aufgrund der einfachen Syntax von vielen Systemen unterstützt. Soviel zum Hintergrund von XML-Dateien.

weiterlesen

 

Dateien mit Excel VBA erzeugen – Teil 2 HTML-Format

Dateien aus Excel können vielfach direkt in andere Anwendungen importiert werden. Manche Systeme/Anwendungen benötigen jedoch speziell aufbereitete Daten in bestimmten Speicherformaten um diese weiterverarbeiten oder nutzen zu können. An einem einfachen Beispiel zeige ich Ihnen in drei Teilen, wie Sie mit einem Excel-Makro Daten aus einer Exceltabelle in unterschiedlichen Speicherformaten erzeugen.

Teil 1: CSV/TXT-Format
Teil 2: HTML-Format
Teil 3: XML-Format

Die Erstellung von HTML-Dateien mit Inhalten aus einer Exceltabelle eignet sich besonders für die automatisierte Bereitstellung von Berichten. Der Vorteil an diesem Format ist, dass die erstellten Dateien mit allen gängigen Webbrowsern aufgerufen werden können und eine optisch ansprechende Darstellung der zusammengefassten Inhalte möglich ist. Die einzelnen Anwender benötigen zur Ansicht der erstellten HTML-Dateien selbst weder MS Excel noch eine vergleichbare Office Anwendung. Auch kann so sichergestellt werden, dass die ursprüngliche Datei nicht verändert oder gelöscht wird. HTML-Dateien können auch wahlweise im Internet oder nur im Firmennetzwerk bereitgestellt werden. Die Anwendungsmöglichkeiten sind zahlreich.

weiterlesen

 

Dateien mit Excel VBA erzeugen – Teil 1 CSV/TXT-Format

Dateien aus Excel können vielfach direkt in andere Anwendungen importiert werden. Manche Systeme/Anwendungen benötigen jedoch speziell aufbereitete Daten in bestimmten Speicherformaten um diese weiterverarbeiten oder nutzen zu können. An einem einfachen Beispiel zeige ich Ihnen in drei Teilen, wie Sie mit einem Excel-Makro Daten aus einer Exceltabelle in unterschiedlichen Speicherformaten erzeugen.

Teil 1: CSV/TXT-Format
Teil 2: HTML-Format
Teil 3: XML-Format

Für den Import/Export von Dateien werden grundsätzlich verschiedene Dateiformate genutzt. Die gängigsten hierfür genutzten Formate sind Textdateien, bei denen die Datensätze ohne die Informationen zur Formatierung der Zellwerte gespeichert werden und mit einem fest definierten Trennzeichen (z.B. Semikolon) oder einer festgelegten Breite die Abgrenzung der Spalten sicherstellen.

weiterlesen

 

Excel Spaltenbuchstaben ermitteln

In VBA wird im Allgemeinen nicht der Spaltenbuchstabe, sondern die Spaltennummer verwendet. Spalte A entspricht der Spaltennummer 1, Spalte B der Spaltennummer 2, Spalte C der Spaltennummer 3 usw.

Möchten Sie nun aus einer Spaltennummer wieder den Spaltenbuchstabe ermitteln gibt es hierfür verschiedene Ansätze, die je nach Verwendungszweck mehr oder weniger geeignet sind.

Fehlerhafter VBA Code auf der Microsoft Support Seite

Im Internet gibt es allerdings auch Lösungsansätze, die nicht ungeprüft verwendet werden sollten. So stellt z.B. selbst Microsoft auf einer Support Seite einen fehlerhaften Algorithmus für die Ermittlung des Spaltenbuchstaben vor.

Zunächst einmal ist dieser generell nur auf zwei Stellen ausgelegt. Seit der Excel 2007 sind inzwischen jedoch 16.384 Spalten bis zum Spaltenbuchstabe XFD vorhanden. Um alle Spaltenbuchstaben ermitteln zu können muss also in jedem Fall ein Algorithmus genutzt werden, der dreistellig die Spaltennummer in den jeweiligen Spaltenbuchstabe(n) umwandeln kann. Davor gab es einen Spaltenbereich der nur 256 Spalten bis Spalte IV unterstützte.

Aber auch in diesem Fall hätte der Algorithmus auf der Microsoft Seite Fehler produziert. Der erste Fehler tritt nämlich bereits bei Spalte 53 (BA) auf. Hier wirft der Microsoft Alogrithmus A[ aus. Mit jedem Durchlauf von A bis Z verschiebt sich fortan der Fehler um eine weitere Spalte (CA, CB / DA, DB, DC / EA, EB, EC, ED / …). Ab Spalte 4508 (FQJ) erzeugt das Makro dann sogar die erste Fehlermeldung.

Excel VBA Spaltenbuchstabe aus Zahl ermitteln

Nachfolgend nun zwei Lösungsvorschläge, die für alle 16.384 Spalten aus der Spaltennummer den Excel Spaltenbuchstaben ermitteln.

weiterlesen

 

Per VBA-Makro eine Email mit Outlook versenden

Sie möchten einen Bericht aus einer Exceltabelle erstellen und anschließend per Email versenden? Auch der Versand einer kompletten Exceldatei oder eines bestimmten Tabellenblatts ist relativ einfach mit VBA realisierbar. Mit dem nachfolgenden Musterbeispiel zeige ich Ihnen wie Sie mit Hilfe eines VBA-Codes eine Email versenden können. Voraussetzung für die Nutzung ist, Sie haben neben Microsoft Excel auch das Programm Microsoft Outlook installiert und darin ein Emailkonto eingerichtet.

Weitere Voraussetzung, im VB-Editor muss in der Exceldatei eine der beiden Objektbibliotheken aktiviert sein:

  • Microsoft Outlook 15.0 Object Library
  • Microsoft 15.0 Object Library

Die Nummer 15.0 steht hierbei für die genutzte Office-Version (15.0 entspricht z.B. Office 2013).

Nun zum eigentlichen VBA-Code. Hier gibt es einige Möglichkeiten den Versand individuell zu gestalten. In den beiden folgenden Makros wird die Email erstellt und zunächst angezeigt (1. VBA-Code), bzw. automatisch versendet (2. VBA-Code):

weiterlesen

 

Dateinamen eines Verzeichnisses mit VBA auslesen und in Excel schreiben

Mit einem kleinen Makro können Sie ganz bequem sämtliche Dateinamen inklusive der Dateiendungen in Excel schreiben. Das nachfolgende Makro brauchen Sie hierfür lediglich in ein Modul Ihrer Exceldatei hinzufügen. Alternativ können Sie auch die im Anschluss an diesen Beitrag anghängte Musterdatei nutzen.

Bevor Sie starten gibt es zwei Wichtige Punkte zu beachten. Vor dem Ausführen des Makros müssen Sie den Ordnername noch abhändern. Im Beispiel würde das Makro ansonsten im Laufwerk “C:\” den Ordner mit dem Namen “Ordnername” suchen und falls vorhanden die Dateien dieses Ordners in Spalte A ab Zeile 1 auflisten. Sofern der Ordnerpfad nicht vorhanden ist, erhalten Sie die Fehlermeldung “Laufzeitfehler ’76’: Pfad nicht gefunden”.

weiterlesen

 

Stolperstein Formatierung in UserForm TextBox

In programmierten Formularen (UserForm) kommen häufig Eingabefelder in Form einer TextBox zur Anwendung. Meistens sollen die darüber eingegebenen Werte umgehend weiter verarbeitet werden. Und hier gilt es ggf. gleich von Beginn an Fehler zu vermeiden. Der Inhalt einer TextBox wird nämlich ohne weiteres Eingreifen grundsätzlich immer als String behandelt. Sollen in einer TextBox jedoch Zahlen erfasst werden, die anschließend zum Beispiel miteinander verglichen oder summiert werden sollen, müssen diese zuvor umformatiert werden.

Was passiert, wenn die Inhalte der TextBox-Felder nicht formatiert werden? Den Fehler wird man unter Umständen nicht sofort merken.

TextBox Eingabefelder in Userform

Unterschiede in der Sortierung von Texten und Zahlen:
Angenommen Sie haben eine UserForm in der die Eingabe einer Zahl “von” und einer Zahl “bis” erfolgen soll. Anschließend möchten Sie prüfen, ob die Eingabe korrekt erfolgt ist, also die Zahl “von” wirklich kleiner als die Zahl “bis” ist.

weiterlesen

 

Mit VBA nach Datum filtern

Ziemlich banal ist eigentlich der heutige VBA Tipp. Allerdings ist das Internet voll von Fragen zu diesem Thema. Und die Lösungsvorschläge funktionieren mal besser, mal schlechter. Meistens sind es improvisierte Ideen, die das eigentliche Problem umgehen (z.B. Umformatieren in einen String etc.). Die Lösung dieses alltäglichen Problems bereit daher offensichtlich so manches Kopfzerbrechen. Gerade auch deshalb weil der Makro Rekorder das Problem nicht lösen kann. Gemeint ist das Ansteuern eines Autofilters über ein VBA Makro wenn eine Datumsspalte gefiltert werden soll. Nachfolgend stelle ich Ihnen jeweils einen passenden VBA Code vor um eine Datumsspalte nach den gängien Varianten:

  • Datum ist gleich
  • Datum ist größer als
  • Datum ist kleiner als
  • Datum liegt zwischen
  • Datum ist größer als das aktuelle Tagesdatum
VBA Makro Lösung

Grundsätzlich ist bei den nachfolgenden Beispielen zu beachten, dass die zu filternde Spalte im Datumsformat “TT.MM.JJJJ” vorliegen muss. Ggf. können Sie die Spalte umformatieren, wenn ein anderes Format vorliegt. Sofern eine andere Datums-Variante genutzt werden soll, sind die Makros entsprechend anzupassen. Für die jeweiligen Beispiele wurde das Datum in eine Variable (Datum, Datum1, Datum2) vom Datentyp “Date” gespeichert. Sie können natürlich auch eine Inputbox, einen Zellwert oder ähnliches als Grundlage für die Filterfunktion verwenden.

weiterlesen

 

Zusammenhängende Datensätze farbig hervorheben

Ein häufiges Problem bei großen Datenmengen ist die fehlende Übersichtlichkeit. Excel bietet hier nur für einfache Konstellationen sinnvolle Lösungsansätze. So kann über die Funktion “Als Tabelle formatieren” zum Beispiel jede zweite Zeile farbig hervorgehoben werden.

Möchten Sie dagegen aus einer großen Datenmenge zusammengehörige Zeilen optisch hervorheben, ist das in der Regel, je nach Größe der Tabelle, mit viel händischer Arbeit verbunden.

weiterlesen

 

Wissenswert: Sverweis Funktion und Makro Alternative

Die Sverweis Formel-Funktion ist eine der beliebtesten Excel Funktionen überhaupt. Die Nutzung kann allerdings auch Probleme bereiten und die Performance bei großen Tabellen stark beeinträchtigen. In diesem Beitrag erfahren Sie, wofür Sie die Sverweis-Funktion nutzen können, worauf Sie achten müssen und schließlich was alternativ Makros leisten können. Dazu stelle ich Ihnen zwei Makros vor. Das erste Makro nutzt die Sverweis-Tabellenblatt-Funktion vlookup() in VBA und bietet somit alle Möglichkeiten der Formel-Funktion. Das zweite Makro stellt die Funktionsweise in ähnlicher Weise nach und bietet im Praxiseinsatz noch weitere Möglichkeiten, die mit der Sverweis-Funktion sonst nicht zu realisieren sind.

Sverweis-Formel im Tabellenblatt
Mit der Sverweis-Funktion können Sie grundsätzlich fehlende Daten aus einer anderen Tabelle zuspielen. Haben Sie zum Beispiel eine Liste mit Postleitzahlen bei denen die Orte fehlen können Sie über einen Sverweis in einer zusätzlichen Spalte die Orte in Abhänigkeit zur Postleitzahl hinzufügen. Voraussetzung ist, dass Sie über ein Postleitzahlenverzeichnis verfügen in denen zu allen Postleitzahlen die Orte gespeichert sind. Sie benötigen also eine Datenbasis aus der Sie die fehlenden Werte zuspielen können. Es sind dabei allerdings noch drei Punkte unbedingt zu beachten.

weiterlesen