Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Arbeitsmappen eines Ordners druckenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Drucken/Seite

(Tipp 20) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit VBA alle Arbeitsmappen eines Ordners ausdrucken lassen?

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.

Bereich als HTML-Mail versendenMakro/Sub/Prozedur

Kategorien: Netz ▸ Mail und Übergreifend

(Tipp 565) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen Bereich als HTML-Mail versenden?

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

Blatt in eine andere Mappe kopierenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Mappe ▸ Tabellen

(Tipp 112) Nachricht zum Beitrag an Autor Nach oben

Auf dem Blatt "Huber" steht in A10 der Pfad und der Name der Datei "Ablage.xls". In diese Datei möchte ich das Blatt "Huber" kopieren. Das Blatt soll in der Datei als letztes erscheinen; diese soll anschließend geschlossen werden.

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

Datei per Makro öffnenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 17) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit einem Makro eine Datei aufrufen?

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.

Datei verdeckt öffnenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 16) Nachricht zum Beitrag an Autor Nach oben

Wie öffnet man eine Exeldatei ohne Bildschirmflackern?

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.

Dateien mit Pfadnamen sammelnMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateien und Steuerelemente ▸ ActiveX

(Tipp 23) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einem Listenfeld eine Reihe von Dateien mit Pfadnamen zum späteren Öffnen sammeln?

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



Dateiname aus Pfad (Dir(), Regulärer Ausdruck, Arrayformel)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorien: Dateien und Ordner ▸ Dateien und Stringoperationen ▸ Teile

(Tipp 24) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Pfad (z. B. bei GetOpenFileName) den Dateinamen (bzw. Ordner) filtern?

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

Dateiupload mit Excel und PHPMakro/Sub/Prozedur

Kategorie: Netz ▸ Serverkommunikation

(Tipp 599) Nachricht zum Beitrag an Autor Nach oben

Wie können mit VBA Dateien auf einen Server im Internet geladen werden?

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;

Dialoge: Datei öffnen + Speichern unterMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

(Tipp 37) Nachricht zum Beitrag an Autor Nach oben

Wie ruft man den Öffnen- und den Speichern-unter-Dialog auf?

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

Einzelnes Tabellenblatt speichern (SaveAs)Makro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 26) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich ein einzelnes, zu benennendes Tabellenblatt unter einem zu benennenden Dateinamen speichern?

Eine einfache Möglichkeit:

Sub BlattSpeichern() Dim strTBName As String, strWBName As String strTBName = InputBox("Blattname:") If strTBName = "" Then Exit Sub strWBName = InputBox("Dateiname:") If strWBName = "" Then Exit Sub Worksheets(strTBName).Copy ActiveWorkbook.SaveAs strWBName ActiveWorkbook.Close End Sub

Allerdings ist es natürlich einfacher, wenn man den Blattnamen nicht tippen muss. Die Namen der Tabellen können zur Auswahl auch in eine beliebige Liste eingetragen werden.

Eine andere Möglichkeit wäre, statt Worksheets(strTBName) das aktive Blatt zu verwenden, also ActiveSheet, wenn dies möglich ist.

Für alle Fälle sollte jedoch noch eine Fehlerbehandlung eingebaut werden, falls das Blatt mit dem eingegebenen Namen nicht existiert oder falls eine Datei mit dem eingegebenen Namen im aktiven Ordner (in den hier gespeichert wird) bereits vorhanden ist.

Damit das Speichern flexibler, komfortabler und vor allem sicherer wird, kann auch ein Dialog eingesetzt werden, zum Beispiel Application.GetSaveAsFilename. Hier wäre dann auch gleich der komplette Pfad enthalten.

Integrierte Dialogfelder aufrufenMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

(Tipp 437) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

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.

