Office: automatisierte Übersicht in Excel aus einzelnen Dateien

Hallo zusammen!

Ich würde gerne in Excel (2007) eine Liste erstellen, die sich selbständig automatisiert:

Ich habe leider keine Idee, wie ich hier automatisch Dateinamen reinziehen lasse. Manuelle, fortlaufende Eingabe bringt mir nichts, weil die Dateinamen aus Rechnungsnummer + Erstelldatum (nicht vorhersehbar) bestehen.

In Spalte B soll automatisch der Gesamtwert aus der jeweiligen Datei (Zelle ist immer die gleiche) gezogen werden, wenn ich eine neue Datei erstelle.

Im schlimmsten Fall würde ich mich darauf einlassen, das Rechnungsdatum aus dem Dateinamen wegzulassen - bringt aber nix, weil auch noch eine Art "Kundencode" mit drin ist, auf den ich nicht verzichten will.

Man muss also in Spalte A eine Formel haben, die die neu erstellte Datei anhand der ersten 10 Zeichen erkennt und sich den Rest dann selber dazu denkt, damit man diese Zelle in der Definition in B angeben kann, was aber auch nicht immer so geht ("='F:\!Daten!\!_Rechnungen\[Dateiname bzw. Zellbezug aus SpalteA]Tabelle1'!$G$43").

Das ist gar nicht so einfach zu erklären... kann jemand nachvollziehen, was ich will? Wahrscheinlich wäre ich mit einer Datenbank besser bedient, ich weiß, aber geht das in Excel nicht auch irgendwie?

 

Hallo Leo,

meinst Du so
="'F:\!Daten!\!_Rechnungen\[" Range("A1") & "]Tabelle1'!$G$43"
Ich habe jetzt hier kein Excel, darum Live.

Gruß Hajo

 

Kenn ich nicht, wie wende ich das an?

Hallo Hajo,

Danke erst einmal für Deine Antwort.

Leider klappt die Formel so nicht. Erklärst Du mir das mit dem "Range..."? Wenn ich verstehe, statt nur reinzukopieren, dann kann ich sie vielleicht anpassen.

 

Hallo leo,

das war ein Fehler
="'F:\!Daten!\!_Rechnungen\[" & Tabelle1!A1 & "]Tabelle1'!$G$43"

Gruß Hajo

 

ich glaube nicht...

...dass ich das meine.

Die Übersicht ist dateiübergreifend, nicht nur tabellenübergreifend. Und die Dateien exisitieren noch nicht, werden nach und nach geschrieben, so dass ich die Namen noch nicht habe.

Auch mit dieser Formel habe ich noch einen Bezugsfehler - irgendwas daran muss ich doch sicher an meine Daten anpassen, oder?

 

Hallo Leo,

die Problematik war mir nicht ganz klar.
Ich habe nur daraus lesen können das Du eine Dateinamen zusammenbauen willst.
Tabell1!A1 mußt Du an Deine Bedingungen anpassen.

Gruß Hajo

 

Genau! Und da soll am besten ein Bezug auf die Zelle in Spalte A rein (s.u.) und dann soll Excel bitte anhand von "Näherungswerten" automatisch die passende Datei aus dem Ordner suchen, in dem der ganze Kram liegt :-)

Ich habe in Spalte A die durchlaufende Rechnungsnummer, mit der der eigentliche Dateiname anfängt. Das soll nun Excel reichen, um sich seinen Teil zu denken und den Wert der entsprechenden Datei zu verwenden - soweit zumindest meine Vorstellung...

 

Hallo Leo,

in der Spalte A stehen Werte und welchen Wert soll sich Excel raussuchen, nach welchen Bedingungen?
Das kann ich bestimmt nicht ohne Excel und meinen HomeComputer lösen. Das wird erst nach 17:00 Uhr was.

Gruß Hajo

 

...dann lass uns dann noch mal schreiben. Ist nicht eilig.

 

Hallo Leo,

du hast Dich zu den Bedingungen nicht geäußert. Ich vermute mal Du möchtest den letzten Wert in Spalte A haben. Ich bin mal Davon ausgegangen das Ab A7 die Spalte geüllt ist.
Das Ergebnis ist aber kein Dateiname für das speichern
="'F:\!Daten!\!_Rechnungen\[" & INDEX(A7:A41;ANZAHL2(A7:A41)) & "]" & Tabelle1!$G$43

In Spalte A steht der Dateiname einschl. dem Dateityp dann so
="'F:\!Daten!\!_Rechnungen\" & INDEX(A7;ANZAHL2(A7:A41))

bis 18:00 Uhr bin ich noch online.

Gruß Hajo

 

Tja, nicht ganz einfach zu beschreiben

Ich versuch's noch mal ganz von vorne:

