Viele Dateien zusammenfassen

[Igelbauer]

Hallo an alle Excelhelfer, jetzt rufe ich mal laut um Hilfe.
Habe von meinem Chef eine Mammutaufgabe gestellt bekommen, die ich vielleicht mit viel Tüftelei auch irgendwann schaffen würde.
Aber leider drängt die Zeit ein wenig und deswegen hoffe ich, dass mir bei dem einen oder anderen Arbeitsschritt ein wenig unter die Arme gegriffen wird.
Ich versuche mal das Projekt zu beschreiben.
Ausgangssituation:
Wir haben eine Exceldatei in der in unregelmässigen Abständen Änderungen vorgenommen werden.
Beim Schliessen erzeugt die Datei eine Kopie mit Datum und Uhrzeit an den Namen angehängt, so dass man im Prinzip alle Änderungen mit Zeitpunkt der Änderungen nachvollziehen kann. Theoretisch.
Leider hatte keiner einkalkuliert wie oft die Datei geöffnet und geschlossen wird (Im Schnitt 5-6 mal pro Tag).
Der Ordner mit den ganzen Kopien füllt sich also immer mehr und wenn mein Chef mich jetzt fragt wann denn beispielsweise Zelle xy geändert wurde stehe ich vor einer gewaltigen Suchaufgabe. Und es werden jeden Tag mehr.
Es betrifft zwar nur immer ein Tabellenblatt, aber in diesem Blatt ca. 10000 Zellen.
Meine Idee (vielleicht denke ich auch zu kompliziert)
Eine Datei mit Namen Aktuell_und_Änderungen o.ä., mit einem Tabellenblatt mit der Grundstruktur des Originals, nur noch ohne Einträge. Die Struktur ändert sich nie.
Erste Sicherungskopie auf, das gefragte Tabellenblatt herauskopieren und umbenennen in "Datum_Uhrzeit" (diese Angabe befindet sich ja als Anhang am Dateinamen).
Die zwei jetzt vorhandenen Tabellenblätter Zelle für Zelle vergleichen und wenn eine Änderung vorliegt den Wert übernehmen und Datum_Uhrzeit als Kommentar. Danach das Tabellenblatt und die entsprechende Datei löschen.
Nächste Datei. Bis ich den Ordner einmal geleert habe.
Übrig bliebe mir nur noch eine Datei, mit einem Blatt, auf aktuellem Stand und Kommentaren wann geändert wurde.
Wenn jemand eine bessere Idee hat - her damit
Gruss Igel :17:

[Igelbauer]

Noch eine kleine Anmerkung.
Ich kann nur vom Arbeitsplatz aus hier ins Forum.
In einer halben Stunde hab ich Feierabend.
Werde morgen mal reinschauen und übers Wochenende ist dann grosses Brainstorming.

[Phi.VBA]

Für den ersten Versuch:

Annahmen:

- alle älteren Dokumente sind *.xlsx, die neueste xlsm.


- es wird nur die letzte Änderung dokumentiert (kann leicht geändert werden)

Code:

Sub Igel()
Dim iPath As String
Dim WB As Workbook
Dim lc As Range
iPath = ThisWorkbook.Path & "\"

With ActiveSheet
   Set lc = .UsedRange.SpecialCells(11)
   fi = Filter(Split(CreateObject("wscript.shell").exec("cmd /c dir " & iPath & "*.xlsx /b/s/o-d").stdout.readall, vbCrLf), ".")
   For f = 0 To UBound(fi)
         Set WB = Workbooks.Open(fi(f))
         If .UsedRange.Address = WB.Sheets(1).UsedRange.Address Then
               For i = 1 To lc.Row
                   For j = 1 To lc.Column
                       If .Cells(i, j) <> WB.Sheets(1).Cells(i, j) Then
                           If .Cells(i, j).Comment Is Nothing Then
                               .Cells(i, j).AddComment fi(f) & ": " & WB.Sheets(1).Cells(i, j)
                           End If
                       End If
                   Next j
               Next i
         End If
         WB.Close 0
   Next f
End With
End Sub

[Igelbauer]

