Pfad in Formel benutzen

Forumthread: Pfad in Formel benutzen

20.07.2005 13:01:25

Klaus

Hallo,
Habe in einem Arbeitsplatt Formeln (SVerweis) die auf Daten aus Dateien in einem anderen Ordner verweisen. Das ist alles soweit kein Problem.
Allerdings muss ich dieses Dateipaket regelmäßig anderen Nutzern zur Verfügung stellen. Die einzelnen Unterdateien als Tabellenblätter in der Hauptdatei zu verwenden scheitert inzwischen an der Dateigröße der Hauptdatei (waren inzwischen über 60MB, beim Versuch weitere Daten zu integrieren, meldete Excel, ich solle andere Programme schließen usw. obwohl keine geöffnet waren, ist eben wohl zu groß gewesen). Ich habe daher wie gesagt, die ganzen Datendateien in einen separaten Ordner gelegt. In der Hauptdatei liegt im SVERWEIS dieser Pfad.
Wenn ich nun aber das Paket weitergeben, stimmen natürlich die Pfadangaben auf dem anderen Rechner nicht mehr überein. Ich habe nun einen kleinen Makro erstellt, der beim Öffnen der Hauptdatei den aktuellen Pfad ausliesst und dann als Text in eine Zelle einfügt. Es gelingt mir aber nicht, diesen "Pfadtext" dann als Teil des SVERWEISES in die entsprechenden Formeln zu integrieren. Geht das überhaupt oder gibt es eine Lösung dafür?
Danke für Eure Hilfe.
Klaus

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Pfad in Formel benutzen

20.07.2005 14:17:36

MichaV
AW: Pfad in Formel benutzen

20.07.2005 17:55:21

Klaus

AW: Pfad in Formel benutzen

20.07.2005 14:17:36

MichaV

Hi,
ActiveWorkbook.ChangeLink Name:="E:\privat\herber.xls", NewName:="E:\privat\tmp\tmp.xls", Type:=xlExcelLinks
wandelt alle Bezüge auf herber.xls um in Bezüge auf tmp.xls.
Der neue Name muss aber vorhanen sein, sonst gibt es eine Abfrage von Excel, die nur durch Makro- Beenden abgebrochen werden kann.
Gruss- Micha

AW: Pfad in Formel benutzen

20.07.2005 17:55:21

Klaus

