Makro | Excel Makro Bibliothek

Mit VBA einen Text in die Windows Zwischenablage kopieren

22. Oktober 2023 von Marco Schade

Es ist ganz einfach mit VBA eine Zelle zu kopieren und an einer anderen Stelle einzufügen. Am Beispiel der Zelle C3 würde das zum Beispiel mit einem einzeiligen VBA Befehl funktionieren.

Range("C3").Copy

Damit kopieren Sie allerdings nicht den Text der Zelle, sondern die gesamte Zelle inklusive der Formatierung. Das hat manche möglicherweise unerwünschten Auswirkungen beim Einfügen an anderer Stelle. Innerhalb von Excel werden so kopierte Zellen beim Aktivieren einer beliebigen Zelle über einen Doppelklick wieder aus dem Speicher gelöscht. Ein häufiges Ärgernis, dass viele Anwender bereits kennen werden, wenn von Hand eine oder mehrere Zellen kopiert worden sind. Auch dann kann nicht zuvor eine Zelle aktiviert werden, ohne dass der Zwischenspeicher wieder geleert wird. Ein weiteres Problem besteht, wenn Sie den Inhalt außerhalb von Excel einfügen möchten. Da erhält man je nach Programm dann nicht nur den Inhalt der Zelle, sondern auch die Zelle und Formatierung eingefügt.

Möchten Sie also den reinen Text kopieren, ist es sinnvoller den Inhalt der Zelle in die Windows Zwischenablage zu kopieren. Die Windows Zwischenablage wird nicht so schnell geleert, und enthält den reinen Text der Zelle. Aktivieren Sie eine Zelle, bleibt der Inhalt der Zwischenablage erhalten.

Daten in die Windows Zwischenablage kopieren

Um einen Text in die Windows Zwischenablage zu kopieren ist etwas mehr VBA Code erforderlich. Zudem ist im VBA Editor über den Menüpunkt Extras -> Verweise zu prüfen, ob die Verweisbibliothek „Microsoft Forms 2.0 Object Library“ aktiviert ist.

Die Bibliothek ist erforderlich um das Objekt HtmlFile nutzen zu können. Ist dies sichergestellt können Sie die folgende VBA Routine in ein Modul kopieren und ausführen.

Sub Zwischenablage()
'erfordert die Verweis-Bibliothek: Microsoft Forms 2.0 Object Library

Dim strText As String
Dim objHF As Object

strText = Range("C3").Value

Set objHF = CreateObject("HtmlFile")

objHF.ParentWindow.ClipboardData.SetData "text", strText

End Sub

So funktioniert das Makro

Die VBA-Routine “Zwischenablage” kopiert den Text aus Zelle C3 in die Zwischenablage des Windows-Betriebssystems.

Die Routine beginnt damit, dass sie zwei Variablen deklariert:

- strText: Diese Variable wird verwendet, um den Text zu speichern, der kopiert werden soll.

- objHF: Diese Variable wird verwendet, um ein Objekt vom Typ “HtmlFile” zu erstellen.

Zunächst wird der Inhalt der Zelle in die Variable strText gespeichert:

strText = Range("C3").Value

Danach wird ein Objekt vom Typ “HtmlFile” erstellt:

Set objHF = CreateObject("HtmlFile")

Dieses Objekt wird verwendet, um die Zwischenablage des Windows-Betriebssystems zu öffnen.

In der letzten Zeile wird der Text aus der Variablen strText in die Zwischenablage kopiert:

objHF.ParentWindow.ClipboardData.SetData "text", strText

Die Methode SetData wird verwendet, um Daten in die Zwischenablage zu kopieren. In diesem Fall wird der Text in der Variablen strText mit der Datenformat-ID “text” in die Zwischenablage kopiert.

Download

zwischenablage.xlsm
Die Exceldatei enthält ein einfaches Beispiel, wie der Inhalt einer Zelle in die Windows Zwischenablage kopiert werden kann.

Anzahl verarbeiteter Datensätze mit VBA protokollieren

6. März 2022 von Marco Schade

Heute stelle ich Ihnen eine Möglichkeit vor, die Anzahl der verarbeiteten Datensätze fortlaufend zu zählen. Das ist insbesondere dann sehr hilfreich, wenn Sie kostenpflichtige Webservices wie zum Beispiel die Google Maps API nutzen. Sie erhalten so einen sehr einfachen Überblick über die Anzahl der abgefragten Datensätze und können entsprechend die anfallenden Kosten kalkulieren. Auch ist es möglich einen monatliches Abfragebudget zu definieren, das nicht überschritten werden soll. Darüber hinausgehende Anfragen an die API-Schnittstelle können anschließend unterbunden werden.

Die nachfolgend vorgestellten VBA Routinen beinhalten allerdings nur die notwendigen Hilfsmittel um entsprechende Datenabfragen zu zählen und zu protokollieren. Das Beispiel beinhaltet kein Makro um tatsächlich eine Anfrage an die Google Maps API zu senden. Das sollte an dieser Stelle nur ein Hinweis für ein praktisches Anwendungsbeispiel sein.

VBA Routinen zum Messen der verarbeiteten Datensätze

