Mehrere Dateien mit mehrfachen Sheets zusammenzählen

[marcoh]

Hallo Excelfreunde


Ich habe folgendes Problem bei dem ich anstehe. Die Ausgangslage ist wie folgt:

Ich habe ca. 15 Dateien mit jeweils 21 Sheets (alle dieselbe Struktur)

Ich brauche folgendes
- 1 Datei mit 21 Sheets, die jeweils die Sheets aus den 15 Dateien zusammenzählt. Da die ja die gleiche Struktur haben, brauche ich die Summe der jeweiligen Zellen
Sprich: 


file1.xlsx - sheet1
A1 = 3
A2 = 4
B1 = 5
B2 = 6
usw.

file2.xlsx - sheet1 
A1 = 7
A2 = 8
B1 = 9
B2 = 10

...

result.xlsx
A1 = 10
A2 = 12
B1 = 14
B2 = 16



die einzelnen Sheets sind in einem Range von A2:G35, also nicht allzu gross

Kann mir jemand helfen, wie ich das zu einem Resultat der einzelnen Sheets zusammenrechnen kann.
Ich steh auf dem Schlauch. Ich weiss nicht genau wo anfangen. Ob ich das in eine Riesenmatrix laden soll und von da die Sheets befüllen... oder vielleicht gehts einfacher.

[Frogger1986]

Mach doch verknüpfungen und rechne die zusammen..

[Fennek]

Hallo,

aus dem Gedächtnis:

Es gibt im Menü "Daten" "Konsolidieren", das verspricht genau die genannte Aufgabe zu erfüllen.

Ich habe damit aber noch nie gearbeitet, wenn es klappen sollte, berichte bitte.

mfg

[Gast 123]

Hallo

falls der Vorschlag von Fennek nicht klappt kann man den von Frogger nehmen.  Mein Tipp:
Die Verknüpfung für 2-3 Dateien in der 1. Tabelle mit dem Makro Recorder aufzeichnen, und dann manuell auf alle 15 Dateien erweitern.  
Das Makro danach für alle 15 Tabellen laufen lassen;  bei gleicher Struktur aendert sich ja nur der Tabellen Name in der Formel.

mfg  Gast 123

[marcoh]

Der Vorschlag von Fennek klappt wunderbar. 

Muss da evtl. noch ein wenig an der Formatierung arbeiten. Aber die Zahlen sind drin.
Für mich scheint es einfacher so, als mit Verknüpfungen.

Ich habe auch da nun Makros gemacht und erweitere diese, damit die Dateien aus einem Verzeichnis gelesen und direkt konsolidiert werden.
Einzig den Range per Sheet muss ich manuell eingeben, der Rest sollte dynamisch klappen.

Danke für euer Input!

[marcoh]

OK, ich habs folgendermassen gelöst:


Im Consolidate Sub wird einfach durch jedes Sheet geloopt

Von da aus wird die Funktion aufgerufen, welche ein Array zurückgibt mit den Filenamen aller xls Dateien im Angegebenen Pfad.
Diese werden dann im Selection.Consolidate Sources:=CalcRange, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False Konsolidiert

Eigentlich ziemlich Einfach und ziemlich Dynamisch, solange der Range sich nicht gross ändert...

Code:

Sub Consolidate()

Dim wks As Worksheet, strName As String
Dim CalcRange As Variant
dim strPath as String

strPath = "C:\PATH\TO\XLSFILES\"
For Each wks In Worksheets
    strName = wks.Name
   
    Sheets(strName).Select
       Cells.Select
       
       Selection.ClearContents
       Range("A1").Select
       
       CalcRange = Consofiles(strPath, strName & "'!R3C1:R40C7")
       Selection.Consolidate Sources:=CalcRange, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
       
       Cells.Select
       Cells.EntireColumn.AutoFit
   
Next

End Sub


'====================================================
Public Function Consofiles(strPath As String, CellRange As String) As Variant
Dim strConso As Variant
Dim StrFile As String

Dim i As Integer

ReDim strConso(1 To 1)

   StrFile = Dir(strPath & "*xls*", vbDirectory)
   Do While Len(StrFile) > 0
       i = i + 1
       ReDim Preserve strConso(1 To i)
       
             strConso(i) = "'" & strPath & "[" & StrFile & "]" & CellRange
       StrFile = Dir
     
   Loop
   
   
Consofiles = strConso

End Function

[snb]

Code:

Sub M_snb()
  c00 = "G:\OF\"
  01 = Dir(c00 & "Consolidate*")
  c02 = "]Sheet1!R1C1:R20C8"
     
  Do Until c01 = ""
    c03 = c03 & vbLf & c00 & "[" & c01 & c02
    c01 = Dir
  Loop
  
  for j to sheets.count   
    Sheets(j).UsedRange.ClearContents
    Sheets(j).Cells(1).Consolidate Split(Mid(replace(c03,"Sheet1","Sheet" & j), 2), vbLf), -4157
  next
End Sub