Thema: Verknüpfungen auf anderes Tabellenblatt per VBA

Hallo zusammen,
ich habe in einer Arbeitsmappe in einem Tabellenblatt WS1.TabA eine Verknüpfung zu Daten in einer anderen Arbeitsmappe WS2.TabB.
Die Reiter in WS2 werden für jeden Arbeitstag fortgeschrieben (z.B. 4.5., 5.5., 6.5. usw.) und für jeden Monat wird eine neue Arbeitsmappe angelegt (z.B. WS2 05.15, WS2 06.15 usw.)
Ich möchte mit einem Makro die Verknüpfung auf das Tabellenblatt ändern.
Wenn ich ein Makro aufzeichne und die Datenverknüpfung ändere erhalte ich folgenden Code, mit _ Bezug auf die Arbeitsmappe aber ohne Bezug auf das Tabellenblatt:

Sub quelle()
ActiveWorkbook.ChangeLink Name:= _
"Pfad\WS2 03.15.XLS" _
, NewName:= _
"Pfad\2015\WS2 04.15.XLS" _
, Type:=xlExcelLinks
End Sub

Wie bekomme ich da den Bezug auf das Tabellenblatt hin?
Da jeden Tag ein neuer Reiter hinzukommt, müsste ich auch eine dynamische Lösung dafür haben _ und nicht mit fixen Links arbeiten. Ich hatte mir da eine Behilfslösung überlegt, indem ich die Links in Zellen zwischenspeicher:

Sub quelle2()
' quelle2 Makro
Dim quellealt As String
Dim quelleneu As String
Dim quellealt2 As String
Dim quelleneu2 As String
quellealt = Range("U17").Value
quelleneu = Range("U18").Value
ActiveWorkbook.ChangeLink Name:=quellealt, NewName:=quelleneu, Type:=xlExcelLinks
quellealt2 = quelleneu
quelleneu2 = Application.GetOpenFilename()
ActiveWorkbook.ChangeLink Name:=quellealt2, NewName:=quelleneu2, Type:=xlExcelLinks
Range("U17").Value = quellealt2
End Sub

Ich habe auch versucht die Zellenbezüge mit "Suchen und Ersetzen" zu editieren, werde dann aber gebeten bei jeder einzelnen Linkänderung den Pfad neu anzugeben.
Hat jemand eine Lösung hierfür? Ich habe schon etliche Foreneinträge durchgewühlt und hoffe auf eure Hilfe.
Danke!

HI
du kannst die Formeln auf dem Tabellenblatt auch mit der Replace-Funktion (Ersetzen) bearbeiten:
Cells.Replace "[DateinameAlt.xls]TabellenblattAlt'!", "[DateinameNeu.xls]TabellenblattNeu'!", xlpart
Gruß Daniel

Hallo Daniel,
danke für deine schnelle Antwort - ich habe deinen Vorschlag sofort umgesetzt:

Sub replace()
Dim Linkalt As String
Dim Linkneu As String
Linkalt = Range("s10").Value
Linkneu = Range("s9").Value
Cells.replace Linkalt, Linkneu, xlPart
Range("s10").Value = Linkneu
End Sub

Wenn ich das Makro so ablaufen lasse, öffnet sich für jeden abgeänderten Link ein Fenster und ich muss die Zielarbeitsmappe (in deinem Beispiel [Dateiname.xls]) auswählen und bestätigen. Funktioniert so noch nicht...
Viele Grüße,
Volker

Hi
das Fenster öffnet sich, wenn der Bezug nicht vorhanden ist.
Dh Dateiname und Tabellenblattname müssen stimmen und entsprechend der Schreibregeln für Dateinamen in Formeln richtig geschrieben sein.
(Dateinname wird in [..] gesetzt, ggf Hochkomma zu Beginn und vor dem !)
das kann ich jetzt natürlich nicht beurteilen, weil ich deine Datei und deinen Code nicht kenne.
Gruß Daniel

Hi Daniel,
das hört sich alles logisch an. Ich habe eine funktionierende Verknüpfung mit folgendem Pfad:
...\2015\[01.15 WPA.XLS]05.01.'!I4
mit der replace-Funktion ersetze ich nun lediglich den Bezug zum Tabellenblatt: "05." ersetzen mit "06.". Das Tabellenblatt "06.01." ist in der Arbeitsmappe angelegt und es sollte funktionieren - jedoch öffnet sich das Fenster zum Bestätigen des Pfades trotzdem bei jeder geänderten Verknüpfung...
Viele Grüße,
Volker

Hi
wie sieht deine Formel aus?
wie heissen deine Dateien?
kann es sein, dass der Text "05." noch an anderer Stelle in der Formel vorkommt, wo er nicht durch "06." ersetzt werden darf?
Wenn du sicher gehen willst, nur den Blattnamen zu bearbeiten, nimmst du den vollständigen Blattnamen incl der angrenzden Zeichen und ersetzt ]05.01.'! durch ]06.01.'!
Gruß Daniel

