Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Importieren von mehreren Zellen aus vielen Quelldateien in eine Übersichtstabelle in Excel 2003

Frage Microsoft Microsoft Office

Mitglied: cnostra

cnostra (Level 1) - Jetzt verbinden

27.01.2010, aktualisiert 20:30 Uhr, 11056 Aufrufe, 11 Kommentare

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:

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
Mitglied: Biber
27.01.2010 um 21:40 Uhr
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
  • 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
Bitte warten ..
Mitglied: cnostra
28.01.2010 um 11:09 Uhr
Hi Biber,

danke Dir erstmal, dass Du Dich meinem Problem annimmst.

Ich versuche erstmal das ganze Konzept noch etwas klarer darzustellen:

In Excel-Datei möchte ich zu Übersichtszwecken 100 und mehr Excel-Dateien zusammenfassen. In Spalte A dieser Datei würde ich gerne in jeder Zeile eine dieser Quelldateien angeben (Ort jeder einzelnen Datei ist im Netzwerk fest und bleibt unverändert, jede liegt aber in einem anderen Unterverzeichnis). Aufbau der Quelldateien ist immer gleich, da aus einer Vorlage immer wieder neu ausgefüllt.
in den folgenden Spalten möchte ich nun bestimmte Zellen dieser Quelldatei einmal definiert und dann automatisch verknüpft bekommen.

Um es etwas mehr zum Anfassen zu machen:
In der Quelldatei werden Mieternamen, Vertragsbeginn, Vertragsende, Miethöhe, Kautionshöhe, aktueller Kontostand des Mietenkontos und eine Reihe anderer Infos hinterlegt. Dies soll für 100 Mieter und mehr in einer einzigen Tabelle zur statistischen Auswertung und zu Filterzwecken zusammen gefasst werden. Nun möchte ich aber halt nicht jedes Mal 20 Zellen verknüpfen, sondern da sich die Zellen nicht ändern nur einmal die jeweilige Quelldatei benennen und den Rest sucht sich Excel "automatisch".

Kommt nun ein neuer Mieter hinzu, lege ich die Quelldatei an und in der Übersichtsdatei wird eine weitere Zeile hinzugefügt und der Ort der Quelldatei angegeben und schwups ist die Zeile ausgefüllt.

Für uns ist ein bestimmter Stand zu einem in der Vergangenheit liegenden Datum nicht wichtig (wenn auch sicherlich manchmal praktisch). Das kann aber vernachlässigt werden.

Konkret zu Deinen Rückfragen:
Die Quelldateien haben immer beispielsweise im Feld J6 die Miethöhe stehen und in Feld J15 steht immer der akuelle Stand des Mietenkontos. Das ist für Mieter X in Zeile 23 genau das gleiche wie für den Mieter Y in Zeile 45.

Die Daten werden ausschließlich zur Übersicht und zur statistischen Auswertung in diesen Tabellen zusammen gefasst. Entsprechend ist eine Reproduzierbarkeit an einem bestimmten Datum nicht notwendig.

Ich hoffe, ich konnte Dir den Fall etwas näher bringen und habe Deine Rückfragen zufriedenstellend beantwortet.

Grüße
cnostra
Bitte warten ..
Mitglied: 76109
28.01.2010 um 11:13 Uhr
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:
01.
Option Explicit 
02.
 
03.
Const InternSheet = "Tabellenname Übersicht"		'Der Name der Tabelle mit der Übersicht 
04.
Const ExternSheet = "Tabellenname externe Tabellen"	'Der Name der Tabelle in den Kundendateien 
05.
 
06.
Const ExternCells = "B5,B7,C7"			'Angabe der Zellen in Tabelle Kundendateien 
07.
 
08.
Const StartFolder = "X:\Test\Kunden"			'Angabe des Start-Ordners der Kundendateien 
09.
 
10.
Sub GetExternData() 
11.
    Dim LinkCells As Variant, LinkPath As Variant, Path As String, NextLine As Long, i As Integer 
12.
     
13.
    Path = GetFilePath 
14.
     
15.
    If Path = "" Then Exit Sub			'Bei File-Dialog <Abbrechen> Makro beenden 
16.
     
17.
    Sheets(InternSheet).Activate 
18.
     
19.
    Application.ScreenUpdating = False 
20.
     
21.
    NextLine = Cells(Rows.Count, "A").End(xlUp).Row + 1 
22.
     
23.
    Cells(NextLine, 1) = Path 
24.
     
