Importieren von mehreren Zellen aus vielen Quelldateien in eine Übersichtstabelle in Excel 2003
Hallo liebe Leute!
Wir arbeiten bei uns in der Firma hauptsächlich mit Excel und für jeden Vorgang (Klient) wird eine Excel-Vorlage ausgefüllt. Entsprechend gibt es nach Klienten bezeichnete Excel-Dateien in nach Klienten bezeichneten Ordnern. Derzeit verknüpfen wir in einer Übersichtstabelle (extra Datei) mühsam die einzelnen relevanten Felder.
Folgende Idee soll das in Zukunft vereinfachen:
In Spalte A soll als einziger Arbeitsschritt nur noch der Pfad (immer ein anderer Unterordner) der jeweiligen Datei als Quelldatei angegeben werden (am besten so, dass mit "Durchsuchen" die Datei ausgewählt wird, damit keine Fehler entstehen durch eine direkte Pfadeingabe). Alle weiteren Spalten sollen dann mit den relevanten Feldern dieser Quelldatei verknüpft sein und übernehmen deren Inhalte.
Beispiel Übersichtstabelle:
Ich möchte nun nur die Quelldatei in Spalte A angeben und Spalte B - D usw. werden automatisch mit den Zellen in dieser Quelldatei verknüpft. In meiner Vorstellung sieht das so aus: Ich brauche in jeder Spalte eine Formel, die das Verknüpfen einer bestimmten Zelle in der in Spalte A benannten Quelldatei vornimmt.
Nun müsste ich wissen, wie ich es realisiere, dass ich in Spalte A diese Quelldatei definiere und mich in allen weiteren Spalten darauf beziehe. Wie könnte so eine Verknüpfung funktionieren? Wie sähe eine solche Formel in den weiteren Spalten aus?
Für Vorschläge, Lösungsansätze oder Alternativen bin ich sehr dankbar. Leider ist mein spezielles Excel-Wissen nicht so gut und mit Makros oder gar VBA-Programmierung kenn ich mich gar nicht aus.
Ich danke schon mal allen, die sich die Mühe machen mir zu helfen!
cnostra
Wir arbeiten bei uns in der Firma hauptsächlich mit Excel und für jeden Vorgang (Klient) wird eine Excel-Vorlage ausgefüllt. Entsprechend gibt es nach Klienten bezeichnete Excel-Dateien in nach Klienten bezeichneten Ordnern. Derzeit verknüpfen wir in einer Übersichtstabelle (extra Datei) mühsam die einzelnen relevanten Felder.
Folgende Idee soll das in Zukunft vereinfachen:
In Spalte A soll als einziger Arbeitsschritt nur noch der Pfad (immer ein anderer Unterordner) der jeweiligen Datei als Quelldatei angegeben werden (am besten so, dass mit "Durchsuchen" die Datei ausgewählt wird, damit keine Fehler entstehen durch eine direkte Pfadeingabe). Alle weiteren Spalten sollen dann mit den relevanten Feldern dieser Quelldatei verknüpft sein und übernehmen deren Inhalte.
Beispiel Übersichtstabelle:
Spalte A | Spalte B | Spalte C | Spalte D | usw. |
Quelldatei | Name, Vorname | Geburtsdatum | Wohnort | usw. |
Bitte Quelldatei angeben | Zelle B5 der in Spalte A benannten Quelldatei | Zelle B7 der in Spalte A benannten Quelldatei | Zelle C5 der in Spalte A benannten Quelldatei | usw. |
Ich möchte nun nur die Quelldatei in Spalte A angeben und Spalte B - D usw. werden automatisch mit den Zellen in dieser Quelldatei verknüpft. In meiner Vorstellung sieht das so aus: Ich brauche in jeder Spalte eine Formel, die das Verknüpfen einer bestimmten Zelle in der in Spalte A benannten Quelldatei vornimmt.
Nun müsste ich wissen, wie ich es realisiere, dass ich in Spalte A diese Quelldatei definiere und mich in allen weiteren Spalten darauf beziehe. Wie könnte so eine Verknüpfung funktionieren? Wie sähe eine solche Formel in den weiteren Spalten aus?
Für Vorschläge, Lösungsansätze oder Alternativen bin ich sehr dankbar. Leider ist mein spezielles Excel-Wissen nicht so gut und mit Makros oder gar VBA-Programmierung kenn ich mich gar nicht aus.
Ich danke schon mal allen, die sich die Mühe machen mir zu helfen!
cnostra
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 134528
Url: https://administrator.de/forum/importieren-von-mehreren-zellen-aus-vielen-quelldateien-in-eine-uebersichtstabelle-in-excel-2003-134528.html
Ausgedruckt am: 25.12.2024 um 14:12 Uhr
11 Kommentare
Neuester Kommentar
Moin cnostra,
da muss ich noch mal rückfragen....
In den Spalten A, B, C etc stehen nun tatsächlich viele viele Werte Namen, Geburtsdatum, Wohnort untereinander?
Oder soll wirklich nur jeweils eine einzige Zelle mit Inhalten aus einer auswählbaren XLS Datei von "festen" absoluten Koordinaten gefüllt werden?
Grundsätzlich geht die Lösung über
Für das Entweder/Oder wäre wichtig, wie euer weiterer Arbeitsprozess aussieht.
Es wäre fatal, wenn die Endergebnisdatei z.B. zum Rechnung-Ausdrucken verwendet wird und sich auch am 15.1. zur Rechnungsstellung verwenden ließ, aber eine Woche später nicht reproduzierbar ist, weil die Referenz-Daten nicht mehr da oder geändert sind...
Grüße
Biber
da muss ich noch mal rückfragen....
In den Spalten A, B, C etc stehen nun tatsächlich viele viele Werte Namen, Geburtsdatum, Wohnort untereinander?
Oder soll wirklich nur jeweils eine einzige Zelle mit Inhalten aus einer auswählbaren XLS Datei von "festen" absoluten Koordinaten gefüllt werden?
Grundsätzlich geht die Lösung über
- entweder über Makro-Formeln VERWEIS() und INDIREKT() ... dann müssen aber auch die referenzierten Tabellen vorliegen (ohne vorliegende Referenz-Tabellen kannst du die XLS-Datei nirgendwo nutzen)
- oder aber über VBA-Makros... dann kannst du es quasi "einmal" auflösen und in der End-Ergebnistabelle nur die (kopierten) Werte abspeichern.
Für das Entweder/Oder wäre wichtig, wie euer weiterer Arbeitsprozess aussieht.
Es wäre fatal, wenn die Endergebnisdatei z.B. zum Rechnung-Ausdrucken verwendet wird und sich auch am 15.1. zur Rechnungsstellung verwenden ließ, aber eine Woche später nicht reproduzierbar ist, weil die Referenz-Daten nicht mehr da oder geändert sind...
Grüße
Biber
Hallo cnostra, Hallo Biber!
Ich habe mal was zum Testen zusammengebastelt
Funktion:
Beim Starten des Makros über <Menu><Extras><Makro><Makros><GetExternData><Ausführen><GetExternData>, wird zur Auswahl einer Datei ein Datei-Öffnen-Dialog ausgegeben. Der Start-Ordner wird in einer Konstanten entsprechen festgelegt. Es können nur *.xls-Dateien geöffnet werden. Nach Auswahl einer Datei wird in der Übersicht-Tabelle in der nächsten freien Zeile (Zelle Spalte A = Leer), in Spalte A der Pfad der Quelldatei eingetragen und ab Spalte B die Verknüpfungen zu den Zellen, die in der Konstanten "ExternCells aufgelistet sind.
Erste Schritte:
Den Quelltext im VB-Editor in ein Modul kopieren
Die Konstanten (Const) entsprechend anpassen, wobei darauf zu achten ist, dass die Zellangaben in der Konstanten ExternCells im gleichen Format angepasst bzw. erweitert werden, also ohne Leerzeichen etc., allerdings können auch absolute Adressen in der Form "$B$5,$B$7,$C$7" verwendet werden.
Das Makro sollte idealerweise über <Menu><Extras><Makro><Makros><GetExternData><Optionen> mit einer Tastenkombination verknüpft werden.
Diesen Quelltext im VB-Editor in ein Modul kopieren:
Gruß Dieter
Ich habe mal was zum Testen zusammengebastelt
Funktion:
Beim Starten des Makros über <Menu><Extras><Makro><Makros><GetExternData><Ausführen><GetExternData>, wird zur Auswahl einer Datei ein Datei-Öffnen-Dialog ausgegeben. Der Start-Ordner wird in einer Konstanten entsprechen festgelegt. Es können nur *.xls-Dateien geöffnet werden. Nach Auswahl einer Datei wird in der Übersicht-Tabelle in der nächsten freien Zeile (Zelle Spalte A = Leer), in Spalte A der Pfad der Quelldatei eingetragen und ab Spalte B die Verknüpfungen zu den Zellen, die in der Konstanten "ExternCells aufgelistet sind.
Erste Schritte:
Den Quelltext im VB-Editor in ein Modul kopieren
Die Konstanten (Const) entsprechend anpassen, wobei darauf zu achten ist, dass die Zellangaben in der Konstanten ExternCells im gleichen Format angepasst bzw. erweitert werden, also ohne Leerzeichen etc., allerdings können auch absolute Adressen in der Form "$B$5,$B$7,$C$7" verwendet werden.
Das Makro sollte idealerweise über <Menu><Extras><Makro><Makros><GetExternData><Optionen> mit einer Tastenkombination verknüpft werden.
Diesen Quelltext im VB-Editor in ein Modul kopieren:
Option Explicit
Const InternSheet = "Tabellenname Übersicht" 'Der Name der Tabelle mit der Übersicht
Const ExternSheet = "Tabellenname externe Tabellen" 'Der Name der Tabelle in den Kundendateien
Const ExternCells = "B5,B7,C7" 'Angabe der Zellen in Tabelle Kundendateien
Const StartFolder = "X:\Test\Kunden" 'Angabe des Start-Ordners der Kundendateien
Sub GetExternData()
Dim LinkCells As Variant, LinkPath As Variant, Path As String, NextLine As Long, i As Integer
Path = GetFilePath
If Path = "" Then Exit Sub 'Bei File-Dialog <Abbrechen> Makro beenden
Sheets(InternSheet).Activate
Application.ScreenUpdating = False
NextLine = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(NextLine, 1) = Path
LinkPath = Split(Path, "\")
LinkPath(UBound(LinkPath)) = "[" & LinkPath(UBound(LinkPath)) & "]"
LinkPath = "='" & Join(LinkPath, "\") & ExternSheet & "'!"
LinkCells = Split(ExternCells, ",")
For i = 0 To UBound(LinkCells)
Cells(NextLine, i + 2).Formula = LinkPath & LinkCells(i)
Next
Application.ScreenUpdating = True
End Sub
Private Function GetFilePath() As String
Dim Dlg As FileDialog
Set Dlg = Application.FileDialog(msoFileDialogOpen)
Dlg.InitialFileName = StartFolder
Dlg.Filters.Add "Excel Dateien", "*.xls", 1
If Dlg.Show = False Then GetFilePath = "" Else GetFilePath = Dlg.SelectedItems(1)
End Function
Gruß Dieter
Hmmja, cnostra und didi1954,
jetzt kamen eure Antworten relativ zeitgleich...und wie das Leben so spielt natürlich nicht mit deckungsgleichen Implikationen...
@cnostra
Danke für deine Erläuterung... jetzt glaube ich es verstanden zu haben.
Allerdings - so, wie ich oben meine Entweder/Oder-Fragestellung hergeleitet habe und so wie die Antwort aussieht wäre eigentlich meine Schlussfolgerung:
Ja, Hey! Dann brauchst du für dein Übersichts-Excel keine VBA-Makros, sondern kannst "live" ausschliesslich in den Tabellenfeldern fortlaufend neue Daten in Spalte A eintippseln, wenn du die brauchst... die Zuordnung geht mit in allen Zeilen jeweils gleichen VERWEIS() und INDIREKT()-Formeln in den Spalten B, C, D....
Einen automatisierten Lauf per VBA würde ich dann erwägen, wenn ich tatsächlich einen konkreten "einfrierbaren Stand" haben will, beispielsweise den Zustand dieser Meiterübersicht am 15.1.2010... also am 15.1.2010 so eine Übersichtstablle erstellen/berechnen lassen per VBA und dann aber auch als einmal berechnete und nur Werte enthaltende Datei "XY-Übersicht_2010-01-15.xls" abspeichern.
@Dieter
Andererseits...
andererseits hat natürlich auch deine Lösung ihren gewohnten Charme. Und insbesondere was die Geschwindigkeit bei "Neuberechnung" angeht ist natürlich ein VBA-Makro um Klassen irgendwelchem INDIREKT/VERWEIS-Vehikel überlegen.
Ich halte mich erstmal zurück und verfolge mit, wie sich der Skript-Ansatz weiterentwickelt...
Grüße
Biber
jetzt kamen eure Antworten relativ zeitgleich...und wie das Leben so spielt natürlich nicht mit deckungsgleichen Implikationen...
@cnostra
Danke für deine Erläuterung... jetzt glaube ich es verstanden zu haben.
Allerdings - so, wie ich oben meine Entweder/Oder-Fragestellung hergeleitet habe und so wie die Antwort aussieht wäre eigentlich meine Schlussfolgerung:
Ja, Hey! Dann brauchst du für dein Übersichts-Excel keine VBA-Makros, sondern kannst "live" ausschliesslich in den Tabellenfeldern fortlaufend neue Daten in Spalte A eintippseln, wenn du die brauchst... die Zuordnung geht mit in allen Zeilen jeweils gleichen VERWEIS() und INDIREKT()-Formeln in den Spalten B, C, D....
Einen automatisierten Lauf per VBA würde ich dann erwägen, wenn ich tatsächlich einen konkreten "einfrierbaren Stand" haben will, beispielsweise den Zustand dieser Meiterübersicht am 15.1.2010... also am 15.1.2010 so eine Übersichtstablle erstellen/berechnen lassen per VBA und dann aber auch als einmal berechnete und nur Werte enthaltende Datei "XY-Übersicht_2010-01-15.xls" abspeichern.
@Dieter
Andererseits...
andererseits hat natürlich auch deine Lösung ihren gewohnten Charme. Und insbesondere was die Geschwindigkeit bei "Neuberechnung" angeht ist natürlich ein VBA-Makro um Klassen irgendwelchem INDIREKT/VERWEIS-Vehikel überlegen.
Ich halte mich erstmal zurück und verfolge mit, wie sich der Skript-Ansatz weiterentwickelt...
Grüße
Biber
Hallo Biber!
Gruß Dieter
Zitat von @Biber:
Einen automatisierten Lauf per VBA würde ich dann erwägen, wenn ich tatsächlich einen konkreten "einfrierbaren
Stand" haben will, beispielsweise den Zustand dieser Meiterübersicht am 15.1.2010... also am 15.1.2010 so eine
Übersichtstablle erstellen/berechnen lassen per VBA und dann aber auch als einmal berechnete und nur Werte enthaltende Datei
"XY-Übersicht_2010-01-15.xls" abspeichern.
Wieso einfierbaren Stand? Es werden doch Verknüpfungen auf die Original-Datei eingefügt und beim Öffnen der Übersicht wird dann gefragt, ob die Daten aktualisiert werden sollenEinen automatisierten Lauf per VBA würde ich dann erwägen, wenn ich tatsächlich einen konkreten "einfrierbaren
Stand" haben will, beispielsweise den Zustand dieser Meiterübersicht am 15.1.2010... also am 15.1.2010 so eine
Übersichtstablle erstellen/berechnen lassen per VBA und dann aber auch als einmal berechnete und nur Werte enthaltende Datei
"XY-Übersicht_2010-01-15.xls" abspeichern.
Ich halte mich erstmal zurück und verfolge mit, wie sich der Skript-Ansatz weiterentwickelt...
Dem schließe ich mich anGruß Dieter
Hallo cnostra!
Beim Starten des Makro's öffnest Du über den File-Dialog eine Datei und alles andere geht automatisch, sofern Du die Konstannten entsprechend angepasst hast
Gruß Dieter
Zitat von @ASBmark:
Letzlich möchte ich halt wirklich nur noch einen Schritt haben: In Spalte A wird Zeile für Zeile eine Quelldatei angegeben.
Spalte B und folgende beziehen sich dann in jeder Zeile auf die Datei in Spalte A, die in der selben Zeile angegeben ist, und dann
immer auf beispielsweise Zelle B4 (derzeit wird das mit Copy und Inhalte Einfügen -> Verknüpfen für jede dieser
Zellen einzeln gemacht).
Genau das macht mein Makro!Letzlich möchte ich halt wirklich nur noch einen Schritt haben: In Spalte A wird Zeile für Zeile eine Quelldatei angegeben.
Spalte B und folgende beziehen sich dann in jeder Zeile auf die Datei in Spalte A, die in der selben Zeile angegeben ist, und dann
immer auf beispielsweise Zelle B4 (derzeit wird das mit Copy und Inhalte Einfügen -> Verknüpfen für jede dieser
Zellen einzeln gemacht).
Beim Starten des Makro's öffnest Du über den File-Dialog eine Datei und alles andere geht automatisch, sofern Du die Konstannten entsprechend angepasst hast
Gruß Dieter
...
Gut, dass du nach Alternativen fragst. Mir scheint, du solltest die gesamte Herangehensweise gründlich überdenken. Was du mühsam aufgebaut hast und nun langsam nicht mehr im Griff hast, ist eine relationale Datenbank. Und so etwas macht man wesentlich besser mit einem echten Dantenbank-Programm und nicht mit Excel und schon gar nicht mit hunderten verschiedenen Dateien!
Für deinen Einsatzfall bietet sich Access geradezu an. Da kannst du alle Daten in einer einzigen Datei speichern und die von dir gewünschten Auswertungen sind ein Kinderspiel. Sieh mal nach, ob in deiner Office-Version Access mit enthalten ist. Wenn ja, entstehen dir nicht einmal Kosten für Softeware-Beschaffung.
Falls du zur Überzeugung kommen solltest, dass du grundlegend etwas ändern musst, dann melde dich mal bei mir.
Gruß
Hans
Für Vorschläge, Lösungsansätze oder Alternativen bin ich sehr dankbar. Leider ist mein spezielles Excel-Wissen
nicht so gut und mit Makros oder gar VBA-Programmierung kenn ich mich gar nicht aus.
nicht so gut und mit Makros oder gar VBA-Programmierung kenn ich mich gar nicht aus.
Gut, dass du nach Alternativen fragst. Mir scheint, du solltest die gesamte Herangehensweise gründlich überdenken. Was du mühsam aufgebaut hast und nun langsam nicht mehr im Griff hast, ist eine relationale Datenbank. Und so etwas macht man wesentlich besser mit einem echten Dantenbank-Programm und nicht mit Excel und schon gar nicht mit hunderten verschiedenen Dateien!
Für deinen Einsatzfall bietet sich Access geradezu an. Da kannst du alle Daten in einer einzigen Datei speichern und die von dir gewünschten Auswertungen sind ein Kinderspiel. Sieh mal nach, ob in deiner Office-Version Access mit enthalten ist. Wenn ja, entstehen dir nicht einmal Kosten für Softeware-Beschaffung.
Falls du zur Überzeugung kommen solltest, dass du grundlegend etwas ändern musst, dann melde dich mal bei mir.
Gruß
Hans