Software für Sie.

Application-Ereignisse gelten für die gesamte Anwendung, für alle Fenster, Arbeitsmappen und Tabellenblätter. Um mit Ereignissen des Application-Objekts zu arbeiten, mu� man zuvor eine öffentliche Variable der Objektklasse in einem Klassenmodul definieren und danach ein Objekt der neuen Klasse und darin wieder ein Objekt der Klasse. Letzteres wird wiederum in einem einfachen Modul erstellt.

Was sich zunächst ein wenig kompliziert anhört, ist im Grunde recht einfach zu verwirklichen.

Ab sofort können alle Ereignisse des Application-Objektes empfangen werden, wenn diese Routine ausgeführt wurde. Damit das neue Objekt immer zur Verfügung steht, sollte der letzte Code in Workbook_Open() der Mappe.

Wir können nun zum Besipiel jeden Blattwechsel in jeder offenen Mappe abfangen (also nicht nur in der mit dem Code), indem wir in das Klassenmodul eintragen:

Private Sub Anwendung_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name End Sub

Hinweis:
Um Application-Ereignisse zu deaktivieren, setzt man einfach den Verweis auf das "Anwendungsobjekt" auf Nothing, also Set Anwendungsobjekt.Anwendung = Nothing

Parameter:

Die Application-Ereignisse haben feste Parameter, die mit übergeben werden (im vorigen Code-Beispiel ist das schon an ByVal Sh As Object zu sehen):

Ereignisse:

Am einfachsten wählt man die natürlich über das Dropdown im Klassenmodul, wie es in der obigen Abbildung dargestellt ist. Hier eine kleine �bersicht:

Anwendung_NewWorkbook(ByVal Wb As Excel.Workbook)

Eine neue Arbeitsmappe wurde eingefügt.

Anwendung_SheetActivate(ByVal Sh As Object)

Ein anderes Blatt wurde aktiviert (Blattwechsel).

Anwendung_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

Doppelklick wurde ausgeführt.

Anwendung_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

Klick mit der rechten Maustaste.

Anwendung_SheetCalculate(ByVal Sh As Object)

Neuberechnung eines Tabellenblattes.

Anwendung_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Zelleninhalt eines Tabellenblattes wurde verändert.

Anwendung_SheetDeactivate(ByVal Sh As Object)

Ein Tabellenblatt wurde verlassen (Blattwechsel).

Anwendung_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Zellenmarkierung eines Tabellenblattes wurde geändert.

Anwendung_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)

Ein neues Fenster wurde aktiviert (Fensterwechsel).

Anwendung_WindowDeactivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)

Ein Fenster wurde verlassen (Fensterwechsel).

Anwendung_WindowResize(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)

Die Grö�e eines Fensters wurde verändert.

Anwendung_WorkbookActivate(ByVal Wb As Excel.Workbook)

Eine neue Arbeitsmappe wurde aktiviert (Arbeitsmappenwechsel).

Anwendung_WorkbookAddinInstall(ByVal Wb As Excel.Workbook)

Eine Arbeitsmappe wurde als Add-In installiert.

Anwendung_WorkbookAddinUninstall(ByVal Wb As Excel.Workbook)

Eine Arbeitsmappe wurde als Add-In deinstalliert.

Anwendung_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)

Eine Arbeitsmappe soll geschlossen werden.

Anwendung_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel As Boolean)

Eine Arbeitsmappe soll ausgedruckt werden.

Anwendung_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

Eine Arbeitsmappe soll geschlossen werden.

Anwendung_WorkbookDeactivate(ByVal Wb As Excel.Workbook)

Eine Arbeitsmappe wurde verlassen (Arbeitsmappenwechsel).

Anwendung_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As Object)

In einer Arbeitsmappe wurde ein neues Tabellenblatt eingefügt.

Anwendung_WorkbookOpen(ByVal Wb As Excel.Workbook)

Eine neue Arbeitsmappe wurde geöffnet.

Der folgende Code legt eine neue Arbeitsmappe an und erstellt im Klassenmodul der Arbeitsmappe eine kleine VBA-Routine.

Sub OpenProzedurAnlegen() Dim nWB As Workbook Dim mdlWB As Object Set nWB = Workbooks.Add Set mdlWB = nWB.VBProject.VBComponents("DieseArbeitsmappe") With mdlWB.CodeModule .InsertLines 3, "Private Sub Workbook_Open()" .InsertLines 4, " Msgbox ""Bin jetzt da!""" .InsertLines 5, "End Sub" End With End Sub

Falls die Meldung Der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher erscheint, müssen folgende Einstellungen vorgenommen werden:

Der Code nimmt sich die erste Exceldatei, die im Ordner in der Variablen ist. Die ruft er auf, druckt deren erstes Blatt und schlie�t sie wieder. Mit Dir() wird nun die nächste Datei genommen und die gleiche Prozedur erneut ausgeführt - so lange, bis mit Dir() keine weitere Datei gefunden wird.

Sub AlleDrucken() Dim WB As Workbook Dim strFName As String strFName = Dir("C:\Excel\*.xls") While strFName <> "" Set WB = Workbooks.Open(Filename:=strFName) WB.PrintOut WB.Close strFName = Dir() Wend End Sub

Der Code funktioniert zwar, wie er hier angegeben ist. Allerdings ist die Gefahr eines Fehlers gro�, wenn bereits Dateien geöffnet sind. Zumindest die Prüfung darauf sollte noch mit eingebaut werden.

Im Zusammenhang damit muss auch beachtet werden, ob eine offene Datei gespeichert ist. Wenn nicht, kommt es beim SchlieÃ?en zur entsprechenden â??Speichern?â??-Frage durch Excel und der Code wird angehalten.

Wie kann ich einen ausgewählten Bereich einer Exceltabelle als HTML-Mail via Outlook versenden?

Const strTempOrdner As String = "C:\Eigene Dateien\" Sub Aufruf() Mail_erstellen "info@example.org" End Sub Sub Mail_erstellen(strAdresse As String) Dim strQuelle As String Dim OutApp Dim OutMail Dim olMailItem Dim strSubject strQuelle = "$B$1:$R$58" strSubject = Range("B1") Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = strAdresse .CC = "" .BCC = "" .Subject = strSubject .HTMLBody = Uebersetzung(strQuelle) '.Send .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function Uebersetzung(strQuelle As String) Dim objFSO As Object Dim objInhalt As Object Dim strTempDatei As String strTempDatei = strTempOrdner & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=strTempDatei, _ Sheet:=ActiveSheet.Name, _ Source:=strQuelle, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set objFSO = CreateObject("Scripting.FileSystemObject") Set objInhalt = objFSO.GetFile(strTempDatei).OpenAsTextStream(1, -2) Uebersetzung = objInhalt.ReadAll objInhalt.Close Set objInhalt = Nothing Set objFSO = Nothing Kill strTempDatei End Function

Bei neueren Outlookversionen kann es zu einer Fehlermeldung kommen:

Ein Programm versucht, Nachrichten mit Item.Send zu senden. Ein Programm versucht, mit dem Befehl Item.Send der Anwendung Microsoft Outlook Visual Basic automatisch E-Mail-Nachrichten zu senden. Wenn Sie möchten, dass das Programm diese E-Mail-Nachricht sendet, klicken Sie auf Ja. Klicken Sie auf Nein, um das Programm zu beenden. Wenn Sie sich nicht sicher sind, welches Programm die E-Mail-Nachricht sendet oder warum, sollten Sie auf Nein klicken, um ein mögliches Verbreiten von Viren zu vermeiden.

Bei Anzeige dieser Meldung steht die Schaltfläche Ja 5 Sekunden lang nicht zur Verfügung.

Vielleicht ist dieser Tipp besser geeignet: Mails mit VBA und PHP versenden (mehrere Empfänger, mehrere Anhänge).

Eigentlich wäre es mit diesem (sicher selbst erklärendem) Code getan:

Dim strPfad As String, strDatei As String strPfad = ThisWorkbook.Sheets("Huber").Range("A10") strDatei = Dir(strPfad) Workbooks.Open Filename:=strPfad ThisWorkbook.Sheets("Huber").Copy after:=Workbooks(strDatei).Sheets(Sheets.Count) Workbooks(strDatei).Close True

Wenn es da nicht ein paar Fehleranfälligkeiten geben würde:

• Existiert die Datei überhaupt?
• Ist die Zieldatei bereits geöffnet?
• Gibt es in der Zieldatei bereits ein Blatt mit dem Namen?