Hallo Micha,
vielen Dank für Deinen Tip. Geht so aber leider nicht. Wenn ich es richtig sehr, verlangt Excel mit dieser Routine die Eingabe des vollständigen Pfades einschließlich Workbook und Sheet. Das ist aber genau das, was ich nicht möchte, kann ich ja gleich alles von Hand ändern. Ich kopiere das Gesamte Verzeichnis inkl. dem Unterverzeichnis mit den Daten auf den anderen Rechner. Bei mir liegt in SVerweis meinetwegen :"T:\Excel\VMA\Datei1.xls[Blatt1]a1:z100;20; oder so ähnlich (auf Klammern, Hochkomma oder Ausrufezeichen habe ich jetzt nicht genau geachtet!). Auf dem anderen Rechner liegt der Kram jetzt beispieslweise auf C:\VMA\...Rest identisch. Ich will also nur den vorderen Teil ändern (müssen), da sich die Datei- und Blattnamen nicht geändert haben. Das funktioniert aber mit Deiner Lösung nicht so. Allerdings habe ich festgestellt....
Excel macht das von alleine. Habe probeweise mit Deinem Makro die Daten auf mein Notebook gespiel (im Netzwerk). Der Makro stopt mit Fehlerhinweis aber die Daten haben gepasst. Habe mir die Verweise angesehen, wurden korrekt geändert. (Hätte ich ja vorher mal nachsehen können. Trotzdem Danke. Generell wäre interessant eine Routine zu haben, die vollkommen ohne feste Zeilenangaben, Spaltenangaben oder Pfadangaben auskommen könnte. Also genauer gesagt, bei Öffnen einer Datei stell die erstmal mittels Makro fest, wo sie überhaupt liegt und legt diesen Pfad dann irgendwo in der Datei ab (in einer Zelle in einem Blatt (nicht nur als Wert einer Variablen für VBA). Dann müssten in Formeln, die sich auf Zeilenwerte oder Spaltenwerte beziehen, nicht die feste Koordinate enthalten sein, sondern quasi Spalte aktuelle Zelle und Zeile aktuelle Zelle. Dies soll bewirken, dass ich die Formel quasi x-beliebig verschieben kann und die rechnet trotzdem immer richtig. Wenn ich beispielsweise so eine SVERWEIS Formel nach unten weiterkopiere, dann verändert sich der Auswahlbereich entsprechende der Anzahl verschobener Zeilen, obwohl der doch immer gleich bleibt, ebenso die Zielspalte. Muss dann alles wieder manuell anpassen. Wenn man anstelle von beispielsweise A1:Z100 nur dort eine Variable oder einen Namen hinterlegen könnte (geht das eventuell sogar?), dann wäre das herunterkopieren kein Thema, würde immer Stimmen. So was in der Art schwebt mir vor. Werde ich noch ne Weile dran tüfteln (wenn ich dazu komme...)
Nochmals Danke.
Klaus

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Aktuellen Pfad ermitteln: Um den Pfad der Arbeitsmappe zu ermitteln, kannst Du die folgende VBA-Funktion verwenden:
```
Function GetWorkbookPath() As String
   GetWorkbookPath = ThisWorkbook.Path
End Function
```
Füge diese Funktion in ein neues Modul in Deinem VBA-Editor ein.
Pfad in eine Zelle schreiben: Schreibe den ermittelten Pfad in eine Zelle, z.B. in A1. Du kannst dies beim Öffnen der Datei automatisieren:
```
Private Sub Workbook_Open()
   Range("A1").Value = GetWorkbookPath()
End Sub
```
Verwendung des Pfades in einer Formel: Um den Pfad in einer SVERWEIS-Formel zu verwenden, kannst Du die Zelle mit dem Pfad referenzieren. Angenommen, der Pfad steht in A1, dann könnte Deine Formel so aussehen:
```
=SVERWEIS(B2;INDIREKT("'" & A1 & "\Datei1.xlsx'[Blatt1]'!A1:Z100");2;FALSCH)
```

Häufige Fehler und Lösungen

Excel hat ein Problem bei mindestens einem Formelbezug festgestellt: Dieser Fehler kann auftreten, wenn der Pfad ungültig ist oder die Datei nicht vorhanden ist. Stelle sicher, dass der Pfad korrekt in der Zelle steht und die Datei existiert.
Formel zeigt falsche Ergebnisse: Überprüfe, ob der Pfad korrekt in die Formel integriert ist. Achte darauf, dass du die richtigen Anführungszeichen und Klammern verwendest.
Änderung des Pfades nicht übernommen: Wenn Du den Pfad manuell in der Formel änderst, stelle sicher, dass Du den gesamten Pfad korrekt eingibst, einschließlich der Dateinamen und der Blätter.

Alternative Methoden

Excel-Name Manager: Du kannst den Namen Manager verwenden, um einen Namen für den Pfad zu definieren. Beispielsweise kannst Du einen Namen mit dem aktuellen Pfad erstellen und dann in Deinen Formeln darauf verweisen.
Makros zur Automatisierung: Erstelle ein Makro, das beim Öffnen der Datei den Pfad automatisch aktualisiert und in die Formeln einfügt. Dies kann besonders nützlich sein, wenn Du regelmäßig mit verschiedenen Dateien arbeitest.

Praktische Beispiele

Pfad aus Zelle in Formel verwenden: Angenommen, der Pfad steht in A1 und die Datei heißt "Daten.xlsx". Du kannst die Formel wie folgt gestalten:
```
=SVERWEIS(B2;INDIREKT("'" & A1 & "\Daten.xlsx'[Tabelle1]'!A1:B100");2;FALSCH)
```
Pfad anzeigen: Um den Pfad in einer Zelle anzuzeigen, kannst Du einfach die oben genannte Funktion verwenden und das Ergebnis in einer Zelle ausgeben lassen.

Tipps für Profis

Nutze Benannte Bereiche für die Daten, um die Handhabung der Formeln zu erleichtern. Dies macht es einfacher, die Formeln zu verstehen und zu verwalten.
Halte die Verzeichnisstruktur konsistent, wenn Du Dateien zwischen verschiedenen Rechnern austauschst. So kannst Du sicherstellen, dass die Pfade korrekt bleiben.
Experimentiere mit INDIREKT-Funktionen, um dynamische Bereiche zu erstellen, die sich je nach Bedarf anpassen.

FAQ: Häufige Fragen

1. Wie kann ich den aktuellen Pfad einer Datei anzeigen? Du kannst den aktuellen Pfad mit der Funktion =ZELLE("Pfad") in eine Zelle einfügen, um den Pfad der aktiven Datei anzuzeigen.

2. Was mache ich, wenn Excel die Pfade nicht aktualisiert? Stelle sicher, dass Du die Pfade korrekt in der Zelle referenzierst und dass die Datei existiert. Überprüfe auch Deine Formeln auf Fehler.

3. Wie kann ich den Pfad dynamisch in Formeln verwenden? Verwende die INDIREKT-Funktion in Kombination mit einer Zelle, die den Pfad enthält, um den Pfad dynamisch in Deinen Formeln zu integrieren.

4. Gibt es eine Möglichkeit, die Dateipfade in Excel zu automatisieren? Ja, Du kannst VBA verwenden, um beim Öffnen einer Datei den Pfad automatisch zu ermitteln und in die entsprechenden Zellen einzufügen.

Beliebteste Forumthreads (12 Monate)