Office: (Office 365) VBA - Dateinamen anhand von Unterstrich und Großbuchstaben trennen

Hallo zusammen,

ich würde gern folgendes Projekt realisieren.

Ich bekomme regelmäßige pdf- und jpg-Dateien (immer im Format Nachname_Vornamen_Geburtsdatum - z. B.: "vonMustermann-Müller_Hans-PeterKlaus_25-10-1960") zugeschickt, die ich gern weiterverarbeiten möchte.

Diese jpg- bzw. pdf-Dateien liegen jeweils in einem Ordner, mit dem selben Namen (ohne ".jpg" oder ".pdf").

In erster Linie möchte ich den Nachnamen, den Vornamen und das Geburtsdatum in jeweils eine Zelle einer Zeile haben. Bei jeder Übermittlung dieser Daten erhalte ich so grob 10 bis 15 Ordner mit jeweils den genannten zwei Dateien.

Da ich diese Informationen aus dem Dateinamen weiter verarbeiten möchte (um mir das Leben einfacher zu machen), benötige ich mal eure Unterstützung.

Ziel ist es, dass über ein Button ein Abfragefenster geöffnet wird, in dem ich den Hauptordner auswählen kann, in dem dann die Ordner mit den jeweiligen pdf- bzw. jpg-Dateien sind. Anschließend sollen diese Daten aus dem Dateinamen der pdf-Datei jeweils in eine Zeile einer Tabelle (B2 bis F2) eingetragen werden, damit ich von hier aus mittels Excel-Formeln weiter arbeiten kann.



Dabei sollen die „Nachnamen“ in Spalte B (ab Zeile 2), die „Vornamen“ in Spalte C und das „Geburtsdatum“ in Spalte D eingetragen werden. In Spalte A soll eine Nummer (fortlaufend ab 1) eingetragen werden.



Ist es möglich, in VBA auch nach Großbuchstaben zu suchen? Ich würde gern realisieren, dass "vonMustermann-Müller_Hans-PeterKlaus_25-10-1960" wie folgt aufgeteilt wird:

· Nachname = "von Mustermann-Müller";

· Vornamen = "Hans-Peter Klaus";

· Geburtsdatum = "25.10.1960"

Das soll bei den Namen unabhängig der Anzahl der Namen erfolgen. Die Trennung der Spalten kann über "_" bzw. der Namen durch die Trennung bei den Großbuchstaben erfolgen (wenn das überhaupt möglich ist). Bindestriche sollen dabei ignoriert werden, denn z. B. "Hans-Peter" ist ja ein Name.

In Spalte E soll dann noch das passende Bild in der "Zelle platziert" werden. Die Namensgebung des Bilder ist, abgesehen von der Dateiendung, identisch zu denen der pdf-Datei.

Eine xlsm-Datei kann ich leider nicht hochladen, daher der Code im Spoiler.
Den folgenden Code habe ich (dank eines Youtube-Spenders) schon:

Spoiler: Code

Code:

Option Explicit

'Verweis auf die Bibliothek "Microsoft Scripting Runtime"

Sub AlleDateien_Auslesen()

'Variablen dimensionieren
Dim Ordner As Variant
Dim Pfad As String

'Benutzer Ordner auswählen lassen
Set Ordner = Application.FileDialog(msoFileDialogFolderPicker)

'Prüfen ob Benutzer einen Ordner ausgewählt hat
If Ordner.Show = False Then Exit Sub

'Pfad definieren
Pfad = Ordner.SelectedItems(1)

'Überschriften eintragen
Range("A1:F1").Value = Array("Nr.", "Nachnamen", "Vornamen", "Geburtsdatum", "Passfoto", "Hyperlink")

'Dateien des Ordners auslesen
Call Dateien_Auslesen(Pfad)

'Dateien der Unterordner auslesen
Call Unterordner_Auslesen(Pfad)

'Spaltebreite automatisch anpassen
Columns("A:F").AutoFit

End Sub

Sub Unterordner_Auslesen(Pfad As String)