Wenigstens diese Fragen sollten im Code noch geklärt werden, damit es keine Fehlermeldungen durch Excel und Abbrüche gibt. Anhand der Variablennamen sollte deutlich werden, was im Code passiert:

Sub Blatt_kopieren() Dim strPfad As String, strDatei As String Dim bolOffen As Boolean Dim objMappe As Object, objBlatt As Object strPfad = ThisWorkbook.Sheets("Huber").Range("A10") strDatei = Dir(strPfad) If strDatei = "" Then MsgBox "Datei existiert nicht" Exit Sub End If bolOffen = False For Each objMappe In Workbooks If objMappe.Name = strDatei Then bolOffen = True Exit For End If Next If bolOffen = False Then Workbooks.Open Filename:=strPfad For Each objBlatt In Workbooks(strDatei).Sheets If objBlatt.Name = "Huber" Then MsgBox "In der Zieldatei existiert bereits ein Blatt mit dem Namen 'Huber'.", vbOKOnly + vbExclamation, "Blatt existiert" If bolOffen = False Then Workbooks(strDatei).Close False Exit Sub End If Next ThisWorkbook.Sheets("Huber").Copy after:=Workbooks(strDatei).Sheets(Sheets.Count) Workbooks(strDatei).Close True End Sub

Im folgenden Beispiel muss natürlich noch der Ordner/Pfad beachtet und ggf. dem Dateinamen vorangestellt werden. Der kann in einer Zelle stehen oder im Code ergänzt werden.

Sub DatumEingeben() Dim varDatum varDatum = InputBox("Bitte Datum eingeben:") If Not IsDate(varDatum) Then MsgBox "Kein Datum!" Else varDatum = Format(varDatum, "yyyymmdd") & ".xls" End If Workbooks.Open varDatum End Sub

In einer Zelle

Die Nummer in eine Zelle schreiben, die auch ausgeblendet werden kann. Dann bei jedem Speichern die Nummer mit z. B. Range("A1") = Range("A1") + 1 erhöhen.

In den Dokumenteigenschaften

Die CustomDocumentProperties der Datei können auch mit VBA-Code verwendet werden. Dann wird die Nummer versteckt in dieser Datei gespeichert:

Dazu einmalig die Eigenschaft mit der folgenden Zeile erstellen:

ThisWorkbook.CustomDocumentProperties.Add Name:="lfdNr", LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:=0

Anzeigen kann man den Wert, der neu 0 ist, mit der Zeile:

MsgBox ThisWorkbook.CustomDocumentProperties("lfdNr").Value

Erhöht wird der der Wert mit

ThisWorkbook.CustomDocumentProperties("lfdNr").Value = ThisWorkbook.CustomDocumentProperties("lfdNr").Value + 1

Nach dem Erhöhen des Wertes nicht vergessen, die Datei zu speichern. Auch wenn diese Eigenschaften nicht sichtbar sind, sind sie doch in der Datei enthalten.

Separate Textdatei

Oder die Nummer in eine Datei auslagern, zum Beispiel so:

Sub lfdNr() Dim lngDNr As Long, intNr As Integer Dim strDName As String, strZielordner As String, strDateiname As String strZielordner = ThisWorkbook.Path & "\" 'Hier den Pfad verändern strDateiname = "Excel_lfdNr" 'Hier den Dateinamen verändern strDName = strZielordner & strDateiname & ".ini" intNr = 0 lngDNr = FreeFile If Dir(strDName) <> "" Then Open strDName For Input As #lngDNr Input #lngDNr, intNr Close #lngDNr End If intNr = intNr + 1 lngDNr = FreeFile Open strDName For Output As #lngDNr Print #lngDNr, intNr; Close #lngDNr ActiveCell.Value = intNr End Sub

Damit wird die Datei einfach geöffnet, wenn sie existiert:

Workbooks.Open FileName:="C:\Eigene Dateien\Test.xlsx"

Sicherheitshalber sollte vorher geprüft werden, ob die Datei existiert, um Fehlermeldungen zu vermeiden:

Dim strPfad As String strPfad = "C:\Temp\Test.xlsx" If Dir(strPfad) = "" Then MsgBox "Die Datei " & vbNewLine & strPfad & vbNewLine & "existiert nicht.", vbOKOnly + vbCritical, "Fehler" Else Workbooks.Open Filename:=strPfad End If

Ggf. sollte noch geprüft werden, ob eine Datei mit dem Namen bereits offen ist. Dazu kann man den Fehler abfangen oder vorher mit For Each objMappe in Workbooks prüfen, ob objMappe.name wie der Name der aufzurufenden Datei ist.

Dateiname in Zelle

Wenn der Dateiname in einer Zelle (hier A1) steht, kann so vorgegangen werden:

Sub Aufruf() Workbooks.Open FileName:=Range("A1") End Sub

Dabei darf natürlich nicht der Ordnerpfad vergessen werden, in dem sich diese Datei befindet. Der kann komplett mit in A1 stehen oder noch vorangestellt werden.

Auch hier sollte vorher noch mit Dir() geprüft werden, ob die Datei überhaupt existiert.

Mit Application.ScreenUpdating = False kann ausgeschaltet werden, dass der Excel-Bildschirm aktualisiert wird. Wenn oft automatisiert mit Zellen gearbeitet wird, kann das sinnvoll sein - einerseits flackert es dann nicht so, andererseits wird die Laufzeit teilweise erheblich verkürzt, wenn nicht sofort alle �nderungen angezeigt werden.

Allerdings sollte nicht vergessen werden, diese Aktualisierung wieder einzuschalten. Vergisst man dies, ist der Bildschirm eventuell an wichtiger Stelle â??eingefrorenâ??. Man kann das zwar einfach mit Application.ScreenUpdating = True beheben - wenn es jedoch einem anderen Anwender passiert, kann das mächtig irritieren.

Dim strDateiPfad As String strDateiPfad = "C:\eigene dateien\test.xlsx" Application.ScreenUpdating = False Workbooks.Open Filename:=strDateiPfad ThisWorkbook.Activate Application.ScreenUpdating = True

Sinnvoll kann es auch sein, vor der Ausführung des gewünschten Codes die Eigenschaft in einer Variablen zu speichern, wie z. B. bolScrUpd = Application.ScreenUpdating. Nach dem relevanten Code setzt man das dann auf den Ausgangszustand mit Application.ScreenUpdating = bolScrUpd zurück.

Der Code leert zunächst die Listbox. Anschlie�end zeigt er den Dialog zur Dateiwahl, in dem die gewünschte Datei gewählt werden kann. Der Dialog wird so lange gezeigt, bis Abbrechen gewählt wird.

Bei der Listbox handelt es sich um ein ActiveX-Steuerelement

Sub DateienSammeln() Dim varPfad ActiveSheet.ListBox1.Clear varPfad = "" Do While varPfad <> False varPfad = Application.GetOpenFilename("Excel-Dateien (*.xl*), *.xl*") If varPfad <> False Then ActiveSheet.ListBox1.AddItem varPfad Loop End Sub

Natürlich kann man den gesamten Pfad am Backslash splitten oder andere Stringoperationen anwenden. Am einfachsten ist es aber sicher, wenn man sich mit Dir() den Dateinamen zurückgeben lässt - das geht auch mit allen Dateien, nicht nur mit Exceldateien.

Dir()

Rein für den Dateinamen wäre dies eine einfache Möglichkeit:

Sub DateinamenExtrahieren() Dim varDName varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else varDName = Dir(varDName) MsgBox varDName End If End Sub

Für alle Angaben aus dem Pfad, also Ordner und Datei, könnte folgende Variante genutzt werden:

Sub DateiPfad() Dim strGesamt As String, strDatei As String, strOrdner As String strGesamt = Application.GetOpenFilename strDatei = Dir(strGesamt) strOrdner = Left(strGesamt, Len(strGesamt) - Len(Dir(strGesamt))) MsgBox strDatei & vbNewLine & strOrdner & vbNewLine & strGesamt End Sub

Wenn davon ausgegangen werden kann, dass die Zeichenfolge des Dateinamens einmalig im Pfad ist, kann auch einfach ersetzt werden:

Sub DateiAusPfad2() Dim varPfad, strOrdner As String, strDatei As String varPfad = Application.GetOpenFilename If varPfad <> False Then strDatei = Dir(varPfad) strOrdner = Replace(varPfad, strDatei, "") MsgBox strDatei & vbNewLine & strOrdner End If End Sub

Regulärer Ausdruck

Noch eine Variante für die Freunde regulärer Ausdrücke:

Sub DateiAusPfad3() Dim varDName, Regex As Object, regMatches, regMatch varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varDName) MsgBox regMatches(0).SubMatches(1) Set Regex = Nothing End If End Sub