Es geht um die Kumulation von vielen, z.T. noch zu erstellenden Exceldateien (=Rechnungen), bei denen immer ein und die selbe Zelle wichtig ist (sach halt $H$40). Dieser Wert soll in einer "Zusammenfassung.xls" automatisch ergänzt und wiedergegeben werden, sobald eine neue Datei dazu kommt (was dann in dieser Datei passiert, hab ich im Griff, ist egal.).

Praktisch sieht das so aus, dass in der kompletten Spalte A der Teil die Rechnungsnummer steht (zB "109") und in Spalte B jeweils der entsprechende Endbetrag dieser Rechnung (Zelle $H$40, wie immer) ausgewiesen sein soll.
Leider setzen sich die Namen der zu kumulierenden Excel-Dateien aus mehr als nur der Rechnungsnummer zusammen (zB "109_090531_kda.xls"), so dass ich diesen Automatismus in Spalte B zum Wiedergeben des Betrages nicht hinkriege.

Man müsste sich auf eine externe Datei beziehen, deren Namen sich zusammen setzt aus dem Wert der Spalte A und es dann schaffen, dass Excel einen Näherungswert nimmt (das kenn ich von S- und WVerweisen).

Vielleicht war es jetzt eindeutiger...

 

Hallo!

Ich denke, in allen bisherigen Formeln fehlt die Funktion INDIREKT:

=INDIREKT("'F:\!Daten!\!_Rechnungen\[" & Tabelle1!A1 & "]Tabelle1'!$G$43")

holt den Wert aus der entsprechenden Tabelle, wenn diese geöffnet ist.

 

Hallo Leo,

Auf Grund dieser Bemerkung nehme ich an, möchtest Du Folgendes:
Du hast in einem fix vorgegeben Verzeichnis (zB. I:\Excel\Forum\) mehrere Excel-Dateien (zB.: *.xls), deren Dateiname mit dem Wert, den Du im Arbeitsblatt in Spalte A hast (zB. AR5710), beginnt. Mit diesen Angaben soll die Datei genau identifizierbar sein, dh. der Rest des Dateinamens bis zum Punkt (zB. 20090513) der Dateierweiterung soll belanglos für die Identifizierung sein.
Darüber hinaus enthalten diese Exceldateien ein bestimmtes Arbeitsblatt (zB. Tabelle9), das in einer bestimmten Zelle (zB. C20) einen Wert enthält, der in Spalte B Deines Arbeitsblattes erscheinen soll.

Wenn ich von diesen Voraussetzungen ausgehe, müsste also Excel zB. in Zelle B8 (falls in Zelle A8 gemäß obiger Beispieldaten 'AR5710' steht) folgende Formel automatisch generieren:
='I:\Excel\Forum\[AR571020090513.xls]Tabelle9'!C20

Das kann man, soweit ich weiß, nicht mit Arbeitsblattfunktionen erreichen. Man muss dazu VBA-Makros heranziehen:

Code:

Public Sub ExternerBezug()
  Dim S$, Pfad$, Match$, BlattZelle$
  With ActiveSheet
    Pfad$ = .Range("K1").Value
    Match$ = .Range("K2").Value
    BlattZelle$ = .Range("K3").Value
  End With
  With ActiveCell
    S$ = Pfad$ & .Offset(0, -1).Value & Match$
    S$ = Dir(S$, vbNormal)
    .Formula = "='" & Pfad$ & "[" & S$ & "]" & Replace(BlattZelle$, "!", "'!")
  End With
End Sub

Dieses Makro ist in ein allgem. Codemodul einzufügen.
Damit dieses Makro möglichst einfach aufzurufen ist, ist noch ein weiteres Makro in das Klassen-Codemodul von 'DieseArbeitsmappe' zu speichern:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Application.OnKey "^ü", "ExternerBezug"
End Sub

Ferner ist in Zelle K1 der fixe Pfad zu speichern, zB. 'I:\Excel\Forum\
weiters in Zelle K2 der Excel-Datei-Matchcode, zB. '*.xls
weiters in Zelle K3 das Tabellenblatt mit der Zelle, zB. Tabelle9!C20

Wird dann zB. in Zelle A8 Folgendes eingegeben: AR5170, die aktive Zelle nach B8 gestellt und die Tastenkombination Strg+ü gedrückt, erscheint in Zelle B8 die gewünschte Zugriffsformel und der Wert, der dort verspeichert ist, wird angezeigt.

 

Hallo @Schatzi,

ich hatte auch zuerst gedacht, mit INDIREKT und mit einem Pfadausdruck als Parameter müsste es gehen - die Tücke von Excel: Es wird nur #BEZUG zurückgeliefert. Außerdem soll die Datei per Matchcode à la 'SpalteA*.xls' ermittelt werden.

 

Hallo!

Wie ich schon schrieb: dazu muss diese Datei geöffnet sein.
INDIREKT kann nicht mit geschlossenen Dateien umgehen, daher ist (d)eine VBA-Lösung hier wohl angebrachter, da diese Dateien teilweise wohl ja noch nicht einmal zu existieren scheinen.