Ich habe 1000 Excel Dateien mit Kunden die heißen z. B. 1 Maria Mustermann, 2 Peter Groß, 3 Marina Zufall,…….1000 Herbert Müller. Alle diese Dateien sind gleich aufgebaut mit den Namen der Registerkarten in den Dateien und es stehen auch immer wieder die Daten in den gleichen Zellen z.B. Datei „2 Peter Groß“ Registerkarte „Stammdaten“, Zelle B5 „Berliner Str. 11“, Zelle G8 „13507 Berlin“.
Ich möchte nun mit wenig Aufwand diese Daten in einer Datei z.B. Zusammenfassung nutzen. So das ich aus den Dateien 1 bis 1000 die benötigten Daten in der Datei Zusammenfassung nutzen kann.
Bisher habe ich jede Zelle einzeln eingegeben z.B. [2 Peter Groß]Stammdaten B5.
Wie schaffe ich es, das ich einmalig in der Datei Zusammenfassung, die Dateien 1 Maria Mustermann, 2 Peter Groß, 3 Marina Zufall,…….1000 Herbert Müller hinterlege, und dann
beliebig auf die Zellen in verschiedenen Registerkarten zugreifen kann. So das ich alle Stammdaten in der Zusammenfassung habe.
So das z.B. in A2 die Datei hinterlegt wird „Datei 2 Peter Groß“.
In B2 soll dann immer aus der A2 hinterlegten Datei der Wert „Berliner Str. 11“ aus der Registerkarte „Stammdaten“ Zelle B5 angezeigt werden. In B2 die Datei hinterlegt wird „Datei 2 Peter Groß“.
In C2 soll dann immer aus der A2 hinterlegten Datei der Wert „13507 Berlin“ aus der Registerkarte „Stammdaten“ Zelle G8 angezeigt werden
Ich möchte nur einmal den Pfad angeben und die Datei. Die Registerkarten und Zellen dann nr kopieren.
Wer kann mir helfen? Steven
Hallo Steven,
schau mal, ob du damit klar kommst. Kopiere diesen VBA-Code in eine neue Datei, die du z.B. „Zusammenfassung.xls“ nennst. In einer Datei „Liste.xls“ müssen untereinander alle Dateinamen hinterlegt sein. Liste.xls schließen
Dann werden alle gesuchten Daten in die Datei Zusammenfassung.xls gespeichert.
Im Code muss noch der korrekte Pfad angegeben werden, der auch für die Datei Liste.xls gelten muss.
[PROGRAMMANFANG]
Dim GESAMT, LISTE, QUELLE As Object
Sub Zusammenfassung()
’
’ In einer Datei müssen in Spalte A ab Zeile 1 untereinander
’ alle 1000 Dateinamen hinterlegt sein
’ Diese nennen wir hier mal Liste.xls
’ Liste.xls wird automatisch geöffnet, um die Dateinamen auszulesen
’ Danach wird jede Datei nacheinander geöffnet und die gesuchten Daten ausgelesen
’ Anschließend wird die Datei wieder geschlossen und die Daten in die Zusammenfassung
’ geschrieben.
’
Set GESAMT = ActiveWorkbook ’ Zusammenfassung
pfad = „C:\Daten“ ’ Bitte Pfad anpassen
lwk = Left(pfad, 3)
’ Der Pfad wird aufgesucht
ChDrive (lwk) ’ Laufwerk wechseln
ChDir (pfad) ’ Pfad wechseln
’ Die Datei Liste.xls wird geöffnet
Workbooks.Open („Liste.xls“)
Set LISTE = ActiveWorkbook
’ Die Liste wird nun Zeile für Zeile durchforstet, um die jeweilige Datei zu öffnen
’ und die benötigten Daten auszulesen
For i = 1 To 1000 ’ Zu Testzwecken kann diese 1000 auch auf z.B. 5 reduziert werden
LISTE.Activate
dateiname = Cells(i, 1).Value 'Dateiname auslesen aus Spalte A, Zeile i
Workbooks.Open (dateiname)
Set QUELLE = ActiveWorkbook
straße = QUELLE.Sheets(„Stammdaten“).Range(„B5“).Value
ort = QUELLE.Sheets(„Stammdaten“).Range(„G8“).Value
’
'… dies kann beliebig um weitere Daten erweitert werden
’
QUELLE.Close SaveChanges:=False
’ Die kopierten Daten werden nun in die Zusammenfassung
’ geschrieben
GESAMT.Activate
Cells(i + 1, 1).Value = dateiname 'Spalte A
Cells(i + 1, 2).Value = straße ’ Spalte B
Cells(i + 1, 3).Value = ort ’ Spalte C
’ Nächste Datei
Next i
End Sub
[PROGRAMMENDE]
Der Programmcode ist kommentiert. Allerdings habe ich ihn jetzt nicht getestet, müsste aber so funktionieren.
Viel Erfolg!
Gruß
Natator
Hallo Steven,
puuuuuh, das ist eine schwierige Frage, ich hoffe, ich habe das Problem richtig verstanden: Du willst in einer Excel-Sammelmappe aus 1000 anderen Excel-Mappen Daten per Verweis einfügen? Eine einfache Lösung kenne ich nicht, zumindest müssen mal die 1000 Dateinamen erfasst und in der Sammelmappe hinterlegt werden. Wenn die Felder in der Zeile der Sammelmappe in der gleichen Reihenfolge stehen sollen wie in der Zeile einer der adressierten Dateien kann man für diese Datei relativ einfach vorgehen: den ersten Verweis händisch generieren, dann die Zelle markieren und waagrecht rüber ziehen, dabei wird die Spaltennummer in der Quelldatei automatisch mit erhöht (resp. Zeile beim Ziehen nach unten). Wahrscheinlich kommt man aber nicht darum herum, bei jedem Verweis den vollen Pfad auf die Excel-Datei und den Tabellennamen anzugeben (vielleicht geht das durch einen Verweis auf eine Zelle mit dem Pfadnamen)). Ich würde die Generierung evtl. mit VBA machen, da kenne ich aber Dein Programmierkenntnisse nicht.
Viele Erfolg
Rainer
Hallo Steven,
Das kopieren mit Makrorecorder aufzeichnen.
Den aufgezeichneten code in einer schleife einfügen, den einzufügenden code so anpassen, das immer eine zeile nach unten verschoben wird.
z.b.
Range(„A“&Zeile&":A"&Zeile).value =
das wars
LG Fred
Hallo Steven,
habe ich dich richtig verstanden, dass du „1000“ Exceldateien mit Adressen hast, und diese in einer neuen Exceldatei „Zusammenfassung“ zentralisieren möchtest? oder hast du bereits eine Exceldateil mit allen Anschriften (Datenbankartig)und möchtest diese Anschriften in ander Exceldateien oder Registerblätter einfachst überführen können? (Z.B. Rechnungsausdrucke mit unterschiedlichen Anschriften)
Bitte um nähere Erläuterung.
Falls ich heute nichts mehr von dir höre, wünsche ich und deiner Familie einen guten Rutsch ins neue Jahr.
Karl-Heinz
Hallo Steven,
da kann ich leider nicht weiter helfen. Dafür reichen meine Kenntnisse in VBA nicht aus.
Gruß Hugo
Hallo Steven,
wegen der Vielzahl an erforderlichen Formeln und der damit verbundenen Anzahl an Verknüpfungen zu externen Dateien ist eine Lösung mit Formeln nicht besonders gut praktikabel.
Ich würde an deiner Stelle die Daten aus den Kontaktdateien per Makro in die Zusammenfassung einlesen.
Eine Exceldatei mit entsprechenden Makros kann ich dir zur Verfügung stellen.
Gruß
Franz
probiere mal :
(laufzeit wird lang sein, status unten links)
Sub zusammenfassen()
Dim dateiname As String, zeile As Integer, datei As Workbook, verzeichnis As String
With Application
verzeichnis = .GetOpenFilename(, , „Verzeichnis wählen“)
verzeichnis = Mid(verzeichnis, 1, InStrRev(verzeichnis, „“))
Cells.ClearContents
.ScreenUpdating = False
ChDir verzeichnis
dateiname = Dir("*.xls")
Do Until dateiname = vbNullString
zeile = zeile + 1
Cells(zeile, 1) = dateiname
.StatusBar = zeile
Set datei = GetObject(verzeichnis + dateiname)
With datei
Cells(zeile, 2) = .BuiltinDocumentProperties(12)
Cells(zeile, 3) = .Worksheets(1).Cells(5, 2) 'b5
Cells(zeile, 4) = .Worksheets(1).Cells(8, 7) 'g8
.Close (False)
End With
Set datei = Nothing
dateiname = Dir()
Loop
.StatusBar = „Bereit“
.ScreenUpdating = True
End With
End Sub
bitte info, falls was schiefgeht. viel erfolg!
sorry, ich versteh das Problem, bitte konkretes Beispiel
Hallo Steven,
hier würden benutzerdefinierte Funktionen einige Tipparbeit ersparen.
z.B.:
Function GetStrasse(ByVal filename As String) As String
'Code zum Lesen der Daten aus der Datei in filename
End Function
man ruft dann die Funktion in Excel genauso wie alle anderen integrierten Funktionen auf z.B. in B2 =GetStrasse(A2) würde dann den Straßennamen aus der ‚Datei 2 Peter Groß‘ ausgeben.
Entsprechend soll man dann andere Funktionen für andere Daten programmieren.
LG
W.W.
Hallo,
Du füllst in einer neuen Datei (z. B. mit Namen „Zusammenfassung“) die Zellen der Spalte A mit den Namen Deiner „Klienten“, z. B. Zelle A2 „1 Marina Mustermann“, A3 „2 Peter Groß“, A4 „3 Marina Zufall“, … Wichtig dabei ist der identische Zellinhalt hier mit dem Dateinamen des einzelnen Klienten.
In Zelle B2 jetzt die Formel
=’[(A3).xlsx]Stammdaten’!$B$5
in Zelle C2
=’[(A3).xlsx]Stammdaten’!$G$8
Leider bleibt Dir der Zwischenschritt „Werte aktualisieren“ bei jeder neuen Formel (hier geht ein Dialogfenster auf in dem Du die jeweilige Datei auswählen musst) nicht erspart. Wenn Du allerdings erst einmal für den ersten Datensatz (1 Maria Mustermann) die Formeln eingegeben hast und die Formeln für die „Stammdaten“ (Zelle B2, C2…) mit AutoAusfüllen auf den darunterliegenden Datensatz (2 Peter Groß) ziehst dann fällt dieser Schritt für den gesamten Klienten nur einmal an…
Hallo Steven
Entschuldige dass ich mich jetzt erst melde, ich hatte Probleme mit dem PC.
Zu deiner Frage, da kann Ich dir nicht weiter helfen da ich in Excel nur ein Anfänger bin.
Ich hoffe dir konnte schon jemand anders helfen.
MFG Harald
Hallo Steven,
das ist ja ein sehr komplexes Problem und nicht so ohne weiteres nachzuvollziehen.
Ich denke aber, dass Du da mit Verknüpfungen irgendwann den Überblick verlierst.
Da muss wohl ein Makro ran, dass die Aufgabe übernimmt.
Es irritiert mich ein wenig, dass in den Einzeldateien die Strasse z.B. in Zelle B5 und der Ort in Zelle G8 steht. Ist dem wirklich so?
Und verstehe ich richtig, dass Du die Daten in der Gesamttabelle pro Einzeldatei in einer Zeile haben möchtest? Du siehst also, es ist noch ein wenig Input nötig. Dann helfe ich Dir auch gerne mit einem Makro.
Gruß,
Jochen
Brauchst du da immer noch hilfe?
sorry, das ich erst jetzt antworte,
ist das Problem noch da? wenn ja, dann müsste ich es noch mal etwas genauer oder anders beschrieben haben, evtl . auch mit einer Beispielsdatei.
lg
willi Brand