Juli 2017

Der VBA-Kracher ‚Äď Juli 2017

In der Juli-Ausgabe erl√§utere ich den Unterschied zwischen Excel-Tabellenfunktionen und deren gleichnamigen VBA-Funktionen. Sie werden feststellen, dass da teilweise schon ein Unterschied besteht. Ebenso stelle ich Ihnen eine Technik vor, √ľber die Sie Daten aus einer Arbeitsmappe ziehen k√∂nnen, ohne diese vorher zu √∂ffnen. Weitere Themen sind die PDF Erstellung aus Excel-Tabellen und deren automatischem Versand √ľber Outlook. Am Ende des Newsletter erfahren Sie, wie Sie Arrays (Datenfelder) f√ľllen und in der Entwicklungsumgebung zur Ansicht bringen k√∂nnen.

Noch einmal zusammengefasst, hier die Themen in der Juli-Ausgabe:

  • Tabellenfunktionen und der Namensvetter bei den VBA-Funktionen
  • Daten aus einer geschlossenen Mappe ziehen
  • Ein PDF in Excel erstellen und direkt versenden
  • Datenfelder in der Entwicklungsumgebung sichtbar machen

 

Tabellenfunktionen und der Namensvetter bei den VBA-Funktionen

Viele Excel-Tabellenfunktionen, die auch f√ľr andere VBA-Applikationen wie Word oder Access n√ľtzlich sein k√∂nnen, wurden als separate VBA-Funktionen in der VBA-Bibliothek ausgelagert. Dabei verhalten sich die VBA-Funktionen manchmal etwas anders als die urspr√ľnglichen Excel-Funktionen. Dazu einmal zwei Beispiele:

  • Die Tabellenfunktion TEIL (Mid) √ü√† VBA-Funktion Mid
  • Die Tabellenfunktion GL√ĄTTEN (Trim) √ü√† VBA-Funktion Trim

Mid ist nicht gleich Mid

Mit Hilfe der Tabellenfunktion TEIL können Sie beispielsweise einen Teil von Zeichen aus einer Zelle abfragen. Die Syntax dieser Funktion lautet dabei.

TEIL(Zellenbezug; Startposition der Übertragung; Länge der Übertragung)

Das letzte Argument muss bei der WorksheetFunction zwingend angegeben werden. Bei der gleichnamigen VBA-Funktion MID kann das letzte Argument werggelassen werden. Die VBA Funktion √ľbertr√§gt eben dann automatisch die Zeichen, die eben noch da sind.

Im Direktfenster der Entwicklungsumgebung können Sie dazu einmal den folgenden Befehl eingeben und mit Enter bestätigen:

?mid(„Held-office“, 6)

  • office

Wenn Sie stattdessen die eine Formel in eine Zelle √ľber das Direktfenster schreiben m√∂chten, dann geben Sie die folgende Zeile in das Direktfenster ein und best√§tigen mit Enter.

Activecell.Formula=MID(„Held-office“, 6, 6)

Interessante Zusatzfunktionalität bei Trim

Mit Hilfe der Tabellenfunktion TRIM k√∂nnen Sie f√ľhrende und nachgestellte Leerzeichen aus einer Zelle entfernen. Dabei gibt es einen gewichtigen Unterschied zwischen der Excel-Tabellenfunktion GL√ĄTTEN (Trim) und der VBA-Funktion TRIM. Schauen wir uns zun√§chst die allgemeine VBA-Funktion Trim an. Geben Sie die folgende Zeile in das Direktfenster von Excel ein und best√§tigen Sie mit Enter.

?trim(“¬†¬† Bernd¬†¬†¬†¬† Held¬†¬† „)

  • „Bernd¬†¬†¬†¬† Held

 

Die Funktion entfernt, so wie es in der Online-Hilfe steht die f√ľhrenden und nachgestellten Leerzeichen in einem String.

Geben Sie nun die folgenden Zeile unter Verwendung der Worksheetfunction Trim im Direktfenster ein und bestätigen mit Enter.

?application.worksheetfunction.trim(“¬†¬† Bernd¬†¬†¬†¬† Held¬†¬† „)

  • „Bernd Held

 

Die Tabellenfunktion Trim entfernt zusätzlich zu vorangestellte und nachfolgenden Leerzeichen auch noch doppelte Leerzeichen zwischen beiden Worten.

Daten aus einer geschlossenen Mappe ziehen

Beim folgenden Beispiel soll ein Datenbestand aus einer geschlossenen Excel-Arbeitsmappe abgefragt und in einer anderen Mappe ausgegeben werden. Dabei sieht die Datenbasis wie folgt aus:

 

 

 

 

 

 

 

 

 

 

 

 

In der Daten-Arbeitsmappe befinden sich exemplarisch 1.000 Zeilen mit 20 Warengruppen und Ums√§tze im Wertebereich von 500 bis 1500 Euro. Im folgenden Makro werden beispielhaft alle Ums√§tze der Warengruppe ‚ÄěW2‚Äú gezogen, die Wertm√§√üig √ľber 1.000 Euro liegen.

Das Makro f√ľr diese Aufgabe lautet:

Sub DatenAusMappeZiehenOhnezu√Ėffnen()
   Dim cn As Object
   Dim rs As Object
   Dim strConnection As String
   Dim strSQL As String

  Tabelle1.UsedRange.ClearContents
  Set cn = CreateObject("ADODB.CONNECTION")
  strConnection = _
  "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=" & _
   ThisWorkbook.Path & "\Daten.xlsx"

   With cn
     .Open strConnection
     strSQL = _
   "SELECT * FROM [Tabelle1$] WHERE WG='W2' and Umsatz>1000 " & _
   "ORDER BY Umsatz desc"
     Set rs = CreateObject("ADODB.RECORDSET")
     With rs
      .Source = strSQL
      .ActiveConnection = strConnection
      .Open
      Tabelle1.Range("A1").CopyFromRecordset rs
      .Close
     End With
   End With

   cn.Close

