Bezug zu einer anderen Datei mittels Platzhalter herstellen?
Wolfgang Janssen
ich habe eine "Master-Excel-Datei", die sich Werte aus ca. 30 anderen
Excel-Dateien nehmen soll ("Unter-Dateien").
Die benoetigten Werte stehen in den Unter-Dateien immer an derselben Stelle.
Also z.B. der benoetigte Wert "Darlehensstand" immer in Zelle A5 der
Unter-Dateien:
meier.xls
schmidt.xls
mueller.xls
kunz.xls
Nun muesste ich in der Master-Datei die Zellbezuege immer per
Hand herstellen.
Also z.B. [meier.xls]Tabelle1!'A5
Gibt es eine Moeglichkeit, in der Master-Tabelle Platzhalter zu definieren,
so dass die Formel sich den Datei-Namen dorther nimmt? Ich koennte
dann in der Master-Datei bequem mittels kopieren die Zellbezuege
herstellen.
Also in der Masterdatei würde im Feld A1 stehen "meier.xls"
und im Feld, zu dem der Zellbezug hergestellt werden soll:
[A1]Tabelle1!'A5
Ich benoetige also so eine Art "Serienbrieffunktion", nur halt
mit einer "Adressliste", die Datei-Namen enthaelt, die ich wiederum
in eine Formel einbauen kann.
Gruss
Wolfgang
Thomas Ramel
Wolfgang Janssen schrieb:
>
> Also in der Masterdatei würde im Feld A1 stehen "meier.xls"
> und im Feld, zu dem der Zellbezug hergestellt werden soll:
>
> [A1]Tabelle1!'A5
Verwende die INDIREKT()-Funktion. Im Ausdruck must Du den zellbezug als
String zusammensetzen:
=INDIREKT("'["&A1&"]Tabelle1'!A5")
--
Mit freundlichen Grüssen
Thomas Ramel
http://www.4853.ch/Schlesinger
http://www.swissgeo.ch/mapit.php3?www=http//www.4853.ch/Schlesinger&swissgeo_id=241718
Peter Jenkner
Thomas Ramel schrieb:
> Wolfgang Janssen schrieb:
> > Also in der Masterdatei würde im Feld A1 stehen "meier.xls"
> > und im Feld, zu dem der Zellbezug hergestellt werden soll:
> >
> > [A1]Tabelle1!'A5
>
> Verwende die INDIREKT()-Funktion. Im Ausdruck must Du den zellbezug als
> String zusammensetzen:
>
> =INDIREKT("'["&A1&"]Tabelle1'!A5")
Das Problem an der INDIREKT()-Funktion ist, dass sie nur mit *geöffneten* Quelldateien
funktioniert. Ich habe ein ähnlich gelagertes Problem, bei dem es sich aber ein Öffnen der
Quelldateien nicht praktikabel bin.
Ich suche also nach einer Möglichkeit für einen externen Bezug mit variablem Dateinamen,
bei dem die Quelldatei nicht geöffnet sein muss.
Hat da jemand einen Tipp?
Viele Grüße
Peter Jenkner
Thomas Ramel
Peter Jenkner schrieb:
>
> Das Problem an der INDIREKT()-Funktion ist, dass sie nur mit *geöffneten* Quelldateien
> funktioniert. Ich habe ein ähnlich gelagertes Problem, bei dem es sich aber ein Öffnen der
> Quelldateien nicht praktikabel bin.
Grundsätzlich funktionieren alle Formeln und Wert-Übertragungen bei
geöffneten Quelldateien. Auch bei Verknüpfungen wird die Quelle im
Hintergrund geöffnet, die Werte übertragen und die Datei wieder
geschlossen.
> Ich suche also nach einer Möglichkeit für einen externen Bezug mit variablem Dateinamen,
> bei dem die Quelldatei nicht geöffnet sein muss.
>
> Hat da jemand einen Tipp?
Da könntest Du höchstens noch mit einem Excel4-Makro via VBA den Wert
holen. Folgende Funktion fügt den String zusammen, der an das Makro
übergeben wird; die Sub ruft die Funktion auf und schreibt den Wert in
eine Zelle:
Private Function GetValue(path, file, sheet, range_ref)
'Retrieves a value from a closed workbook
'Nur in VBA zu gebrauchen
'From John Walkenbach:
'The GetValue function, listed below takes four arguments:
'path: The drive and path to the closed file (e.g., "d:\files")
'file: The workbook name (e.g., "99budget.xls")
'sheet: The worksheet name (e.g., "Sheet1")
'ref: The cell reference (e.g., "C4")
Dim arg As String
'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Public Sub HoleWert(Pfad As String, Datei As String, Blatt As String,
Zelle As String)
ActiveCell = GetValue(Pfad, Datei, Blatt, Zelle)
End Sub
Peter Jenkner
>Grüezi Peter
Ja, ein Schweizer! Grüezi, odr? :-)
Thomas Ramel schrieb:
>Peter Jenkner schrieb:
>>Das Problem an der INDIREKT()-Funktion ist, dass sie nur mit
>>*geöffneten* Quelldateien funktioniert. Ich habe ein ähnlich
>>gelagertes Problem, bei dem es sich aber ein Öffnen der
>>Quelldateien nicht praktikabel bin.
>Grundsätzlich funktionieren alle Formeln und Wert-Übertragungen bei
>geöffneten Quelldateien. Auch bei Verknüpfungen wird die Quelle im
>Hintergrund geöffnet, die Werte übertragen und die Datei wieder
>geschlossen.
Da muss ich leider widersprechen.
Die Excel-Hilfe sagt ausdrücklich:
"Verweist Bezug auf eine andere Arbeitsmappe (ein externer Bezug) muß diese Arbeitsmappe
geöffnet sein. Ist die Quellarbeitsmappe nicht geöffnet, liefert die INDIREKT-Funktion den
Fehlerwert #BEZUG!."
Auch im Selbstversuch bestätigt sich dies: Während ein "normaler" externer Bezug auch bei
geschlossener Quelldatei funktioniert und beim Öffnen der Zieldatei aktualisiert wird, geht dies
mit INDIREKT() *nicht*. Ein externer Bezug mit INDIREKT() wird nicht einmal als Verknüpfung
(Bearbeiten-Verknüpfungen) erkannt.
Ist die Quelldatei geöffnet, wird der Wert in der Zieldatei übernommen; aber sobald die
Quelldatei wieder geschlossen ist und in der Zieldatei auch nur eine andere Zelle desselben
Blattes geändert wird, erscheint wieder #BEZUG!.
Dieses Verhalten von INDIREKT() wundert mich ehrlich gesagt, da ich denke, dass externe Bezüge
zu variablen Quelldateien eigentlich öfter benötigt werden könnten (bei mir: Übernahme von Daten
aus verschiedenen Vorjahren).
>>Ich suche also nach einer Möglichkeit für einen externen Bezug mit
>>variablem Dateinamen, bei dem die Quelldatei nicht geöffnet sein muss.
>>
>>Hat da jemand einen Tipp?
>Da könntest Du höchstens noch mit einem Excel4-Makro via VBA den Wert
>holen. Folgende Funktion fügt den String zusammen, der an das Makro
>übergeben wird; die Sub ruft die Funktion auf und schreibt den Wert in
>eine Zelle:
Danke für die Funktion! Da muss ich allerdings noch jemanden zu Rate ziehen, der VBA kann... :-)
Bisher habe ich alle Probleme "nur" mit Excel-Funktionen lösen können. Und eigentlich wollte ich
ohne Makros auskommen. Aber es lässt sich wohl nicht vermeiden...
Viele Grüße
Peter Jenkner
Thomas Ramel
Peter Jenkner schrieb:
>
> Hallo Thomas, hallo allerseits!
>
> >Grüezi Peter
>
> Ja, ein Schweizer! Grüezi, odr? :-)
Genau! Wie Du das nur erkennen konntest? ;-)
> Thomas Ramel schrieb:
>
> >Grundsätzlich funktionieren alle Formeln und Wert-Übertragungen bei
> >geöffneten Quelldateien. Auch bei Verknüpfungen wird die Quelle im
> >Hintergrund geöffnet, die Werte übertragen und die Datei wieder
> >geschlossen.
>
> Da muss ich leider widersprechen.
> Die Excel-Hilfe sagt ausdrücklich:
>
> "Verweist Bezug auf eine andere Arbeitsmappe (ein externer Bezug) muß diese Arbeitsmappe
> geöffnet sein. Ist die Quellarbeitsmappe nicht geöffnet, liefert die INDIREKT-Funktion den
> Fehlerwert #BEZUG!."
Ja; so gesehen hast Du recht.
> Auch im Selbstversuch bestätigt sich dies: Während ein "normaler" externer Bezug auch bei
> geschlossener Quelldatei funktioniert und beim Öffnen der Zieldatei aktualisiert wird, geht dies
> mit INDIREKT() *nicht*. Ein externer Bezug mit INDIREKT() wird nicht einmal als Verknüpfung
> (Bearbeiten-Verknüpfungen) erkannt.
Da mit dem aktualisieren beim Öffnen der Zieldatei meinte ich; die
Quell-Dateien werden geöffnet, die Werte übertragen und die Quelldateien
wieder geschlossen.
> Ist die Quelldatei geöffnet, wird der Wert in der Zieldatei übernommen; aber sobald die
> Quelldatei wieder geschlossen ist und in der Zieldatei auch nur eine andere Zelle desselben
> Blattes geändert wird, erscheint wieder #BEZUG!.
>
> Dieses Verhalten von INDIREKT() wundert mich ehrlich gesagt, da ich denke, dass externe Bezüge
> zu variablen Quelldateien eigentlich öfter benötigt werden könnten (bei mir: Übernahme von Daten
> aus verschiedenen Vorjahren).
Wo genau der Unterschied zwischen einer direkten Verknüpfung und
derjenigen mit INDIREKT() besteht müsste vielleicht jemand aus dem hause
Microsoft näher erläutern. Tatsache ist, wie Du geschrieben hast, dass
sie bei geschlossenen Quelldateien #BEZUG! liefert.
> >Da könntest Du höchstens noch mit einem Excel4-Makro via VBA den Wert
> >holen. Folgende Funktion fügt den String zusammen, der an das Makro
> >übergeben wird; die Sub ruft die Funktion auf und schreibt den Wert in
> >eine Zelle:
>
> Danke für die Funktion! Da muss ich allerdings noch jemanden zu Rate ziehen, der VBA kann... :-)
> Bisher habe ich alle Probleme "nur" mit Excel-Funktionen lösen können. Und eigentlich wollte ich
> ohne Makros auskommen. Aber es lässt sich wohl nicht vermeiden...
Das ist das einzige, das mir noch einfällt. Du könntest die Funktion mit
dem Workschee_Change-Ereignis koppeln, damit der Wert angepasst wird,
wenn die Quelldatei geändert wird. Mit der Funktion resp. Sub hast Du
auch den 'Vorteil', dass nur der Wert in der Zelle steht und niemand die
Formel aus Versehen 'zerschiessen' kann.
Dann also viel Spass mit VBA. Bei Problemen kannst Du dich gerne hier
melden.