Hi Phi (oder wie ich dich anreden soll)
Danke erstmal für die Mühe.
Hab mir den Code gerade durchgelesen.
Offen gestanden, alles versteh ich nicht (Bin vom Profi noch weit entfernt), aber was ich versteh klingt gut.
Vor allem die ersten Zeilen sind mir etwas kryptisch. Aber da versuch ich mich durchzuwühlen.
Ein paar Anpassungen und das Ding müsste laufen.
Ich zieh mir das jetzt mal auf nen Stick und nehm es mit ins Wochenende.
Am Montag meld ich mich dann mit einer virtuellen Umarmung, oder noch irgendwelchen Fragen.
Oder beides.

Schönes Wochenende
Igel

[Igelbauer]

Wenn ich meinen Code mit dem vom Phi vergleiche
kommt mir das vor wie Bedienungsanleitung gegen Zauberspruch.

Ich bin hin und weg

Ein paar kleine Fragen hätte ich noch muss jetzt aber erstmal arbeiten.
bis später
Igel

[Der Steuerfuzzi]

Hallo,

eine Möglichkeit wäre noch, das ganze über Power Query abzubilden. Mit einer Spalte, die den Dateinamen beinhaltet könnte man dann über den Filter herausfinden, welche Zelle in welcher Datei geloggt wurde.

[Igelbauer]

Code:

Option Explicit

Sub Igel()
Dim iPath As String
Dim WB As Workbook
Dim lc As Range
Dim fi As Variant
Dim f As Integer
Dim i As Integer
Dim j As Integer

iPath = ThisWorkbook.Path & "\"

With ActiveSheet
   Set lc = .UsedRange.SpecialCells(11)
   fi = Filter(Split(CreateObject("wscript.shell").exec("cmd /c dir " & iPath & "*.xlsm /b/s/o-d").stdout.readall, vbCrLf), ".")
   For f = UBound(fi) To 1 Step -1
         Set WB = Workbooks.Open(fi(f))
'Scheiss UFo
         If .UsedRange.Address = WB.Sheets(2).UsedRange.Address Then
               For i = 1 To lc.Row
                   For j = 1 To lc.Column
                       If .Cells(i, j) <> WB.Sheets(2).Cells(i, j) Then
                 .Cells(i, j).ClearComments
                             .Cells(i, j).Value = WB.Sheets(2).Cells(i, j).Text
                           If .Cells(i, j).Comment Is Nothing Then
                               .Cells(i, j).AddComment Left(Right(fi(f), 21), 17) & ": " & WB.Sheets(2).Cells(i, j)
'                 Else: .Cells(i, j).Comment.Text = .Cells(i, j).Comment.Text & Left(Right(fi(f), 21), 17) & ": " & WB.Sheets(2).Cells(i, j)
                           End If
                       End If
                   Next j
               Next i
         End If
         WB.Close 0
'       WB.Kill
   Next f
End With
End Sub

So, jetzt wird's spannend
Hatte den Code wie oben eingefügt abgeändert und zuhause mit ein paar Beispieldateien getestet.
Bis auf ein paar Kleinigkeiten hat er prima funktioniert.
Jetzt hab ich es auf der Arbeit mit dem Original versucht - und - nix da !
Der Befehl: fi = Filter(Split(CreateObject... usw funktioniert hier nicht.
Im  Überwachungsfenster bleibt fi leer und dementsprechend läuft auch sonst nicht viel.
Ausgerechnet die Zeile, die ich am wenigsten verstehe.
Wer weiss Rat

Übrigens PowerQuery gibt es meines Wissens erst ab 2016.
Wir haben auf der Arbeit nur 2013.

Grrruss
Igel

[Der Steuerfuzzi]

Übrigends PowerQuery gibt es meines Wissens erst ab 2016.
Wir haben auf der Arbeit nur 2013.

Für 2013 kann man es als Add-In nachinstallieren (kostenloser Download bei MS).

[Igelbauer]

Wäre vielleicht für die Zukunft ne interessante Sache.
Aber müsste ich mich auch erstmal mit beschäftigen.
Eigentlich fand ich den Code von Phi genial, hat zuhause auch funktioniert.
Jetzt grübel ich, was auf meinem Firmenrechner anders ist, weil hier funktioniert er überhaupt nicht.

Achja Tell me why i don´t like Mondays

[Wastl]

hi,

ich hab hier auf Arbeit Excel 2010.
Ich habe noch die Pivotfunktion enthalten
mein Code läuft fehlerlose in einem Modul über die von dir genannte Stelle hinweg.
Habe dann abgebrochen, weil mein Tabellenblatt völlig leer ist.
Hilft dir das weiter?