'Variablen deimensionieren
Dim fso As New FileSystemObject
Dim Unterordner As Folder


'Schleife über alle Unterordner im Hauptordner
For Each Unterordner In fso.GetFolder(Pfad).SubFolders

    'Dateien des Unterordners auslesen
    Call Dateien_Auslesen(Unterordner.Path)

Next Unterordner

End Sub

Sub Dateien_Auslesen(Pfad As String)

'Variablen dimensionieren
Dim fso As New FileSystemObject
Dim Datei As File
Dim LetzteZeile As Long

'Schleife über alle Dateien im Ordner
For Each Datei In fso.GetFolder(Pfad).Files

    'Letzte Zeile herausfinden
    LetzteZeile = Cells(Rows.Count, 1).End(xlUp).Row + 1
   
    'Ergebnisse ins Tabellenblatt eintragen
    Cells(LetzteZeile, 1).Value = Datei.Name
    ActiveSheet.Hyperlinks.Add anchor:=Cells(LetzteZeile, 6), Address:=Datei.Path, TextToDisplay:=Datei.Path
   
Next Datei

End Sub

Schöne Grüße und vielen Dank im Voraus

Daniel

 

Moin,

wozu VBA?
Alle Dateien eines Ordners einzulesen und die Dateinamen nach Deinen Wünschen aufzuteilen ginge wesentlich einfacher mit ein paar Mausklicks in Power Query. Setzt voraus, dass Du Off. 2016 oder höher (oder mindestens 2010) hast. Hast Du?

Menü Daten --> Daten abrufen --> Aus Datei --> Aus Ordner

Ordner auswählen --> OK

Dann Daten transformieren lassen.

Im PQ Editor: Dateien ggf, nach Typen filtern lassen, die nicht benötigten Spalten entfernen, Rechtsklick auf den Spaltenkopf, Spalte teilen auswählen, Trennzeichen festlegen, Überschriften anpassen... das wars (im Grunde) schon

 

Moin Ralf,

vielen Dank für die Antwort. Diese Möglichkeit kannte ich noch gar nicht.
ich habe hierzu noch einige Fragen, die du mir vielleicht auch beantworten kannst.
1. Kann man das automatisieren, damit ich nicht immer wieder die selben Schritte durchklicken muss? Das wäre mit VBA dann ja gegeben.
2. Gibt es im PQE auch die Möglichkeit z. B. die zusammengeschriebenen Vornamen innerhalb einer Zelle zu trennen? Zumindest die Trennung in eine zweite Spalte durch "Rechtsklick auf den Spaltenkopf" - "Spaltenkopf" - "Nach Wechsel von Kleinbuchstabe zu Großbuchstabe" habe ich gefunden. Ansonsten muss ich das nachträglich machen.

Sonnige Grüße

Daniel

 

Zu 1.
Alle diese Schritte werden in einer PQ Abfrage gespeichert. Die kannst Du so oft Du willst, aufrufen. Ist quasi wie VBA, nur halt nicht in der Sprache Basic sondern in M geschrieben.
Standardmäßig wird sie (die Abfrage) beim Öffnen der Datei aufgerufen. Ansonsten musst Du in der Ergebnistabelle über Rechtsklick darin, das Submenü Aktualisieren aufrufen.
Zu 2.
Du meinst sowas wie Hans-Peter Klaus? In diesem Fall hast Du ja unterschiedliche Trennzeichen. Entsprechend oft musst Du die Spalten trennen. Wäre in VBA auch nicht anders. Verschiedene Bedingungen benötigen angepasste Reaktionen...
Du kannst, wenn Du Dir mal die Optionen anschaust, auch festlegen, ob das Trennzeichen nur links, rechts oder immer beachtet werden soll und anderes mehr. Spiel das mal durch, dann entdeckst Du auch die Möglichkeiten.
Aber auch diese Schritte musst Du nur einmalig festlegen.
Im Prinzip lässt sich mit PQ vieles durch Mausklick erledigen. Beinahe alles dann, wenn Du Dich mit der Abfragesprache M befasst.