Der Schrägstrich wurde aufgenommen, weil Pfade in Onedrive gespeicherter Dateien mit Schrägstrich geliefert werden.

Sollen Ordner und Dateiname zurückgegeben werden, wäre dies möglich:

Sub DateiAusPfad4() Dim varPfad, strOrdner As String, strDatei As String Dim Regex As Object, regMatches, regMatch varPfad = Application.GetOpenFilename If varPfad <> False Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) Set Regex = Nothing MsgBox strDatei & vbNewLine & vbNewLine & strOrdner End If End Sub

Die integrierte Funktion =ZELLE("Dateiname";A1) liefert den kompletten Pfad bis zum Tabellenblatt. Der Dateiname ist dabei in eckige Klammern eingeschlossen: Pfad[Dateiname]Blattname. Mit einem regulären Ausdruck können die einzelnen Bestandteile ausgegeben werden (ggf. noch Fehlerbehandlung einbauen):

Sub DateiAusZellFunktion() Dim strPfad, strOrdner As String, strDatei As String, strBlatt As String Dim Regex As Object, regMatches, regMatch strPfad = Evaluate("=cell(""filename"",A1)") If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) strBlatt = regMatches(0).SubMatches(2) Set Regex = Nothing MsgBox strBlatt & vbNewLine & strDatei & vbNewLine & strOrdner End Sub

Dynamische Matrixformel (Arrayformel) und verschütteter Array

Per benutzerdefinierter Funktion können seit Excel 365 auch die einzelnen Ordner bzw. Bestandteile eines Pfades in Zellen ausgegeben werden. Dazu diese Funktion als Beispiel:

Function PfadDetails(ByVal strPfad As String) PfadDetails = "" If strPfad <> "" Then Select Case True Case InStr(1, strPfad, "\") > 0: PfadDetails = Split(Replace(strPfad, "\\", "\"), "\") Case InStr(1, strPfad, "/") > 0: PfadDetails = Split(Replace(strPfad, "//", "/"), "/") End Select End If End Function

In die Zelle kommt dann diese Formel:

=PfadDetails(A5)

Wenn wie hier im Beispiel in A5 ein Pfad steht, werden an der Zelle mit der Formel die einzelnen Elemente des Pfades ausgegeben. Das letzte Element sollte bei einem kompletten Pfad zu einer Datei der Dateiname sein.

Das Beispiel mit der Funktion =ZELLE("Dateiname";A1) kann auch als Arrayformel verwendet werden:

Function DateiAusZellFunktion(strFktPfad) Dim arrTemp(1 To 3) Dim Regex As Object, regMatches, regMatch DateiAusZellFunktion = "" If strFktPfad <> "" Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strFktPfad) If regMatches.Count = 1 Then arrTemp(1) = regMatches(0).SubMatches(2) arrTemp(2) = regMatches(0).SubMatches(1) arrTemp(3) = regMatches(0).SubMatches(0) DateiAusZellFunktion = arrTemp End If Set Regex = Nothing End If End Function

In die Zelle kommt dann =DateiAusZellFunktion(ZELLE("Dateiname";A1)) und in ihr sowie den Nachbarzellen werden Blattname, Dateiname und Ordnerpfad erscheinen.

Formeln/integrierte Funktionen

Den aktuellen Ordner gibt diese Funktion zurück:

=INFO("Verzeichnis")

In anderen Sprachen geht übrigens auch einfach Basename(Pfad).

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

In diesem Beispiel sind die hochzuladenden Dateien als Konstanten angegeben:

Private Const strDatei1 As String = "C:\â?¦\test1.jpg" Private Const strDatei2 As String = "C:\â?¦\test2.jpg" Private Const strDatei3 As String = "C:\â?¦\test3.jpg"

Natürlich können die - wie ebenfalls die Script-URL - dem Script auch dynamisch übergeben werden, zum Beispiel als Array. Anpassungen müssen nur an der Sub DateiUpload() vorgenommen werden, der Rest kann so bleiben.

Dies ist der VBA-Code:

Private Const strURL As String = "https://â?¦Ihre URLâ?¦/â?¦Ihre PHP-Dateiâ?¦.php" Private Const strDatei1 As String = "C:\â?¦\test1.jpg" Private Const strDatei2 As String = "C:\â?¦\test2.jpg" Private Const strDatei3 As String = "C:\â?¦\test3.jpg" Sub DateiUpload() Dim strPostDaten As String, strBoundary As String strBoundary = "---------------------------7da24f2e50046" strPostDaten = "" strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Der VBA-Code bereitet die Dateien für den Upload vor, erstellt die richtige Syntax und sendet sie an das PHP-Script:

// Verzeichnis, in dem die Anhänge zwischengespeichert werden: $uploadverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $a = 0; $ausg = ""; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); // Vorbereitung, falls Dateiname im Verzeichnis existiert: $t = explode(".", $datname); // an Dateiendung splitten $endung = $t[count($t)-1]; $datname_pref = substr($datname, 0, strlen($datname) - strlen($endung) - 1); $uploadpfad = $uploadverz . $datname; // Prüfen, ob Datei existiert: $n = 0; while (file_exists($uploadpfad)) { $n++; $uploadpfad = $uploadverz . $datname_pref . "(" . $n . ")." . $endung; } if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $ausg .= $uploadpfad . "\n"; } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $a . " Datei(en) erfolgreich hochgeladen." . "\n\n" . $ausg;

Dazu gibt es verschiedene Möglichkeiten. Manche haben Nachteile, zum Beispiel Probleme mit UTF-8 oder dass gesendete Daten in den Server-Logs erscheinen.

Empfehlenswert ist das MSXML2.XMLHTTP-Objekt, mit dem per POST Formulardaten ganz einfach gesendet und Antworten empfangen werden können. Die Daten werden einfach als key=value-Kombinationen an die Adresse des Server-Scripts (Beispiel: PHP) übergeben. Ein einfaches Beispiel dazu:

Sub Post_mit_Response() Dim strURL As String, strPostDaten As String strURL = "https://example.org/phptest.php" strPostDaten = "" strPostDaten = strPostDaten & "name=Müller" strPostDaten = strPostDaten & "&vorname=Hans" strPostDaten = strPostDaten & "&strasse=Blumenweg 22" strPostDaten = strPostDaten & "&ort=Musterort" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Response je nach Anforderung prüfen MsgBox .ResponseText 'Antwort des Servers ausgeben Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Ein Beispiel für ein zugehöriges PHP-Script:

$adresse = "Die Adresse ist:\n\n"; $adresse .= $_POST['vorname'] . " " . $_POST['name'] . "\n"; $adresse .= $_POST['strasse'] . "\n"; $adresse .= $_POST['plz'] . " " . $_POST['ort'] . "\n"; echo $adresse;

In diesem Beispiel würde PHP aus den einzelnen Angaben eine formatierte Adresse gestalten und diese an VBA senden.

Dadurch können u. a. Daten aus Zellen oder Userformen ganz einfach per Mausklick, Tastendruck oder auch per Event an ein Script auf dem Server gesendet werden, das dann die weitere Verarbeitung erledigt. Somit können Mails versendet oder Eintragungen in Datenbanken (z. B. mySQL) vorgenommen werden usw.

Die Antwort des Servers ist hier natürlich nur reiner Text. Für komplexere Vorgänge sollten Sie sich ansehen, was in der Zusammenarbeit von VBA mit JSON möglich ist.

Download: excel_php_json.xlsm

Datei ▸ Ã?ffnen

Die wohl bekannteste Variante für den �ffnen-Dialog, mit der man die Auswahl komfortabel abfragen kann:

Dim varPfad varPfad = Application.GetOpenFilename("CSV-Dateien (*.csv), *.csv") If varPfad <> False Then Workbooks.Open Filename:=varPfad

Oder so:

With Application.FileDialog(msoFileDialogOpen) .Filters.Clear .Filters.Add "CSV-Dateien", "*.csv" .InitialFileName = "C:\Temp\" 'Ordner vorbelegen If .Show = -1 Then MsgBox .SelectedItems(1) End With

Hier wird eine gewählte Datei geöffnet:

Select Case Application.Dialogs(xlDialogOpen).Show("test.xls") Case -1: MsgBox "Geöffnet" Case 0: MsgBox "Abgebrochen" End Select

Datei ▸ Speichern unter

1. Möglichkeit (In A1 steht der Dateiname.):

Dim varPfad varPfad = Application.GetSaveAsFilename(Range("A1")) If varPfad = False Then Exit Sub ActiveWorkbook.SaveAs varPfad