End Sub '*** Quelle: VBA-Tanker, ID 6997 ***

Um den Datenzugriff auf die geschlossene Excel-Arbeitsmappe umzusetzen, greifen Sie auf die Zugriffsmethode ADO (Active Data Objects) zur√ľck. Diese Bibliothek wird neben DAO gerne f√ľr Access-Datenbanken eingesetzt und kann eben auch f√ľr Excel Mappen zum Einsatz kommen. √úber die Anweisung CreateObjekt wird ein Verwies auf diese Bibliothek erzeugt. Danach haben Sie Zugriff auf alle Methoden und Eigenschaften, die diese Bibliothek zur Verf√ľgung stellt. Geben Sie danach am Besten in einer String-Variablen den Treiber sowie den Pfad- und Dateinamen der Mappe an, die sie verarbeiten m√∂chten. Mit Hilfe der Methode Open wir die Verbindung hergestellt. Danach formulieren Sie die Abfrage mittels einer SQL-Anweisung und √ľbergeben Die der Eigenschaft Source. Das Ergebnis der Abfrage wird in einem Recordset-Element abgelegt, welches √ľber die Anweisung CreateObject erzeugt wird. √úber die Methode Open wird letztendlich die Abfrage durchgef√ľhrt und das Ergebnis im Recordset gespeichert. √úber die Methode CopyFromRecordset kann der Inhalt des Recordsets in einem Aufwasch in die Zieltabelle geschrieben werden. Danach wird der Recordset und die Verbindung √ľber die Methode Close geschlossen.

 

Ein PDF in Excel erstellen und direkt versenden

Beim folgenden Beispiel soll der Inhalt einer Tabelle in ein PDF Format gewandelt und direkt als E-Mail versendet werden. Das Makro f√ľr diese Aufgabe lautet:

Sub TabelleInPDFundEmailSenden()
 Dim objOut As Object
 Dim objOutMail As Object

 Tabelle1.ExportAsFixedFormat Type:=xlTypePDF, _
 Filename:=ThisWorkbook.Path & "\Export.pdf"
 
 Set objOut = CreateObject("Outlook.Application")
 Set objOutMail = objOut.createitem(0)

 With objOutMail
   .Subject = "Muster.."
   .To = "info@held-office.de"
   .body = "Sehr geehrte ..." & vbLf & "M.f.G" & vbLf & "Bernd Held"
   .attachments.Add ThisWorkbook.Path & "\Export.pdf"
   .Display
  '.send 'auskommentiert, um vorher das Ergebnis zu sehen
 End With
End Sub '*** Quelle: VBA-Tanker, ID 7883 ***

Die Methode ExportAsFixedFormat gibt es seit Office 2007. Sie erlaubt es eine Mappe bzw. ein Tabellenblatt in ein PDF-Dokument zu wandeln. Nach der Erstellung und Speicherung des PDFs wird von Excel aus eine Outlook-Sitzung mittels der Anweisung CreateObject erstellt und ein leeres E-Mail-Fenster √ľber die Methode Createitem eingef√ľgt. ¬†Danach erfolgt die n√§here Spezifikation des E-Mails √ľber diverse Eigenschaften. √úber die Eigenschaft Subject wird der Titel des e-Mails festgelegt. √úber die Eigenschaft To wird der Adressat der E-Mail angegeben. Mit Hilfe der Eigenschaft Body k√∂nnen Sie die eigentliche Nachricht des e-Mails formulieren. √úber die Methode Add, die Sie auf das Objekt Attachment anwenden k√∂nnen Sie einen E-Mail-Anhang festlegen. Verwenden Sie entweder die Eigenschaft Display, um das E-Mail-Fenster am Bildschirm anzuzeigen oder setzen Sie die Methode Send ein, um das E-Mail ohne vorherige Ansicht zu senden.

 

 

 

 

 

 

 

 

 

 

Datenfelder in der Entwicklungsumgebung sichtbar machen

Beim nachfolgenden Beispiel soll eine Zelle mit mehreren Informationen, die jeweils durch einen Zeilenumbruch getrennt sind, auseinander genommen und weiterverarbeitet werden.

Sehen Sie sich das folgende Makro an, das die einzelnen Teile aus der Zelle in ein Datenfeld schreibt und dieses dann Teile f√ľr Teil im Direktfenster der Entwicklungsumgebung ausgibt.

Sub ZellenTextMitZeilenumbruchSplitten()
Dim Vardat As Variant
Dim lngZZ As Long

 With Tabelle1

 Vardat = Split(.Range("D10").Value, vbLf)
 For lngZZ = 0 To UBound(Vardat)
    Debug.Print Vardat(lngZZ)
 Next lngZZ

 End With

End Sub '*** Quelle: VBA-Tanker, ID 8144

Mit Hilfe der Funktion Split k√∂nnen Sie einen Text zerteilen, der mit einem Trennzeichen voneinander getrennt ist. Dazu wird ein Datenfeld ben√∂tigt, das die entstehenden Teile aufnehmen kann.¬† √úber eine anschlie√üende Schleife wird das Datenfeld Feld f√ľr Feld abgearbeitet und im Direktfenster der Entwicklungsumgebung mit Hilfe der Anweisung Debug.Print ausgegeben.

Wichtig:

Wenn Sie das Makro √ľber die Taste F8 im Einzelschrittmodus Zeile f√ľr Zeile abarbeiten und dazu noch das Lokal-Fenster einblenden, dann sehen Sie die einzelnen Felder des Datenfeldes.