Hallo zusammen,
ich habe in einer Arbeitsmappe in einem Tabellenblatt WS1.TabA eine Verknüpfung zu Daten in einer anderen Arbeitsmappe WS2.TabB.
Die Reiter in WS2 werden für jeden Arbeitstag fortgeschrieben (z.B. 4.5., 5.5., 6.5. usw.) und für jeden Monat wird eine neue Arbeitsmappe angelegt (z.B. WS2 05.15, WS2 06.15 usw.)
Ich möchte mit einem Makro die Verknüpfung auf das Tabellenblatt ändern.
Wenn ich ein Makro aufzeichne und die Datenverknüpfung ändere erhalte ich folgenden Code, mit _ Bezug auf die Arbeitsmappe aber ohne Bezug auf das Tabellenblatt:

Sub quelle()
ActiveWorkbook.ChangeLink Name:= _
"Pfad\WS2 03.15.XLS" _
, NewName:= _
"Pfad\2015\WS2 04.15.XLS" _
, Type:=xlExcelLinks
End Sub

Wie bekomme ich da den Bezug auf das Tabellenblatt hin?
Da jeden Tag ein neuer Reiter hinzukommt, müsste ich auch eine dynamische Lösung dafür haben _ und nicht mit fixen Links arbeiten. Ich hatte mir da eine Behilfslösung überlegt, indem ich die Links in Zellen zwischenspeicher:

Sub quelle2()
' quelle2 Makro
Dim quellealt As String
Dim quelleneu As String
Dim quellealt2 As String
Dim quelleneu2 As String
quellealt = Range("U17").Value
quelleneu = Range("U18").Value
ActiveWorkbook.ChangeLink Name:=quellealt, NewName:=quelleneu, Type:=xlExcelLinks
quellealt2 = quelleneu
quelleneu2 = Application.GetOpenFilename()
ActiveWorkbook.ChangeLink Name:=quellealt2, NewName:=quelleneu2, Type:=xlExcelLinks
Range("U17").Value = quellealt2
End Sub

Ich habe auch versucht die Zellenbezüge mit "Suchen und Ersetzen" zu editieren, werde dann aber gebeten bei jeder einzelnen Linkänderung den Pfad neu anzugeben.
Hat jemand eine Lösung hierfür? Ich habe schon etliche Foreneinträge durchgewühlt und hoffe auf eure Hilfe.
Danke!

HI
du kannst die Formeln auf dem Tabellenblatt auch mit der Replace-Funktion (Ersetzen) bearbeiten:
Cells.Replace "[DateinameAlt.xls]TabellenblattAlt'!", "[DateinameNeu.xls]TabellenblattNeu'!", xlpart
Gruß Daniel

Hallo Daniel,
danke für deine schnelle Antwort - ich habe deinen Vorschlag sofort umgesetzt:

Sub replace()
Dim Linkalt As String
Dim Linkneu As String
Linkalt = Range("s10").Value
Linkneu = Range("s9").Value
Cells.replace Linkalt, Linkneu, xlPart
Range("s10").Value = Linkneu
End Sub

Wenn ich das Makro so ablaufen lasse, öffnet sich für jeden abgeänderten Link ein Fenster und ich muss die Zielarbeitsmappe (in deinem Beispiel [Dateiname.xls]) auswählen und bestätigen. Funktioniert so noch nicht...
Viele Grüße,
Volker

Hi
das Fenster öffnet sich, wenn der Bezug nicht vorhanden ist.
Dh Dateiname und Tabellenblattname müssen stimmen und entsprechend der Schreibregeln für Dateinamen in Formeln richtig geschrieben sein.
(Dateinname wird in [..] gesetzt, ggf Hochkomma zu Beginn und vor dem !)
das kann ich jetzt natürlich nicht beurteilen, weil ich deine Datei und deinen Code nicht kenne.
Gruß Daniel

Hi Daniel,
das hört sich alles logisch an. Ich habe eine funktionierende Verknüpfung mit folgendem Pfad:
...\2015\[01.15 WPA.XLS]05.01.'!I4
mit der replace-Funktion ersetze ich nun lediglich den Bezug zum Tabellenblatt: "05." ersetzen mit "06.". Das Tabellenblatt "06.01." ist in der Arbeitsmappe angelegt und es sollte funktionieren - jedoch öffnet sich das Fenster zum Bestätigen des Pfades trotzdem bei jeder geänderten Verknüpfung...
Viele Grüße,
Volker

Hi
wie sieht deine Formel aus?
wie heissen deine Dateien?
kann es sein, dass der Text "05." noch an anderer Stelle in der Formel vorkommt, wo er nicht durch "06." ersetzt werden darf?
Wenn du sicher gehen willst, nur den Blattnamen zu bearbeiten, nimmst du den vollständigen Blattnamen incl der angrenzden Zeichen und ersetzt ]05.01.'! durch ]06.01.'!
Gruß Daniel