lfd. Nr.KonstanteName
1xlDialogActivateAktivieren
2xlDialogActiveCellFontSchrift
3xlDialogAddinManagerAdd-In-Manager
4xlDialogAlignmentAusrichtung
5xlDialogApplyStyleFormatvorlage
6xlDialogArrangeAllFenster anordnen
7xlDialogAutoCorrectAutokorrektur
8xlDialogBorderRahmen
9xlDialogCalculationBeschriftungsoptionen
10xlDialogCellProtectionZellschutz
11xlDialogClearInhalte löschen
12xlDialogColorPaletteFarboptionen
13xlDialogColumnWidthSpaltenbreite
14xlDialogConditionalFormattingBedingte Formatierung
15xlDialogConsolidateKonsolidierung
16xlDialogCopyPictureBild kopieren
17xlDialogCreateNamesNamen erstellen
18xlDialogCustomizeToolbarAnpassen
19xlDialogCustomViewsAnsichten
20xlDialogDataSeriesReihe
21xlDialogDefineNameNamen definieren
22xlDialogDefineStyleFormatvorlage
23xlDialogDeleteFormatZahlenformat
24xlDialogDeleteNameNamen definieren
25xlDialogDemoteGruppierung
26xlDialogDisplayBildschirmanzeigeoptionen
27xlDialogEditDeleteZellen löschen
28xlDialogFileDeleteDatei löschen
29xlDialogFileSharingArbeitsmappe freigeben
30xlDialogFilterAdvancedSpezialfilter
31xlDialogFindFileDatei suchen/öffnen
32xlDialogFormatAutoAutoformat
33xlDialogFormatNumberZahlenformat
34xlDialogFormulaFindSuchen
35xlDialogFormulaGotoGehe zu
36xlDialogFormulaReplaceErsetzen
37xlDialogGoalSeekZielwertsuche
38xlDialogImportTextFileTextdatei importieren
39xlDialogInsertZellen einfügen
40xlDialogInsertHyperlinkHyperlink einfügen
41xlDialogInsertNameLabelBeschriftungsbereiche
42xlDialogInsertObjectObjekt einfügen
43xlDialogInsertPictureBild einfügen
44xlDialogNewDatei - Neu
45xlDialogOpenDatei öffnen
46xlDialogOptionsCalculationOptionen: Berechnung
47xlDialogOptionsEditOptionen: Bearbeitung
48xlDialogOptionsGeneralOptionen: Allgemein
49xlDialogOptionsListsAddOptionen: Liste
50xlDialogOptionsTransitionOptionen: Umsteigen
51xlDialogOptionsViewOtionen: Ansicht
52xlDialogPageSetupSeite einrichten
53xlDialogPasteSpecialInhalte einfügen
54xlDialogPatternsFormat: Muster
55xlDialogPrintDrucken
56xlDialogPrinterSetupDruckereinrichtung
57xlDialogPropertiesDateieigenschaften
58xlDialogProtectDocumentBlatt schützen
59xlDialogRoutingSlipMailverteiler
60xlDialogRowHeightZeilenhöhe
61xlDialogRunMakro
62xlDialogSaveAsSpeichern unter
63xlDialogSelectSpecialInhalte auswählen
64xlDialogSendMailMappe als Mail
65xlDialogSetBackgroundPictureHintergrundbild
66xlDialogSetPrintTitlesDrucktitel
67xlDialogSortSortieren
68xlDialogUnhideTabelle einblenden
69xlDialogWorkbookAddBlatt verschieben/kopieren
70xlDialogWorkbookNameBlatt umbenennen
71xlDialogWorkbookNewTabelle usw. einfügen
72xlDialogWorkbookProtectArbeitsmappe schützen
73xlDialogZoomZoom

Download: integrierte_dialogfelder.xlsm

Mails mit VBA und PHP versenden (mehrere Empfänger, mehrere Anhänge)Makro/Sub/ProzedurTipp

Kategorien: Netz ▸ Serverkommunikation und Netz ▸ Mail

(Tipp 600) Nachricht zum Beitrag an Autor Nach oben

Wie können mit VBA Mails mit mehreren Dateianhängen an mehrere Empfänger gesendet werden?

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.";

Markierten Bereich als CSV speichernMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Stringoperationen ▸ Verketten

(Tipp 576) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen markierten Bereich als speichern?

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



Modul als Textdatei speichernMakro/Sub/Prozedur

Kategorien: VBE und Dateien und Ordner ▸ Dateien

(Tipp 31) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Modul als Textdatei speichern?

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:

Speichern ohne MeldungMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Ereignisse ▸ Mappe

(Tipp 120) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich beim Vorhandensein einer Mappe eine neue ohne Fehlermeldung unter dem Namen der alten speichern?

Um eine Datei ohne Fehlermeldung zu überschreiben, kann der Code verwendet werden:

Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="g:\Test\Test.xls" Application.DisplayAlerts = True

Ob das ein guter Stil ist, sei jedem selbst überlassen …

Textdatei erstellen und Text wieder in Excel einlesenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 33) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Tabellenbereich eine Textdatei erstellen und diese Textdatei wieder in Excel einlesen?

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.