Werte aus unterschiedlichen Dateien zusammenführen

[StrammerMax]

Hey Leute,

ich habe hier ungefähr 30 Excel-Dateien mit unterschiedlichen Namen.
Jede Datei hat 1 bis X Blätter.

Jede Datei beinhaltet mehrere einheitliche Formulare (1 Formular = 1 Arbeitsblatt). 
Die Bezeichnung der Blätter ist nicht einheitlich und hat kein erkennbares Schema. 
Neben den Blättern mit den Formularen gibt es noch weitere Blätter "Readme, Berechnungen usw..."

Das Formular ist immer einheitlich. Also in jedem Formular steht z.B. an der Stelle C3 die Summe. An der Stelle B4 die Bezeichnung usw. 

Ziel ist es aus den 30 Dateien mit jeweils X Blättern auf denen das Formular ist eine Zusammenfassung zu erstellen. 
Dort soll in einer Liste/Tabelle für jedes Formulararbeitsblatt eine Zeile / ein Datensatz erstellt werden.

So, dass in dieser einen Liste alle Formulare aus den 30 Dateien aggregiert werden. 


Hat jemand eine Idee, wie man das am schlausten automatisiert macht?

Die Daten manuell zusammen zu kopieren würde Tage dauern. 
Zumal sich die Werte im Formular wöchentlich/monatlich ändern können.

[EbyAS]

Hi,
ohne Beispiele fürchte ich, kann Dir keiner helfen. Effizient lässt sich das wahrscheinlich nur mit VBA lösen.

[StrammerMax]

Hallo,

anbei ein Beispiel.


Einmal eine der 30 Quelldaten beispielhaft. Alle rot markierten Blätter sind die, die das einheitliche Formular beinhalten. --> Quelle 1 von 30 
  Quelle 1 von 30.xlsx (Größe: 17,43 KB / Downloads: 16)
Jedes Blatt = ein Datensatz in einer Zusammenfassenden Datei. --> Zusammenfassung: 
  Zusammenfassung.xlsx (Größe: 10,87 KB / Downloads: 12)

Von den Quelldateien gibt es ca. 30 Stück mit unterschiedlich vielen Formularen (Arbeitsblättern).
Die sollen alle wie in meinem Beispiel in die Datei Zusammenfassung aggregiert werden.

[EbyAS]

Hi,
noch ein Frage bzw. Hinweis: wenn die Tabellen freie Namen haben, wie soll die Software erkennen welche Blätter gelesen werden sollen?????
Also irgend ein Merkmal sollte etweder im Tabellennamen oder eine speziell belegte Zelle sollte vorhanden sein.
Erstes ist schneller.
Wie sind übrigens die Sheet-Namen aufgebaut?

[Gast 123]

Hallo

@Armin    amüsant, das war Gedankenübertragung ...

wie soll die Software erkennen welche Blätter gelesen werden sollen???

Amüsant,  mein erster Versuch war das ganze als Formellösung zu programmieren, wo man die Dateien nicht zu öffen braucht!
Das klappt aber nur wenn man in 30 Dateien alle Formulare Namentlich kennt! Das hielt ich für die Praxis zu unpraktisch.
Über genau deine Frage habe ich dann auch solange "geknaberrt", bis mir diese Lösung einfiel.

Wie gefaellt euch meine jetzige Lösung, wo ich allle Dateien öffne, in Zelle B7 und B11 nach "Bezeichnung" und "Anmerkung" suche, und dann aufliste was ich da finde. Ich hoffe damit ist das Problem gelöst??  Das Programm bitte testen, ich weiss nicht ob es 100%ig klappt, konnte es nicht mit echten Daten testen ...

mfg Gast 123

[StrammerMax]

Genau das ist ja die Schwierigkeit. 
Das leere Sheet wird an zig unterschiedliche Empfänger gesendet. Diese benennen die Blätter alle wie wild nach ihrer eigenen Logik um - daher kann man eben nicht über den Blattnamen gehen. 

Ich hatte mir überlegt, dass er sich jedes Blatt anschaut und wenn in Zelle A5 "Wert" steht und in Zelle B3 "Bezeichnung" dann handelt es sich höchstwahrscheinlich um das Formular, welches er dann einlesen soll. 

Wenn ich das richtig verstanden habe ist das auch genau der Ansatz von Gast 123. 
Ich teste das jetzt gleich mal. Vielen Dank dafür :)

---

@Gast 123 das ist einfach genial. Genau das was ich gesucht habe. Du bist mein Held :)

---

Gerade intensiv getestet - es funktioniert wirklich perfekt. 

