Datein aus Ordner auslesen aber vorher für jede Datei ein Makro ausführen

[llotS]

Hallo,

euer Forum ist super und diente mir in der Vergangenheit schon immer mal wieder als große Hilfe bei Office Problemen. Da ich nun selber ein Problem habe, welches sich so im Forum oder über Google nicht gelöst bekomme dachte ich, ich Frage euch einfach mal :)


Ich habe etwa 100 *.xlsx Dateien, welche 10 Tabellenblätter beinhalten. Die Formatierung der Tabellenblätter ist immer gleich. Ich möchte diese Tabellenblätter nun gesammelt in eine neue Datei schreiben damit ich diese später per Pivot auswerten kann. Dies ist aber nur möglich wenn ich die Daten vorher "Pivot-tauglich" mache, sprich die aktuelle Daten in eine Zeile bekomme. Was ich soweit machen möchte ist eigentlich klar, nur an dem wie hapert es ein wenig :S


erdachter Prozedur-Ablauf:

1. Auswertungs-Datei öffnen und "GrabData" Makro ausführen
   
2. Makro soll einen Bestimmten Order auslesen
   
3. Jede Lese-Datei nacheinander ansteuern
   
4. Nach dem öffnen einer Lese-Datei ein neues Tabellenblatt "SUM" hinzufügen
   
5. automatisch ein Makro ausführen welches auf dem neuen Tabellenblatt "SUM", Formeln einbindet welches die 10 Tabellenblätter auf eine Zeile reduziert und somit 10 Zeilen erzeugt
   
6. Zeile 2 aus "SUM" der Lese-Datei nun in die letzte Zeile von Tab1 der Auswertungs-Datei einfügen
   
7. Zeile 3 aus "SUM" der Lese-Datei nun in die letzte Zeile von Tab2 der Auswertungs-Datei einfügen
   
8. Zeile 4 aus "SUM" der Lese-Datei nun in die letzte Zeile von Tab3 der Auswertungs-Datei einfügen
   
9. ...
   
10. schließen der Lese-Datei ohne zu speichern
    

Ist dies so überhaupt realisierbar? Ich habe einmal ein Beispiel der "Auswertungs-Datei" und "Lese-Datei" angehangen.

Vielen Dank für eure Hilfe!

[shift-del]

Moin

Ist das was auf "Auswertung - Daten1" steht nun ein Zwischenergebnis oder ein Endergebnis?

[llotS]

Moin

Ist das was auf "Auswertung - Daten1" steht nun ein Zwischenergebnis oder ein Endergebnis?

Hallo shift-del,

das wäre in der Tat schon das "Endergebnis" welches ich dann in eine Pivot schieben wollen würde

[shift-del]

Nein, das ist nur ein Zwischenergebnis. Die Pivot wäre das Endergebnis.

Ich warte dann mal auf das Endergebnis.

[llotS]

Sorry,

da hast du natürlich recht. Im Anhang einmal eine Beispiel Pivot (Pivot_Daten1), auf eine "Produkt-Art" über alle Umsatzjahre - Dies wäre dann das Endergebnis.

[snb]

Code:

Sub M_snb()
  ReDim sp(10 * 15 * 5, 2)
 
  For Each it In Sheets
      If it.Name <> "SUM" Then
        sn = it.UsedRange
        For j = 1 To UBound(sn)
          If sn(j, 1) = "" Then
              y = j
          Else
            For jj = 2 To UBound(sn, 2)
              sp(n, 0) = Replace(sn(j, 1), "Umsatz ", "")
              sp(n, 1) = sn(y, jj)
              sp(n, 2) = sn(j, jj)
              n = n + 1
            Next
          End If
        Next
    End If
  Next
 
  Sheets("sum").Cells(1).Resize(n, 3) = sp
  Sheets("sum").Copy
  With ActiveWorkbook
       .SaveAs Application.DefaultFilePath & "Beispiel_002.csv", 23
       .Close 0
   End With
End Sub

[steve1da]

https://www.clever-excel-forum.de/misc.php?action=help&hid=10
Bitte nachholen.

[llotS]

Hallo steve1da,

vielen Dank für den Hinweis, mir war der Umgang mit Cross-Posting tatsächlich so nicht bewusst, das war keine Absicht >.<
Auch gepostet hier: https://www.ms-office-forum.net/forum/sh...ost2082032

[llotS]

Hallo,

@snb - danke für deinen Code, jedoch verstehe ich diesen leider nicht ganz - wo würdest du hier definieren welche XLSX Datein überhaupt ausgelesen werden sollen (Verzeichnis)?

Aktuell habe ich folgendes zusammengeschrieben. Dieses Makro würde aber vorraussetzten, dass die Tabelle "SUM" bereits in allen "Lese-Dateien" mit Inhalt vorhanden ist, was sie aber leider nicht ist.

Code:

Sub GrabData()
    On Error Resume Next
    Const PFAD = "C:\files" 'Pfad der auszulesenden Dateien
    Dim ws As Worksheet, rngZiel As Range, f As String
    Set ws = Sheets("Auswertung - Daten1") 'Tabellenname in die ich reinschreiben möchte
    ws.Range("2:1048576").Clear 'Tabelle vorher leeren
    Set rngZiel = ws.Range("A2") 'ab wo ich die Dateien einfügen möchte
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    f = Dir(PFAD & "\*.xlsx")
    While f <> ""
        With GetObject(PFAD & "\" & f).Sheets("SUM") 'aus welchem Tabellenblatt ich auslesen möchte
            .Range("B2:XFD2").Copy 'welche Zeile ich rauskopieren möchte
            rngZiel.PasteSpecial xlPasteValuesAndNumberFormats
            .Parent.Close False
        End With
        Set rngZiel = rngZiel.Offset(1, 0)
        f = Dir
    Wend
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "=NOW()" 'Amateurhaftes einfügen eines Datums
    Range("A1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Alle Datein wurden importiert!"
End Sub

Unabhängig davon steuert dieses Makro natürlich jetzt auch nur ein Tabellenblatt an "Auswertung - Daten1", ich könnte dieses jetzt 10x untereinanderkopieren und entsprechende Variablen ändern, aber dann würde er natürlich auch alle Lese-Dateien 10x öffnen ^^;

Aber erst einmal wäre ich euch dankbar, wenn ihr mir evtl. ein Tipp geben könntet, wie ich innerhalb dieser Prozedur beim Öffnen einer "Lese-Datei" ein neues Tabellenblatt erzeuge, welches ich automatisch mit formeln beschreibe.

Vielen Dank!

[schauan]

Hallöchen,

also, mal abgesehen davon, dass man einiges auch mit Powerquery erledigen könnte mal was zu Deiner angedachten Vorgehensweise.

1) Wenn in den Lesedateien ein Blatt fehlt, dann musst Du diese Dateien öffnen um dieses Blatt hinzuzufügen. Du kannst das mit dem Makrorekorder aufzeichnen und den aufgezeichneten Code dann in eine Schleife einbauen. Eventuell stellst DU den Code auch zuerst mal hier ein, da gibt es bei einer Aufzeichnung das eine oder andere zu verbessern / verkürzen
2) Wenn Du Dateien aus mehreren Verzeichnissen in Deiner Schleife verarbeiten willst, siehe mal hier der Ansatz: Thread-Speichern-als-per-UserForm-Letztes-Blatt-nicht-drucken