Office: (Office 2007) Pfad mit Makro /VBA zu verknüpften Excel-Dateien ändern

Hallo,
Vielleicht kann mir jemand bei folgendem Problem weiter helfen:
Und zwar habe ich eine Excel-Datei, die Ergebnisse aus anderen (50 oder mehr) Dateien zusammenfassen soll. Ich würde das gerne mittels Verknüpfung machen, was auch funktioniert.

Das Problem ist jetzt, dass das ganze jährlich wiederholt werden soll, also alle Quelldateien in Ordner 2014 kopieren, neue Daten befüllen, neue Ergebnisse in Zusammenfassung. Das Problem ist, dass Excl so weit ich weis nicht automatisch die Verknüpfung auf die neue Version ändert und man über hundert mal (mehrere Werte pro Datei) die Verknüpfung ändern müsste.

Nun würde ich gerne folgendes tun:
Jede Verknüpfung ist ja so aufgebaut:
C:/Projekt/2013/Auswertung[exceldatei.xlsx]Tabellenblatt!ZelleX usw.

Und es ändert sich ja immer nur der Pfad zur Datei. Gibt es die Möglichkeit mittels Hilfsbariablen den Pfad einfach ein mal am Beginn der Exceldatei zu definieren, auf die alle Verknüpfungen zugreifen?

Dachte etwa so:
Pfad:="c:/Projekt/2013/Auswertung";
Und diese Variable dann einfach in der jeweiligen Zelle eintragen, dann müsste man immer nur in der Variable "Pfad" aus 2013 2014 usw. Machen...


Wäre klasse wenn mir da jemand weiter helfen könnte...

:)

 

Hallo ???,

Oder gleich so: Code:

 

Hallo Hasso und danke für deine Antwort.

Das Jahr soll schon gerne manuell eingegeben werden, denn sonst ändert er ja auch ältere Dateien, die man in der Zukunft öffnet zwecks Einsicht.

Mein Problem ist, dass ich es noch nicht hinbekommen hab die Verknüpfung gestückelt anzulegen.

Beispiel:
In Zelle B1 steht der Pfad.
Und wird dann automatisch in alle Verknüpfungen eingetragen.

Ich weis jetzt, dass man mittels des & Texte zusammenfügen und mittels der Funktion INDIREKT sogar ohne Makro nutzen kann. Jedoch muss dafür die Zieldatei geöffnet sein!

Ist das bei Verknüpfungen immer so oder könnte ich das mit einem Makro irgendwie umgehen?

Danke schon mal.

Gruß
Andreas

 

Hallo,

Wenn der Bereich aus dem du die Werte beziehst kannst du die Daten auch aus der geschlossenen Arbeitsmappe kopieren. Es erfolgt dann aber keine Aktualisierung der Daten solange du sie nicht erneut mit dem Makro "holst".
Weiß aber nicht ob dir das bei deinem Problem hilft.

 

Hallo Lino87,
Hauptsache die Daten werden beim öffnen der Datei aktualisiert.

Sprich ich hab sehr viele Quelldateien und eine Datei, die das zusammenfasst.
Wenn jetzt die Daten jedes mal beim öffnen der Zusammenfassung aktualisiert würde würde mir das reichen.

 

Entscheidend ist nur ob sich die Daten an einem fest definierten sich nicht ändernden Ort in deiner Arbeitsmappe befinden.

 

Hallo Andreas,

angenommen in A1 deiner Übersichtstabelle trägst du das betreffende Jahr ein, dann müsste in der Zelle mit dem Link stehen Code:

 

Ich geb am besten noch ein paar Hintergrundinfos:
Ich bin Werkstudent und das, was ich da baue soll später in der Firma eingesetzt werden und weil ich nicht weis wo die das nachher speichern deshalb der ganze Pfad bis zur Excel Datei als Variable.

Ich gehe dann davon aus, dass die Datei an diesem Punkt bleibt. Sprich als grobes Beispiel:
c:/Projekte/ProjektXY/2013/Auswertung/Quellexceldatei(en).xlsx
c:/Projekte/ProjektXY/2014/Auswertung/Quellexceldatei(en).xlsx
... usw.

Hasso:
So habe ich es bereits versucht aber Excel bietet mir dann immer an manuell die Datei auszu wählen und wenn ich das nicht mache steht in der Zelle dann #BEZUG! und der Inhalt sieht so aus (die eckigen Klammern und die doppelten Angaben hat Excel automatisch gegen meinen Willen eingefügt):

Code:

Ich habe hier versucht den ganzen Pfad als Variable an zu geben und bin sonst so vorgegangen wie in deinem Beispiel...

 

Hallo nameloser,

mit Formellösungen wirst Du nicht glücklich werden (dafür bedürfte es der Funktion Indirekt, die aber nicht mit geschlossenen Mappen arbeitet!) und in den Formeln direkt "rumbasteln" würde ich auch nicht.

Man kann die Verküpfungsziele über LinkSources der Arbeitsmappe ansprechen und mittels ChangeLink ändern:

Code:

Anstelle nur der Jahreszahl kannst Du größere fixe Abschnitte des Pfades in die Änderung einbeziehen - das musst Du anhand der tatsächlichen Randbedingungen abschätzen, was sicherer und sinnvoller ist.

[Edit]
Oopsi, das war der falsche Code *wink.gif* Der richtige steht nun im Codeabschnitt.
[/Edit]