2. Möglichkeit:

Dim varPfad varPfad = Application.Dialogs(xlDialogSaveAs).Show("test.xls") Select Case varPfad Case -1 'Gesichert Case 0 'Abgebrochen End Select

Eine Ã?bersicht zu integrierten Dialogfeldern finden Sie in Integrierte Dialogfelder aufrufen

Dafür gibt es verschiedene Möglichkeiten - wobei es mit der Find-Methode Probleme gibt, da die nach dem Datum sucht, nicht aber nach einem Teil davon. Gezeigt werden zwei Möglichkeiten - die erste funktioniert ab Excel 365, die zweite generell:

Einsatz der Tabellenfunktion FILTER() ab Excel 365

Das grundlegende Vorgehen ist hier wie bei der älteren Variante (siehe unten), nur beim Sammeln der Daten wird die Tabellenblattfunktion FILTER() verwendet. Die Blätter werden also auch einzeln abgearbeitet, pro Blatt arbeitet aber diese Funktion. Wir gehen hier davon aus, dass der Datenbereich ab Zeile 3 und ab Spalte C beginnt.

Die Funktion sorgt dafür, dass nicht jede Zeile auf dem Blatt durchlaufen werden muss, sondern dass bereits eine Ergebnismenge vorhanden ist - es müssen also weniger Daten ausgewertet werden. Sie liefert einen Array, in dem die Zeilen enthalten sind, in denen sich die gefundenen Geburtstage befinden. Die einzelnen Daten sind Elemente jeder Zeile, der Geburtstag ist das vierte Element (ab Spalte C). Die Datumsangaben liegen in diesem Array als Long-Zahlen vor, deshalb sind sie hier noch formatiert.

Für den praktischen Einsatz kann es notwendig sein, die Blätter noch etwas einzugrenzen, weil sonst Fehler auftreten könnten. Hier wird mit IsArray() geprüft, ob ein Array vorliegt, fürs Grobe reicht das erst mal. Die Routine:

Sub Geburtstage1() Dim lngLZ As LongPtr Dim intN As Integer Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr Dim varT lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngLZ = wksBlatt.Cells(wksBlatt.Rows.Count, 4).End(xlUp).Row varT = Application.Evaluate("=FILTER(" & wksBlatt.Name & "!C3:J" & lngLZ & ",(DAY(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=DAY(TODAY()))*(MONTH(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=MONTH(TODAY())),0)") If IsArray(varT) Then For intN = 1 To UBound(varT) lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = Format(varT(intN, 4), "DD.MM.YYYY") & " " strEintrag = strEintrag & Format(Year(Date) - Year(varT(intN, 4)), "00") & " " strEintrag = strEintrag & varT(intN, 3) & ", " & varT(intN, 2) strEintrag = strEintrag & ", Telefon: " & varT(intN, 8) strEintrag = strEintrag & " (Blatt: " & wksBlatt.Name & ")" arrS(lngArrS) = strEintrag Next End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub

Alte Variante, generell funktionshähig

Hier wird das Verwenden einer Schleife aufgezeigt.

Dazu wird natürlich eine Userform benötigt, hier ist es UserForm1. Auf ihr sind die ListBox1 und ein Commandbutton zum Schlie�en der Userform.

Die folgende Routine, die hier in einem Standardmodul sein muss, durchläuft alle Blätter und prüft in Spalte 6 (F) auf Datumsangaben. Wenn es sich um ein Datum handelt, wird auf �bereinstimmung mit dem heutigen Tag und dem heutigen Monat geprüft. Gibt es die, wird in diesem Beispiel einfach ein String aus den Angaben gebastelt und in einen Array aufgenommen.

Am Ende wird der Array mit UserForm1.ListBox1.List an die Listbox übergeben und die Userform aufgerufen.

Sub Geburtstage() Dim lngEZ As LongPtr, lngLZ As LongPtr, lngZ As LongPtr, lngS As LongPtr Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr lngS = 6 lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngEZ = wksBlatt.UsedRange.Row lngLZ = lngEZ + wksBlatt.UsedRange.Rows.Count If lngLZ > lngEZ Then With wksBlatt For lngZ = lngEZ To lngLZ If IsDate(.Cells(lngZ, lngS)) Then If Day(.Cells(lngZ, lngS)) = Day(Date) And Month(.Cells(lngZ, lngS)) = Month(Date) Then lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = .Cells(lngZ, 6).Text & " " strEintrag = strEintrag & Format(Year(Date) - Year(.Cells(lngZ, 6)), "00") & " " strEintrag = strEintrag & .Cells(lngZ, 5) & ", " & .Cells(lngZ, 4) strEintrag = strEintrag & ", Telefon: " & .Cells(lngZ, 10) strEintrag = strEintrag & " (Blatt: " & .Name & ")" arrS(lngArrS) = strEintrag End If End If Next End With End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub

Es gibt natürlich auch Alternativen. So kann z. B. der Array mehrspaltig verwendet und das Ganze zu einer Funktion umgeschrieben werden, die den Array zurückgibt und beliebig eingesetzt werden kann. Oder die Listbox wird mit ColumnCount mehrspaltig erstellt und die einzelnen Daten werden auf mehrere Spalten verteilt. Oder die Daten werden sofort in die Listbox geschrieben. Oder, oder, oder �

Soll die Geburtstagsprüfung beim Dateiaufruf erfolgen, wird im Klassenmodul DieseArbeitsmappe (doppelt anklicken) dieser Code eingefügt:

Private Sub Workbook_Open() Geburtstage End Sub

Damit der Commandbutton wirkt: Doppelklick auf den Button und diesen Code eintragen:

Private Sub CommandButton1_Click() Unload Me End Sub

Die integrierten Dialogfelder von Excel können auch mit VBA aufgerufen werden. Dies geschieht einfach mit:

Application.Dialogs(Konstante).Show

Bei den Dialogfeldern können verschiedene Argumente mitgegeben werden, die natürlich bei jedem Element anders sind. Hierzu am besten einfach in die Hilfe sehen.

Manchmal muss man sich entscheiden, ob ein integriertes Dialogfeld oder ein herkömmliches Dialogfeld für die Aufgabe besser geeignet ist. Mit z. B. Application.GetOpenFilename kann schön der Pfad abgefragt werden, was mit xlDialogOpen schon nicht mehr so einfach ist.

Download: integrierte_dialogfelder.xlsm

In Zeiten zunehmender Vernetzung spielt natürlich auch das Json-Format eine gro�e Rolle, zunehmend auch für Excel. Das Senden und Empfangen der Daten ist kein Problem, während die Analyse dieser Daten mit VBA vergleichsweise umständlich ist. Einfache Möglichkeiten, wie beispielsweise mit jQuery, geben die Bordmittel von VBA nicht her.

Wesentlich vereinfacht wird dies jedoch mit dem JsonConverter von Tim Hall, den es hier zum Download gibt: VBA-tools/VBA-JSON. Zur Installation müssen (alles für Windows) nur zwei Schritte erfolgen:

• Modul JsonConverter.bas in das VBA-Projekt einfügen
• im Editor unter Extras ▸ Verweise ein Häkchen bei Microsoft Scripting Runtime setzen

Damit steht schon die Funktion JsonConverter.ParseJson() zur Verfügung.

Eine Datenebene

Das Verwenden soll hier zunächst an einem einfachen Beispiel gezeigt werden. Dazu nutzen wir dieses PHP-Script, das einen Adressdatensatz als Json-Objekt zur Verfügung stellt, den wir mit VBA abrufen:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; echo json_encode($a);

Zunächst rufen wir das Ganze vom Server ab:

strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten)

Damit empfangen wir .ResponseText. Zum Vermeiden von Fehlern durch den JsonConverter ist hier schon wichtig, diese Rückgabe zu prüfen. Im einfachsten Fall mit If InStr(1, .ResponseText, "{") > 0 Then.

In .ResponseText steckt nun dieses Objekt:

Das weisen wir mit der Funktion JsonConverter.ParseJson(.ResponseText) einer Variablen zu, die als Dictionary-Objekt deklariert ist:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

In der Variablen sind nun die einzelnen Einträge aus dem ResponseText als Key=Value-Paare enthalten. Da die Variable dicParsed ein Objekt ist, verfügt sie über verschiedene Eigenschaften, mit denen wir arbeiten können. So können wir z. B. einfach eine Schleife über die Keys laufen lassen und haben somit deren Werte:

strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe

Das komplette VBA-Script zum Testen:

Sub Post_JSONAusgabe_1() Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) 'Die Antwort muss geprüft werden, da sonst ein Fehler beim JsonConverter auftritt. 'Hier einfach auf das Vorhandensein der geschweiften Klammer: If InStr(1, .ResponseText, "{") > 0 Then strAusgabe = "" Set dicParsed = JsonConverter.ParseJson(.ResponseText) strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe Else MsgBox "Schiefgegangen: " & .ResponseText, vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Mehrere Datenebenen

Häufig haben wir es jedoch mit Verschachtelungen zu tun, dass also in einem Array-Element ein weiterer Array (bzw. in einem Json-Element ein weiteres Json-Objekt) steckt. im vorigen Beispiel waren alle Elemente auf einer Ebene, aber genau so könnten die Elemente insgesamt ein Wert eines übergeordneten Elements sein:

• values
  • vorname="Max"
  • nachname="Mütze"
  • strasse="Mützenweg 55"
  • plz="01234"
  • ort="Mützenhausen"

Wir hätten hier also den Key â??valuesâ??, dem als Wert ein weiterer Array, bestehend aus Key-Value-Paaren, zugeordnet ist. Ein Beispiel, wie dies mit PHP möglich wäre:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; $b['values'] = $a; echo json_encode($b);

In VBA würde somit dieses Json-Objekt ankommen:

Zum Dekodieren verwenden wir wieder die bekannte Funktion und weisen das Ganze der Dictionary-Variablen zu:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

Wenn wir nun an die relevanten Inhalte wollen, müssen wir beachten, dass die als Array (bzw. genauer als Dictionary) im übergeordneten Schlüssel â??valuesâ?? stecken. Das heiÃ?t, dass wir den ansprechen müssen. Wir durchlaufen also die einzelnen Elemente in dicParsed("values"):

'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert

Hier ist das gesamte Script zum Testen, zum Schluss ergänzt um ein paar Ausgaben zur Verdeutlichung:

Sub Post_JSONAusgabe_2() 'PHP: 'Beachten: Hier gibt es eine Verschachtelung. 'Im Array $a sind die eigentlichen Daten enthalten. '$a['vorname'] = "Max"; '$a['nachname'] = "Mütze"; '$a['strasse'] = "Mützenweg 55"; '$a['plz'] = "01234"; '$a['ort'] = "Mützenhausen"; 'Zuletzt wird $a jedoch dem Array $b zugewiesen, hier dem Key â??valuesâ??: '$b['values'] = $a; 'Im Json ist also diese Verschachtelung auch enthalten und wird so ausgegeben: 'echo json_encode($b); Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Anpassen, auf Fehler prüfen! strAusgabe = "" 'dicParsed ist der komplette(!) verschachtelte Array: Set dicParsed = JsonConverter.ParseJson(.ResponseText) 'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert MsgBox "In PHP in $a enthalten:" & vbNewLine & vbNewLine & strAusgabe 'Nun wird der Array um einen Eintrag erweitert (entspräche in PHP $a['alter'] = 32;): 'dicParsed("values")("alter") = 32 dicParsed("values").Add "alter", 32 strAusgabe = "Das gesamte Json-Objekt ist nun:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed) 'komplettes Json-Objekt strAusgabe = strAusgabe & vbNewLine & vbNewLine strAusgabe = strAusgabe & "während im Key â??valuesâ?? (ehemals $b) nur das enthalten ist:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed("values")) 'Inhalt nur von "values" MsgBox strAusgabe Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Download: excel_php_json.xlsm

Am einfachsten und schnellsten geht es sicher mit dem JsonConverter von Tim Hall. Die Installation geht schnell und ist hier beschrieben: Json verarbeiten. Auf dieser Basis erfolgt auch die Beschreibung an dieser Stelle.

Die Erklärungen erfolgen am Beispiel der abgegbildeten Tabelle mit IDs, Nachnamen und Vornamen. Im ersten Beispiel werden die Spalten A bis C genutzt, im zweiten A bis D.

Für das Verständnis ist ggf. die Gliederung wichtig: Das kleinste Element ist das Paar aus Key und Value. Mehrere dieser (aber zusammengehörigen) Paare werden in einem Dictionary gesammelt. Verschiedene Dictionaries wiederum werden in einer Collection zusammengefasst. Gibt es davon mehrere, werden die in einem Dictionary gebündelt usw. Am Ende habe wir dann die verschiedenen Elemente der obersten Hierarchieebene; in diesen Beispielen sind das die jsonItems, die dann in Json umgewandelt werden.

Hinweis zur Syntax: Ob eine Zuweisung zu einem Element per Element(Key) = Value oder per Element.Add Key, Value erfolgt, ist hier egal. Im ersten Beispiel wird die erste Variante verwendet, im zweiten die zweite.

Eine Ebene

Im ersten Beispiel wollen wir die drei Zeilen als gleichrangige Elemente, die jeweils aus ID, Namen und (erstem) Vornamen bestehen, in eine Json-Struktur bringen. Dazu lassen wir einfach eine Schleife über die drei Zeilen laufen und erzeugen mit jeder Zeile ein Dictionary, hier jsonDictionary, das aus den drei Elementen id, nachname und vorname besteht. Jedes dieser drei Dictionaries weisen wir der Collection jsonItems zu, so dass diese am Ende aus den drei Dictionaries für die einzelnen Zeilen besteht:

For i = 2 To 4 jsonDictionary("id") = Cells(i, 1) jsonDictionary("nachname") = Cells(i, 2) jsonDictionary("vorname") = Cells(i, 3) jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i

Nun führen wir zwei Schritte gleichzeitig durch: Wir konvertieren die Collection in das Json-Format und weisen das Ergebnis gleich der Variablen zu, die wir brauchen, um das Ganze per POST an den Server zu schicken. Diese Variable besteht aus einem Key (hier jsonobjekt) und einem Value, der hier der erzeugte Json-Code ist:

strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems)

So sieht das dann als String aus, der an den Server geht:

Nun können wir das Ganze losschicken:

With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten)

Zur Kontrolle können wir diesen PHP-Code verwenden, der das Json-Objekt in einen Array umwandelt und ausgibt:

$jso = json_decode($_POST['jsonobjekt'], true); $a = print_r($jso, true); echo "Anzahl: " . count($jso) . "\n\n" . $a;

Wenn wir dann in VBA .ResponseText ausgeben lassen, sehen wir das Ergebnis, wie es auf dem Server vorliegt:

Hier der gesamte Code zum Testen:

Sub VBA2JSON() 'PHP: '$jso = json_decode($_POST['jsonobjekt'], true); '$a = print_r($jso, true); 'echo "Anzahl: " . count($jso) . "\n\n" . $a; Dim strURL As String, strPostDaten As String Dim jsonItems As New Collection Dim jsonDictionary As New Dictionary Dim i As LongPtr For i = 2 To 4 jsonDictionary("id") = Cells(i, 1) jsonDictionary("nachname") = Cells(i, 2) jsonDictionary("vorname") = Cells(i, 3) jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems, 3) strURL = "https://example.org/vba2json.php" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then MsgBox .ResponseText Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Verschachtelungen, mehrere Ebenen

Das Vorgehen bei mehreren Levels, also Ebenen, ist genau das gleiche. Die Besonderheit besteht nur darin, dass dem Dictionary beim Key=Value-Paar dem Key statt eines z. B. Strings eine Collection zugewiesen wird, also Key=Collection. Dieser Collection werden vorher die einzelnen Dictionary-Einträge mitgegeben, also die Key=Value-Paare der tieferen Ebene.

Wir verwenden das gleiche Beispiel wie vorher, nur dass die jeweils beiden Vornamen gesammelt in einem Array unter "vorname" eine Ebene tiefer sein sollen:

Dafür müssen wir nur die Zeile ändern, in der dem jsonDictionary bisher der Vorname zugewiesen wurde. Dafür legen wir ein neues Dictionary an, hier dicVorname. Dem weisen wir die beiden Key-Value-Paare zu (Hinweis: zum Verdeutlichen wurde hier die .Add-Schreibweise verwendet). Daraus erstellen wir dann die Collection colVornamen. Fertig ist der Value für die erste Ebene, dort weisen wir den dem Dictionary jsonDictionary zum Key vorname zu.

Der Rest bleibt. Mit PHP können wir .ResponseText wieder ansehen:

$jso = json_decode($_POST['jsonobjekt'], true); $a = print_r($jso, true); echo $a;

Oder etwas komprimierter, indem wir den Array analysieren:

$jso = json_decode($_POST['jsonobjekt'], true); foreach ($jso as $key => $value) { foreach ($value as $key1 => $value1) { if ($key1 == "vorname") { $jso1 = $value1; foreach ($jso1[0] as $key_v => $value_v) { echo $key . " => " . $key_v . ": " . $value_v . "\n"; } } else { echo $key . " => " . $key1 . ": " . $value1 . "\n"; } } echo "\n-----------------------------------\n"; }

Der gesamte Code zum Testen:

Sub VBA2JSON_Level() 'PHP: '$jso = json_decode($_POST['jsonobjekt'], true); '$a = print_r($jso, true); 'echo $a; 'Oder: '$jso = json_decode($_POST['jsonobjekt'], true); 'foreach ($jso as $key => $value) { ' foreach ($value as $key1 => $value1) { ' ' if ($key1 == "vorname") { ' $jso1 = $value1; ' foreach ($jso1[0] as $key_v => $value_v) { ' echo $key . " => " . $key_v . ": " . $value_v . "\n"; ' } ' } else { ' echo $key . " => " . $key1 . ": " . $value1 . "\n"; ' } ' ' } ' echo "\n-----------------------------------\n"; '} Dim strURL As String, strPostDaten As String Dim jsonItems As New Collection, colVornamen As New Collection Dim jsonDictionary As New Dictionary, dicVorname As Dictionary Dim i As LongPtr For i = 2 To 4 jsonDictionary.Add "id", Cells(i, 1) jsonDictionary.Add "nachname", Cells(i, 2) Set dicVorname = New Dictionary dicVorname.Add "vorname1", Cells(i, 3) dicVorname.Add "vorname2", Cells(i, 4) colVornamen.Add dicVorname jsonDictionary.Add "vorname", colVornamen Set colVornamen = Nothing jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems) strURL = "https://example.org/vba2json.php" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then MsgBox .ResponseText Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Download: excel_php_json.xlsm

Die Aufgabenstellung, per Mausklick oder automatisiert Mails zu versenden, ist sehr weit verbreitet. Es gibt auch viele Tipps, wie das mit Outlook erfolgen kann. Jedoch gibt es immer wieder Probleme, sei es durch Rechtebeschränkungen, wegzuklickende Meldungen von Outlook usw.

Am sinnvollsten ist deshalb eine Lösung, mit der man weitestgehend unabhängig von Officeprogrammen ist. Hier besteht die Möglichkeit, VBA nur zum Versand der Maildaten an einen Server zu benötigen, der dann den Rest des Erstellens und Versands der Mail übernimmt.

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

Basis ist der Beitrag Dateiupload. Der VBA-Code dazu wurde hier um die Möglichkeit, Text zu versenden, erweitert. Prinzipiell trifft hier aber das Gleiche zu - vor allem, dass die oben verwendeten Konstanten auch dynamisch verwendet werden können. Hier wird nur das Prinzip aufgezeigt, die Anpassungen müssen dann entsprechend der eigenen Gegebenheiten erfolgen. Dies betrifft insbesondere auch die Sicherheit. Denken Sie daran, die manuell zu ändernden Daten vorher zu prüfen. Diese Scripte sind auch leicht zum Versand von Massenspam zu verwenden.

Dies ist der VBA-Code, der um die Text-Funktion erweitert wurde:

Private Const strURL As String = "https://�Ihre URL�/�Ihre PHP-Datei�.php" Private Const strDatei1 As String = "C:\�\test1.jpg" Private Const strDatei2 As String = "C:\�\test2.jpg" Private Const strDatei3 As String = "C:\�\test3.jpg" Private Const strMailEmpfaenger As String = "info1@example.org;info2@example.org;info3@example.org" Private Const strAbsText As String = "Max Mütze" Private Const strAbsMail As String = "xyz@example.org" Private Const strBoundary As String = "---------------------------7da24f2e50046" Sub DateiUploadUndMail() Dim strPostDaten As String strPostDaten = "" strPostDaten = strPostDaten & Header_Text("empfaenger", strMailEmpfaenger, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("abstext", strAbsText, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("absmail", strAbsMail, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("betreff", "Das ist der Betreff.", "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("mailtext", "Hallo!" & vbNewLine & vbNewLine & "Ich bin der Mailtext.", "text/html", strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Text(strKey As String, strValue As String, strMime As String, strBoundary) Header_Text = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""" & strKey & """;" & vbCrLf & _ "Content-Type: " & strMime & "; charset=UTF-8" & vbCrLf & vbCrLf & strValue & vbCrLf End Function Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Damit werden die Daten an das PHP-Script gesendet, das diese empfängt und daraus die Mails erstellt sowie diese versendet. Auch hier unbedingt daran denken, die Daten noch auf Gültigkeit zu prüfen!

Das komplette PHP-Script:

/* Wichtig: Da das Script prinzipiell auch zu Versand von Massenspam geeignet ist, sollten ganz am Anfang noch Kennwort und Nutzername (oder eine andere Absicherung) abgefragt werden. Diese Angaben können von VBA aus ganz einfach als POST-Paare mitgeschickt werden. */ // Verzeichnis, in dem die Anhänge zwischengespeichert werden: $anhangverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // Mailadressen durch Semikolon getrennt $empfstrings = $_POST['empfaenger']; // Text, der als Absender erscheint: $abstext = $_POST['abstext']; // Mailadresse des Absenders: $absmail = $_POST['absmail']; $betreff = utf8_encode($_POST['betreff']); $mailtext = utf8_encode($_POST['mailtext']); // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $anhangarray = array(); $a = 0; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); $uploadpfad = $anhangverz . $datname; if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $anhangarray[$a]['pfad'] = $uploadpfad; $anhangarray[$a]['dateiname'] = $datname; $anhangarray[$a]['groesse'] = filesize($uploadpfad); $dateiinhalt = fread(fopen($uploadpfad, "r"), filesize($uploadpfad)); $anhangarray[$a]['data'] = chunk_split(base64_encode($dateiinhalt)); } } $empf_array = explode(";",$empfstrings); $encoding = mb_detect_encoding($mailtext, "utf-8, iso-8859-1, cp-1252"); $mime_boundary = "-----=" . md5(uniqid(microtime(), true)); $versendet = 0; // Mail an jeden Empfänger schicken: foreach($empf_array as $einzelmail) { if (trim($einzelmail) != "") { $header ="From:" . $abstext . "<" . $absmail . ">\n"; $header.= "MIME-Version: 1.0\r\n"; $header.= "Content-Type: multipart/mixed;\r\n"; $header.= " boundary=\"" . $mime_boundary . "\"\r\n"; $content = "This is a multi-part message in MIME format.\r\n\r\n"; $content.= "--".$mime_boundary."\r\n"; $content.= "Content-Type: text/plain; charset=\"$encoding\"\r\n"; $content.= "Content-Transfer-Encoding: 8bit\r\n\r\n"; $content.= $mailtext . "\r\n"; if ($a > 0) { // Ggf. Mailanhänge anfügen for ( $n = 1; $n <= $a; $n++) { $content.= "--" . $mime_boundary . "\r\n"; $content.= "Content-Disposition: attachment;\r\n"; $content.= "\tfilename=\"" . $anhangarray[$n]['dateiname'] . "\";\r\n"; $content.= "Content-Length: " . $anhangarray[$n]['groesse'] . ";\r\n"; $content.= "Content-Type: application/pdf; name=\"" . $anhangarray[$n]['dateiname'] . "\"\r\n"; $content.= "Content-Transfer-Encoding: base64\r\n\r\n"; $content.= $anhangarray[$n]['data'] . "\r\n"; } } $content .= "--" . $mime_boundary . "--"; if (mail($einzelmail, $betreff, $content, $header)) { $versendet++; } } } // Ggf. Mailanhänge löschen if ($a > 0) { for ( $n = 1; $n <= $a; $n++) { if ($anhangarray[$n]['pfad'] != "") {unlink($anhangarray[$n]['pfad']);} } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $versendet . " Mail(s) erfolgreich gesendet.";

Sub Range2CSV() Dim varPfad As Variant Dim strPfad As String, strText As String, strTemp As String Dim objZelle As Object Dim lngZeile As Long, lngI As Long, lngDnr As Long Dim intFrage As Integer If Workbooks.Count = 0 Then MsgBox "Keine Mappe offen.", vbOKOnly + vbInformation, "Keine Mappe" Exit Sub End If If ActiveSheet.Type <> -4167 Then MsgBox "Das aktive Blatt ist kein Tabellenblatt.", vbOKOnly + vbInformation, "Keine Tabelle" Exit Sub End If If Selection.Cells.Count = 1 Then MsgBox "Es ist kein Bereich markiert.", vbOKOnly + vbInformation, "Keine Markierung" Exit Sub End If varPfad = Application.GetSaveAsFilename("", "CSV-Dateien (*.csv), *.csv") If varPfad = False Then Exit Sub If Dir(varPfad) <> "" Then intFrage = MsgBox("Die Datei existiert bereits. Soll sie überschrieben werden?", vbYesNo + vbExclamation, "Datei existiert") If intFrage = vbNo Then MsgBox "Datei nicht erzeugt.", vbOKOnly + vbInformation, "Abbruch" Exit Sub End If End If strPfad = varPfad lngI = 0 strText = "" For Each objZelle In Selection lngI = lngI + 1 If lngI = 1 Then lngZeile = objZelle.Row If objZelle.Row = lngZeile Then strTemp = strTemp & objZelle.Text & ";" Else strTemp = Left(strTemp, Len(strTemp) - 1) strText = strText & strTemp & vbNewLine strTemp = objZelle.Text & ";" End If lngZeile = objZelle.Row Next strTemp = Left(strTemp, Len(strTemp) - 1) strText = strText & strTemp & vbNewLine lngDnr = FreeFile Open strPfad For Output As #lngDnr Print #lngDnr, strText Close #lngDnr MsgBox "Datei erzeugt.", vbOKOnly + vbInformation, "Fertig" End Sub

Download: range2csv.xlam

Der folgende Code speichert den gesamten Text des in der Variablen eingetragenen VBA-Moduls in eine Textdatei:

Sub Modulspeichern() Dim varZiel, strKomponente As String, intI As Integer, objX As Object Dim lngDNr As LongPtr strKomponente = "Modul1" varZiel = Application.GetSaveAsFilename("test", "Textdateien (*.txt), *.txt") If varZiel = False Then Exit Sub lngDNr = FreeFile Open varZiel For Output As #lngDNr Set objX = ThisWorkbook.VBProject.VBComponents(strKomponente).CodeModule With objX For intI = 1 To .countofLines Print #lngDNr, .Lines(intI, 1) Next End With Close #lngDNr End Sub

Natürlich muss das nicht in eine Textdatei gespeichert werden; statt Print #lngDNr, .Lines(intI, 1) kann die Ausgabe auch woanders erfolgen oder in einen Array eingetragen werden.

Bitte den Hinweis auf der Startseite beachten, wenn die Meldung Der programmatische Zugriff auf das Visual-Basic-Projekt ist nicht sicher. kommt:

Die OnEvent-Methoden können in jeder beliebigen Prozedur stehen. Normalerweise stehen sie jedoch im Workbook_Open-Ereignis und sollten vor dem Beenden der Mappe mit z. B. Application.OnKey "" wieder entschärft werden. Durch die Zeichenfolge "" werden die OnEvents deaktiviert.

Ereignisse

Application.OnKey "Taste", "Makro"

bewirkt, das beim Drücken einer Taste das Makro aufgerufen wird. Z. B. ruft Application.OnKey "N", "Test", beim Drücken Taste N das Makro Test auf.

Application.OnRepeat "Wiederholung: xxx", "Makro"

bewirkt, das bei dem Kommando Bearbeiten/Wiederholen das Makro ausgeführt wird. Dabei mu� man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"

Application.OnUnDo "Rückgängig: xxx", "Makro"

bewirkt, das bei dem Kommando Bearbeiten/Rückgängig das Makro ausgeführt wird. Dabei mu� man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"

Application.OnTime Zeitangabe, "Makro", Endzeit

bewirkt, das zum Zeitpunkt der Zeitangabe das Makro gestartet wird. Gibt man die Endzeit (dieser Prameter ist optional) mit an, so versucht Excel bis zum Verstreichen dieser Zeit das Makro zu starten. Dies kann z. B. der Fall sein, wenn ein Dialog geöffnet ist, oder ein anderes Makro läuft. Kann Excel das Makro nicht innerhalb dieser Zeit starten, wird darauf verzichtet.

Application.OnTime Zeitangabe, "Makro", , False

setzt man den vierten Parameter auf False, wird diese OnTime-Methode deaktiviert.

Application.OnEntry = "Makro"

wird bei der Eingabe in eine Zelle ausgeführt, nicht aber, wenn ein Makro den Zellwert verändert. Dies kann gegenüber Worksheet_Change ein Vorteil sein.

Hinweis zu Excel 2007: Hier ist es wirkungslos.

Dim Cd As CommandBar Dim Cdb$() Private Sub Workbook_Open() Dim intI As Integer For Each Cd In Application.CommandBars If Cd.Type <> msoBarTypeMenuBar Then If Cd.Visible Then On Error Resume Next intI = intI + 1 ReDim Preserve Cdb(intI) Cdb(intI) = Cd.Name Cd.Visible = False End If End If Next Cd Application.CommandBars("MeineSymbolleiste").Visible = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim intI As Integer On Error Resume Next For intI = 1 To UBound(Cdb) Application.CommandBars(Cdb(intI)).Visible = True Next intI Application.CommandBars("MeineSymbolleiste").Visible = False End Sub

In Spalte A wird solange gesucht, bis eine leere Zelle gefunden wird. Natürlich wäre auch eine For-Schleife mit Application.Cells(Rows.Count, 1).End(xlUp).Row möglich.

Die Daten aus A, B und C werden mit einem Semikolon als Trennzeichen in eine Textdatei eingelesen.

Einlesen in eine Textdatei mit immer gleichem Pfad:

Sub AlsTextSpeichern() Dim intI As Integer, lngDNr As LongPtr lngDNr = FreeFile 'Pfad anpassen Open "C:\Eigene Dateien\aus Tabelle.txt" For Output As #lngDNr intI = 2 'erste Zeile mit Angaben Do While Cells(intI, 1).Value <> "" 'Schleife, solange die Zelle nicht leer ist 'Ã?bernehmen der Daten in die Textdatei Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Einlesen in eine Textdatei mit wählbarem Pfad:

Sub AlsTextSpeichern1() Dim intI As Integer, lngDNr As LongPtr Dim varPfad varPfad = Application.GetSaveAsFilename(InitialFileName:="Test", fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub lngDNr = FreeFile Open varPfad For Output As #lngDNr intI = 2 Do While Cells(intI, 1).Value <> "" Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Textdatei in Exceldatei einlesen, immer gleicher Pfad:

Da das Semikolon als Trennzeichen verwendet wurde, brauchen wir die Textdatei als solche nicht aus- und in Excel einzulesen, sondern wir können die Datei direkt öffnen:

Sub AusTextAufrufen() On Error Resume Next 'falls Datei nicht existiert 'hier nur den Pfad ändern Workbooks.OpenText Filename:="C:\Eigene Dateien\aus Tabelle.txt", DataType:=xlDelimited, semicolon:=True End Sub

Textdatei in Exceldatei einlesen, wählbarer Pfad:

Sub AusTextAufrufen1() Dim varPfad varPfad = Application.GetOpenFilename(fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub Workbooks.OpenText Filename:=varPfad, DataType:=xlDelimited, semicolon:=True End Sub

Gibt es andere Trennzeichen, erfolgt das Aufteilen auf die Zellen natürlich nicht unbedingt. Dann kann entweder mit Split() gearbeitet werden oder es kann mit der integrierten Methode Text in Spalten aufgeteilt werden.

Dynamische Arrayformel mit Matrixfunktion

Möglich ist natürlich auch ab Excel 365, die Textdatei mittels benutzerdefinierter Matrixfunktion auszulesen und die Ergebnisse als Array zu übergeben:

Function DateiEinlesen(strDatei, strTrenner, intSpalten) Dim intS As Integer, lngZ As LongPtr Dim lngDNr As Long, strZeile As String, arrTemp Dim arrS() lngDNr = FreeFile lngZ = 0 Open strDatei For Input As #lngDNr Do While Not EOF(lngDNr) Line Input #lngDNr, strZeile If strZeile <> "" Then arrTemp = Split(strZeile, strTrenner) lngZ = lngZ + 1 ReDim Preserve arrS(1 To intSpalten, 1 To lngZ) For intS = 1 To intSpalten If UBound(arrTemp) >= intS Then arrS(intS, lngZ) = arrTemp(intS) Else arrS(intS, lngZ) = "" End If Next End If Loop Close #lngDNr DateiEinlesen = Application.WorksheetFunction.Transpose(arrS) End Function

In die Zelle kommt dann nur noch die Formel:

=DateiEinlesen(Pfad zur Datei;Trennzeichen;Anzahl der Spalten)

=DateiEinlesen(A1;";";4)

Allerdings sollten die Dateien natürlich nicht zu gro� sein, weil die Berechnung dieser Formel sonst alles verzögern würde.

Eine Lösung per Zahlenformatierung (00":"00) ist nicht empfehlenswert. Die Zahlen sehen zwar wie Uhrzeiten aus, ein Weiterrechnen mit diesen "Zeiten" ist aber nicht möglich. Besser ist eine Makrolösung, die automatisch eine eingegebene Zahl in eine Uhrzeit umwandelt.

Ein Klassenmodul »Klasse1« einfügen, dort diesen Code eingeben:

Public WithEvents Anwendung As Application Private Sub Anwendung_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim bolEvents As Boolean, intI As Integer, varZeit As Variant, arrTemp If Target.Cells.Count > 1 Then Exit Sub If Target = "" Then Exit Sub If Target.HasFormula Then Exit Sub If InStr(1, Target, "+") = 0 Then Exit Sub arrTemp = Split(Target, "+") If UBound(arrTemp) > 2 Then Exit Sub varZeit = "" For intI = 0 To UBound(arrTemp) varZeit = varZeit & arrTemp(intI) & IIf(intI < UBound(arrTemp), ":", "") Next If IsDate(varZeit) Then Target = varZeit Application.EnableEvents = bolEvents End Sub

In »DieseArbeitsmappe« diesen Code einfügen:

Dim Anwendungsobjekt As New Klasse1 Private Sub Workbook_Open() Set Anwendungsobjekt.Anwendung = Application End Sub

Immer dann, wenn diese Mappe offen ist, können Sie Zeiten mit dem Pluszeichen eingeben. Empfehlenswert ist, diese Mappe als Add-In zu speichern. Ein fertiges finden Sie hier: Downloads

Diese drei Varianten rufen die Adresse im Browser auf und bringen das Browserfenster nach vorn:

Sub URL_Aufruf() ActiveWorkbook.FollowHyperlink Address:="https://www.joerglorenz.de", NewWindow:=True, AddHistory:=True Application.WindowState = xlMaximized End Sub

Oder:

Sub URL_Aufruf1() Dim WSHShell As Object Set WSHShell = CreateObject("WScript.Shell") WSHShell.Run "https://joerglorenz.de" End Sub

Oder:

'Deklaration muss am Anfang des Moduls stehen: #If VBA7 Then Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _ (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As LongPtr) As LongPtr #Else Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _ (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long #End If Sub URL_Aufruf2() Dim lngRet As LongPtr lngRet = ShellExecute(0, "Open", "https://joerglorenz.de", "", "", 5) End Sub

Eine Möglichkeit ist, diese direkt in den Editor einzufügen - allerdings per Tastensimulation. Das geht sicher schnell, allerdings ist danach immer die Num-Taste ausgeschaltet:

Sub InTextExportieren() Dim intI As Integer Shell "Notepad.exe", 3 Application.SendKeys "Sicher ist die Num-Taste jetzt ausgeschaltet.~" For intI = 1 To 10 Application.SendKeys Sheets(intI).Range("A1").Value & "~" Next End Sub

Die bessere Alternative ist, die Werte in eine Textdatei zu speichern und anschlieÃ?end diese Textdatei mit Notepad aufzurufen:

Sub InTextExportieren1() Dim intI As Integer, strPfad As String, lngDNr As LongPtr strPfad = ThisWorkbook.Path & "\temp.txt" lngDNr = FreeFile Open strPfad For Output As #lngDNr For intI = 1 To 10 Print #lngDNr, Sheets(intI).Range("A1").Value Next Close #lngDNr Shell "Notepad.exe " & strPfad, 3 End Sub

Bei den Workbook-Ereignissen trifft eingeschränkt das zu, was bei den Application-Ereignissen steht. Der markanteste Unterschied ist, dass Workbook-Ereignisse - wie der Name schon sagt - nur die Elemente der Mappe mit dem Code betreffen und dass wir hier kein Klassenmodul einfügen müssen.

Im Visual-Basic-Editor (Alt & F11) reicht es, im Projektfenster auf Diese Arbeitsmappe doppelzuklicken und dann von Allgemein auf Workbook zu wechseln. Nun stehen im rechten Drop-Down-Feld die Ereignisse zur Verfügung:

Parameterinfo

Bei verschiedenen Prozeduren werden auch Parameter übergeben. Diese verhalten sich wie folgt:

Ereignisse:

Workbook_Activate

Tritt ein, nachdem die Arbeitsmappe aktiviert wurde.

Workbook_AddinInstall

Tritt ein, wenn die Arbeitsmappe als Add-In installiert wurde.

Workbook_AddinUninstall

Tritt ein, wenn die Arbeitsmappe als Add-In deinstalliert wurde.

Workbook_BeforeClose(Cancel As Boolean)

Tritt ein, bevor die Arbeitsmappe geschlossen werden soll.

Workbook_BeforePrint(Cancel As Boolean)

Tritt ein, wenn die Arbeitsmappe ausgedruckt werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Drucken.

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Tritt ein, wenn die Arbeitsmappe gespeichert werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Speichern. SaveAsUI hat dabei den Wert True, wenn das Dialogfeld Save As angezeigt wird.

Workbook_Deactivate

Tritt ein, wenn die Arbeitsmappe deaktiviert wird, zum Beispiel beim Wechsel in eine andere Arbeitsmappe.

Workbook_NewSheet(ByVal Sh As Object)

Tritt ein, wenn ein neues Blatt eingefügt wird.

Workbook_Open

Tritt ein, wenn die Arbeitsmappe geöffnet wurde. Diese Prozedur wird häufig dazu verwendet, um Werte zu initialisieren, welche später in der BeforeClose-Prozedur wieder entfernt werden sollten.

Workbook_SheetActivate(ByVal Sh As Object)

Tritt ein, wenn ein Blatt aktiviert wird, also bei einem Blattwechsel.

Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

Tritt bei einem Doppelklick auf einem Tabellenblatt ein.

Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

Tritt bei einem Klick mit der rechten Maustaste in einem Tabellenblatt ein.

Workbook_SheetCalculate(ByVal Sh As Object)

Tritt ein, wenn Zellen eines Blattes neu berechnet werden. Dieses Ereigniss tritt auch dann ein, wenn sich ein Bezug verändert. Z. B. in B1 steht "= A1" und A1 wird verändert, so wird die Prozedur ausgeführt, da B1 neu berechnet wird.

Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Tritt ein, wenn mindestens eine Zelle in einem Blatt geändert wurde. Leider gibt es immer wieder Probleme bei externen Bezügen, wenn z.B. Daten per DDE geholt werden. Man sollte dann prüfen, ob man nicht mit Calculate zum Ergebnis kommt, indem man einen Bezug zur DDE-Zelle herstellt (=A1).

Workbook_SheetDeactivate(ByVal Sh As Object)

Tritt ein, wenn ein Blattwechsel stattgefunden hat.

Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Tritt ein, wenn sich die Markierung in einem Blatt ändert.

Workbook_WindowActivate(ByVal Wn As Excel.Window)

Tritt bei einem Fensterwechsel ein.

Workbook_WindowDeactivate(ByVal Wn As Excel.Window)

Tritt bei einem Fensterwechsel ein.

Workbook_WindowResize(ByVal Wn As Excel.Window)

Tritt bei einer Veränderung der Fenstergrö�e ein.

In Spalte A stehen ab Zeile 1 die Linkbezeichnungen, also das, was als Text erscheint. In Spalte B stehen die Adressen (URLs).

Formel

="<a href="&ZEICHEN(34)&B1&ZEICHEN(34)&">"&A1&"</a>"

VBA

Die Routine erstellt die Links (<a href), speichert sie in einer Textdatei und ruft diese im Editor auf:

Sub In_Link_konvertieren() Dim lngDNr As LongPtr, lngZ As LongPtr, strLink As String, strPfad As String strPfad = "c:\eigene dateien\temp\test.txt" 'Datei mit HTML-Code lngZ = 1 'erste Zeile mit Angaben lngDNr = FreeFile Open strPfad For Output As #lngDNr Do While Cells(lngZ, 1) <> "" 'Schleife, solange die nächste Zelle nicht leer ist '�bernehmen der Daten in die Textdatei strLink = "<a href=" & Chr(34) & Cells(lngZ, 2) & Chr(34) & ">" & Cells(lngZ, 1) & "</a>" Print #lngDNr, strLink lngZ = lngZ + 1 Loop Close #lngDNr Shell "notepad.exe " & strPfad & "", vbMaximizedFocus End Sub