Eine Kleinigkeit hätte ich allerdings noch. 
Einige der Quelldateien die geöffnet werden haben Verlinkungen auf andere Blätter. Beim Öffnen der Blätter öffnet sich immer der Dialog mit der Abfrage, ob die Quellen aktualisiert werden sollen. 
Könnte man diesen Dialog noch unterdrücken, so dass automatisch immer "nicht aktualisieren" gewählt wird?

Ich habe es mit Application.AskToUpdateLinks = False probiert --> aber dann kommt eine Meldung "Einige Verknüpfungen aus ihrer Datei konnten nicht aktualisiert werden...".
Ich möchte, dass keine Fehlermeldung / kein Dialog mehr geöffnet wird in dem ich manuell eingreifen muss.

Zudem ist mir noch aufgefallen, dass in den meisten Quelldateien ein Beispiel (Blattname "Example") enthalten ist. Dieses Beispiel soll nicht in die Übersicht mit aufgenommen werden.

[Dieter63]

Hallo,  

ist zu spät  -> aber schau dir mal  Google Formulare an

[Gast 123]

Hallo

bitte einfach den neuen Code ins Modul kopieren und testen ob alles klappt? Das Sheet "Example" überspringen funktioniert, habe ich hier getestet.
Ob der Befehl:  DisplayAlert = False den Dialog unterdrückt konnte ich hier leider nicht testen?? Versuch macht klug ...

mfg Gast 123

Code:

Option Explicit      '6.12.2019   Gast 123   für Clever Forum

'Makro zum 30 Quell dateien auflisten

Sub Zusammenfassung_auflisten()
Dim sPfad As String, iRow As Integer
Dim Wb As Workbook, i As Integer, temp
 'Ornder Pfad aus Zelle F1 laden
 sPfad = Worksheets(1).Range("E1").Value
 If Right(sPfad, 1) <> "\" Then sPfad = sPfad & "\"
 temp = Dir$(sPfad & "*.xls*")
 iRow = 5   '1.Zeile in Liste

With Worksheets(1)
 'Alte Tabelle komplett löschen
 .UsedRange.Offset(4, 0).ClearContents
  Application.ScreenUpdating = False

 On Error Resume Next
 Do While temp <> ""
    'Zusammenfassung überspringen!!
    If InStr(temp, "Zusammenfassung") = 0 Then
       'Quell Datei öffnen und auslesen
       Application.DisplayAlerts = False  '** 9.12 neu eingefügt
       Err = Empty: Workbooks.Open sPfad & temp
       If Err = Empty Then
          Set Wb = ActiveWorkbook
          .Cells(iRow, 4) = temp
          'alle Tabllen nauf "Bezeichnung" und Anmerkung prüfen
          For i = 1 To Wb.Worksheets.Count
              '** "Example" übedrspringen!  9.12 neu eingefügt
            If Wb.Worksheets(i).Name <> "Example" Then
            If InStr(Wb.Worksheets(i).Range("A7"), "Bezeichnung") And _
               InStr(Wb.Worksheets(i).Range("A11"), "Anmerkung") Then
               '** Name des Fırmulars auflisten  (oder löschen)
               .Cells(iRow, 5).Value = Wb.Worksheets(i).Name
               'Faten des Formulars auflisten, B3; B7; B11
               .Cells(iRow, 1).Value = Wb.Worksheets(i).Range("B3")
               .Cells(iRow, 2).Value = Wb.Worksheets(i).Range("B7")
               .Cells(iRow, 3).Value = Wb.Worksheets(i).Range("B11")
                iRow = iRow + 1
            End If
            End If
          Next i
          'Aktive Mappe schliessen  (ohne Speichern)
          ActiveWorkbook.Close savechanges:=False
        Else
           MsgBox temp & "  diese Datei konnte nicht geöffnet werden!"
        End If
    End If
    temp = Dir$()
 Loop
 
 Application.DisplayAlerts = True
End With
End Sub

---

Nachtrag:  mir ist gerade ein Öffnen Befehl aus dem alten Excel 7 eingefallen:  Probier es bitte auch mal damit:
        Err = Empty: Workbooks.Open sPfad & temp, UpdateLinks:=False

Wenn das klappt kannst du beide DisplayAllert Befehle im Code löschen!!

[StrammerMax]

Perfekt - hat direkt mit dem Code ohne Anpassungen funktioniert. 
Vielen, vielen Dank nochmal :)

[StrammerMax]

Eine kleine Rückfrage noch.
Welche Parameter muss ich anpassen, um mehr als nur 3 Werte auszulesen?
In der Realität möchte ich 15-20 Werte aus den Quelldateien auslesen. 

Ich habe schon etwas ausprobiert - mir werden aber immer maximal 5 Spalten angezeigt (3 ausgelesene Werte + Name Quelldatei + Name Formularblatt). 

Ich möchte jetzt z.B. 15 Werte + Name Quelldatei + Name Formularblatt.