Grüße
EarlFred

 

Hallo EarlFred,
danke für deine Antwort.

Ich hab deinen Code mal getestet (die Version, die du vor meiner jetztigen Antwort gepostet hast *grins ) und für den Fall, dass sich nur das Jahr ändert ist das eine einfache Lösung, die ich begreife.

Jedoch weis ich wie gesagt nicht wo die Dateien nachher gespeichert werden also würde ich gerne in einer Zelle oben den Pfad, in dem sich die Quelldateien befinden nehmen und die Verknüpfungen wie mit deinem Code ändern.

Ich bin nur gerade etwas aus VBA raus und muss erstmal gucken wie ich da eine Zelle auslese (habe ich gerade gemacht, mit Zellen in VBA arbeiten bekomme ich hin). *grins

Wenn ich das dann hab muss die Verknüpfung aktuallisert werden und da bin ich unsicher wie ich das mache und Excel damit dann auch arbeitet weil ja der Pfad bei allen Dateien gleich ist aber sich Dateinamen und die abgefragten Zellen variieren...

Ich hab dazu mal eine kleines Beispiel als Excel Datei angehängt.

Wäre klasse wenn ich einen Platzhalter vergeben könnte, der je nach dem was man in der oberen Zelle einträgt die Verknüpfung ändert (wie mit deinem Code, nur mit dem Pfad)...

Danke schon mal.

Gruß
Andreas

 

Hallo Andreas,

die ChangeLink-Methode löst eine Neuberechnung aus. Nach meinen Erfahrungen verläuft das Aktualisieren der Daten also problemlos. Solltest Du andere Erfahrungen haben, teile sie bitte mit (vor allem unter welchen Randbedingungen das passiert).

Der neue Pfad wird in diesem Codeteil ermittelt:
Replace(l, lngAltJahr, lngNeuJahr)
"Ersetze lngAltJahr im Pfad l durch lngNeuJahr"

Eine Zelle kannst Du so z. B. auslesen:
Code:

Verstehe ich nicht.
Ist das ein Widerspruch zur eingangs klar formulierten Randbedingung:

Ich habe leider wenig Zeit, mich in das komplette Problem einzuarbeiten und mir die tatsächlichen Randbedingungen anzulesen oder zu erfragen. Wie Du die Verknüpfungen generell ändern kannst, weißt Du nun - und das war ja auch die Frage.

Grüße
EarlFred

 

Ja mit dem Pfad hatte ich mich etwas ungünstig ausgedrückt weil ich das Ziel hatte den Pfad zu ändern und den Rest der Verknüpfung bestehen zu lassen.

Die Quelldateien liegen im selben Verzeichnis:
c:/Projekte/ProjektXY/2013/Auswertung/
(ist nur ein Beispiel weil ich wie gesagt nicht weis wo das nachher gespeichert werden soll)

In diesem liegen die Dateien, die nach dem jeweiligen Ortsnamen beschriftet sind:
Ort1
Ort2
Ort3 usw.

Die Verknüpfungen, die ich bisher manuell gemacht hab sehen dann eben so aus:
'c:/Projekte/ProjektXY/2013/Auswertung/[Quelldatei1.xlsx]Blatt1'!B2
'c:/Projekte/ProjektXY/2013/Auswertung/[Quelldatei2.xlsx]Blatt1'!B2

Rotmarkiert hab ich das, was manuell eingegeben wird und Jahr für Jahr gleich bleibt (nur die Werte, die die Exceldateien in den Jahren haben ändern sich)

Ich müsste das blau markierte durch eine Variable ersetzten, die vom Makro geändert werden soll...

Danke trotzdem schon mal vielen dank für deine Hilfe ich werde mich damit beschäftigen und hoffentlich auch so hin bekommen.

Ach ja Zellen lese ich jetzt einfach mit dem Befehl Cells(x,y) aus.

Gruß
Andreas

 

Hallo Andreas,

also muss doch NUR der Pfadname geändert werden, wie eingangs formuliert?
Das tut der Code doch schon.

Du musst doch nur noch anstelle lngAltJahr und lngNeuJahr die ALTEN und NEUEN Pfadteile eintragen und kannst diese Werte natürlich auch durch Zellbezüge auslesen.
Also zum Beispiel anstelle Replace(l, lngAltJahr, lngNeuJahr)
Replace(l, "C:\Projekte\ProjektXY\2013\Auswertung\", Thisworkbook.Worksheets("Name").cells(123,456))
(nicht wundern: Bei mir segeln die Slashs mit Wind von Steuerbord)

Cells() geht natürlich auch, aber bitte nicht "einfach": Der Verweis muss (sollte dringend) genauso komplett aufgebaut sein wie in meinem Beispiel mit Range().

Grüße
EarlFred

 

Hallo EarlFred,
dass das so funktioniert hab ich schon verstanden, jedoch funktioniert das Makro dann ja nur einmalig, sprich ich muss eine Datei als Vorlage speichern.

Denn wenn der Pfad zu dem neuen Pfad geändert wurde könnte man den Pfad ja nicht noch mal ändern (wenn aus welchen Gründen auch immer die Dateien mal verschoben werden)...

Aber mein Grundproblem ist gelöst und dafür danke ich dir nochmal herzlich *Smilie

 

Hallo,

natürlich kann man den Pfad wieder ändern. Du musst den neuen nur kennen / feststellen / mitteilen...

Grüße
EarlFred