Im Wesentlichen werden für das zählen und protokollieren der verarbeiteten Datensätze zwei VBA Routinen zusätzlich zur eigentlichen VBA Routine für die Verarbeitung der Datensätze benötigt. Bevor die Datensätze verarbeitet werden, wird zunächst der Stand der bereits verarbeiteten Datensätze aus einer externen Datei eingelesen. Hierfür wird im Beispiel eine einfache unformatierte Textdatei im TXT-Format genutzt. Reihenfolge der Verarbeitung:

Mit VBA Email versenden ohne Outlook

29. Juni 201915. Juni 2019 von Marco Schade

Im ersten Teil wurde der Emailversand mit Outlook erklärt. Bei dieser Variante gibt es sehr viele Optionen um den Emailversand optimal anzupassen. Eine Email mit ganz einfachen Funktionen kann aber auch ohne die Nutzung von Microsoft Outlook sehr einfach und schnell versendet werden.

Teil 1: Per VBA-Makro eine Email mit Outlook versenden
Teil 2: Mit VBA Email versenden ohne Outlook

Email mit FollowHyperlink erzeugen

Die Methode über einen Mailto-Hyperlink ist eine sehr einfache Möglichkeit zum Erzeugen einer Email, allerdings auch mit einem eingeschränkten Funktionsumfang.

Dateien mit Excel VBA erzeugen – Teil 2 HTML-Format

13. Februar 202124. September 2017 von Marco Schade

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.

Per VBA-Makro eine Email mit Outlook versenden

29. Juni 20196. März 2017 von Marco Schade

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.

Teil 1: Per VBA-Makro eine Email mit Outlook versenden
Teil 2: Mit VBA Email versenden ohne Outlook

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):

Dateinamen eines Verzeichnisses mit VBA auslesen und in Excel schreiben

15. Februar 202231. Januar 2017 von Marco Schade

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 um Dateinamen mit VBA auslesen zu können.

Bevor Sie starten gibt es zwei Wichtige Punkte zu beachten. Vor dem Ausführen des Makros müssen Sie den Ordnername im VBA Code 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”.

Mit VBA nach Datum filtern

17. Dezember 202127. Dezember 2015 von Marco Schade

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

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.

Laufen Excel Makros auf Smartphone und Tablet Apps?

15. Januar 202114. November 2015 von Marco Schade

Inzwischen erhalte ich vermehrt Anfragen zu Makros die z.B. auf iOS-Systemen wie dem iPhone oder dem iPad von Apple laufen sollen. Das Smartphone wandelt sich immer mehr zum mobilen Büro. Emails checken und im Internet surfen ist längst üblich. In vielen Unternehmen werden inzwischen auch Office Anwendungen auf mobilen Geräten genutzt.

Aufgrund der Preisentwicklungen auf dem Mobilfunkmarkt auch kein Wunder. Inzwischen gibt es für unter 10 Euro eine Allnet-Flat in alle Mobilfunknetze und das Festnetz, sowie einer 3 GB Internet-Flatrate (z.B. Tarif LTE2000 bei www.deutschlandsim.de). Und das Beste bei diesen Angeboten, inzwischen gibt es sie ohne sich für 24 Monate binden zu müssen. Mindestlaufzeit 1 Monat! Sobald mein 24-Monate-Knebelvertrag im Januar 2016 ausläuft, werde ich mir auch einen monatlich kündbaren Tarif sichern.

Excel Makros auf Smartphones und Tablets nutzen

Aber zurück zu den Anfragen bezüglich Makros, die auf dem Smartphone oder Tablet laufen sollen. Aktuell entwickle ich ausschließlich Makros, die mit Windows Betriebssystemen garantiert kompatibel sind.

VBA Unterstützung in alternativen Office Programmen

Bei anderen Betriebssystemen und alternativen Office-Anwendungen wie z.B. WPS Office, LibreOffice oder Open Office kann die Unterstützung von VBA nicht immer hundertprozentig gegeben sein. Ebenfalls problematisch ist die VBA Unterstützung für Microsoft Office für Mac. Hintergrund ist, dass in unterschiedlichen Office Versionen verschiedene VBA Befehle unterstützt werden. In vielen Fällen liegt das an unterschiedlichen Objekt-Bibliotheken.

Hier kann beim nachträglichen Einbinden der notwendigen Objekt-Bibliotheken die Funktion des Makros ggf. noch gerettet werden. In einigen Fällen müssen jedoch auch VBA Routinen umgeschrieben und in unterschiedlichen Systemen getestet werden. Anfragen zu Programmieraufträgen für iOS oder Android Geräten biete ich daher aktuell nicht an.

Zusammenhängende Datensätze farbig hervorheben

13. Februar 202118. August 2015 von Marco Schade

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. Dadurch können Sie die Zellen einer Zeile einfacher im Blick behalten. Mehrere zusammenhängende Zeilen hervorheben, ist hiermit allerdings nicht möglich.

Möchten Sie deshalb 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.

Wissenswert: Sverweis Funktion und Makro Alternative

24. Oktober 20226. Mai 2015 von Marco Schade

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.

Teil 1: Wissenswert: Sverweis Funktion und Makro Alternative
Teil 2: Besser als SVERWEIS: Die XVERWEIS Funktion

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.