25.
    LinkPath = Split(Path, "\") 
26.
     
27.
    LinkPath(UBound(LinkPath)) = "[" & LinkPath(UBound(LinkPath)) & "]" 
28.
     
29.
    LinkPath = "='" & Join(LinkPath, "\") & ExternSheet & "'!" 
30.
     
31.
    LinkCells = Split(ExternCells, ",") 
32.
     
33.
    For i = 0 To UBound(LinkCells) 
34.
        Cells(NextLine, i + 2).Formula = LinkPath & LinkCells(i) 
35.
    Next 
36.
     
37.
    Application.ScreenUpdating = True 
38.
End Sub 
39.
 
40.
Private Function GetFilePath() As String 
41.
    Dim Dlg As FileDialog 
42.
    
43.
    Set Dlg = Application.FileDialog(msoFileDialogOpen) 
44.
     
45.
    Dlg.InitialFileName = StartFolder 
46.
    Dlg.Filters.Add "Excel Dateien", "*.xls", 1 
47.
     
48.
    If Dlg.Show = False Then GetFilePath = "" Else GetFilePath = Dlg.SelectedItems(1) 
49.
End Function
Gruß Dieter
Bitte warten ..
Mitglied: Biber
28.01.2010 um 11:43 Uhr
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
Bitte warten ..
Mitglied: 76109
28.01.2010 um 14:52 Uhr
Hallo Biber!

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 sollen
Ich halte mich erstmal zurück und verfolge mit, wie sich der Skript-Ansatz weiterentwickelt...
Dem schließe ich mich an

Gruß Dieter
Bitte warten ..
Mitglied: cnostra
28.01.2010 um 15:30 Uhr
Hmm, ihr seid beide schon nicht auf dem verkehrten Weg, soweit ich das beurteilen kann.

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).

Weitere Recherchen meinerseits haben folgendes Ergeben:
http://www.office-loesung.de/ftopic72789_0_0_asc.php

Allerdings komme ich da mit meinem Bescheidenen Wissen auch nicht so viel weiter.

Dieter hat allerdings soweit Recht, dass dieser "einfrierbare Stand" zu vernachlässigen ist. Entweder ich aktualisiere beim Öffnen, oder lasse es eben

LG
cnostra
Bitte warten ..
Mitglied: 76109
28.01.2010 um 15:47 Uhr
Hallo cnostra!

Zitat von cnostra:
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!

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
Bitte warten ..
Mitglied: hybadd
28.01.2010 um 19:25 Uhr
Zitat von cnostra:
...
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.


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
Bitte warten ..
Mitglied: cnostra
28.01.2010 um 20:06 Uhr
Hallo Hans,

prinzipiell hast Du natürlich Recht. Ich würde sogar nen Schritt weiter gehen und sagen, dass Access durch andere professionelle, nicht selbst geschriebene Softwarelösungen ersetzt werden kann.

Der jetztige Schritt kann aber leider nur sein, das bisherige zu verfeinern. Denn über den Jahreswechsel sind grade enorme Kapazitäten in das bestehende System der relationalen Datenbank geflossen. Hier wäre halt wünschenswert den administrativen Teil etwas abzuspecken und Automatismen einzubinden, wie in der jetzigen Form von mir angedacht.

Es geht hierbei um die Verwaltung von Klienten und Mietern und entsprechend einer Mieter- und einer Klienten-Übersicht. Seit neustem stecken da auch noch zwei Firmen hinter. Das bestehende System funktioniert, kann aber verbessert werden. Jetzt auf eine andere Herangehensweise umzusatteln würde Zeit und Manpower binden, die nicht vorhanden sind.

Es ist das leidige Thema, mit dem sich wohl jedes mittelständische Unternehmen irgendwann auseinander setzen muss, dass anfangs mit Excel gut zurecht gekommen ist und aus diesem Schuh langsam raus wächst.

Danke für Deinen Kommentar dazu. Hier gehts aber wie gesagt "nur" um eine Verfeinerung. Einen Paradigmenwechsel nehmen wir dann wann anders vor
Bitte warten ..
Mitglied: hybadd
28.01.2010 um 20:27 Uhr
Habe dir eine PN geschrieben.
Hans
Bitte warten ..
Mitglied: cnostra
03.02.2010 um 10:55 Uhr
Danke Hans, komme ggf zu einem späteren Zeitpunkt darauf zurück!


Vielleicht kann einer von Euch hierzu noch etwas sagen? Weitere Recherchen haben den folgenden Code ausgespuckt, der für mein Problem wohl eine Lösung darstellt:

01.
Sub DatenAusDateien() 
02.
 
03.
Dim AnzahlDateien%, zeile%, spalte% 
04.
Dim Dateiname As String, datensatz As String 
05.
 
06.
Application.ScreenUpdating = False 
07.
 
08.
AnzahlDateien = Cells(Rows.Count, 2).End(xlUp).Row 
09.
 
10.
For zeile = 2 To AnzahlDateien 
11.
    Dateiname = Cells(zeile, 2).Value 
12.
    For spalte = 3 To 5 
13.
        datensatz = GetObject(Dateiname).Sheets("Tabelle1").Cells(zeile, spalte) 
14.
        Cells(zeile, spalte).Value = datensatz 
15.
    Next spalte 
16.
    GetObject(Dateiname).Close True 
17.
Next zeile 
18.
     
19.
Application.ScreenUpdating = True 
20.
 
21.
End Sub
Für eine Einschätzung bin ich dankbar!

Liebe Grüße
cnostra
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
VB for Applications
gelöst Excel VBA Eine oder mehrere Zellen Verschieben (2)

Frage von batchnewbie zum Thema VB for Applications ...

VB for Applications
Bestimmte Daten aus eine CSV-Datei in eine Excel-Tabelle importieren (2)

Frage von MariaElena zum Thema VB for Applications ...

Microsoft Office
gelöst Excel 2010 Zellen mit bestimmten Inhalt mit Makro formartierten (5)

Frage von packmann2016 zum Thema Microsoft Office ...

Batch & Shell
CSV-Datei nach Excel importieren (5)

Frage von mrvfbnummer2 zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (20)

Frage von Xaero1982 zum Thema Microsoft ...

Outlook & Mail
gelöst Outlook 2010 findet ost datei nicht (19)

Frage von Floh21 zum Thema Outlook & Mail ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Festplatten, SSD, Raid
M.2 SSD wird nicht erkannt (14)

Frage von uridium69 zum Thema Festplatten, SSD, Raid ...