VBA für Excel Verknüpfungen aktualisieren?
Hallo alle zusammen,
ich bin am verzweifeln.. :-(
Ich möchte über VBA Verknüpfungen in einem aktiven Tabellenblatt ändern. Ich finde aber immer nur Antworten wie man Verknüpfungen der gesamten Arbeitsmappe ändert.
Die Verknüpfungen beziehen sich auf ein anderes (nicht aktives) Tabellenblatt in der selben Arbeitsmappe.
In der Zelle steht zum Beispiel =WENN(ISTFEHLER('04.2016'!$C8);"";'04.2016'!$C8) und VBA soll mir aus '04.2016' nun '06.2016' machen.
Bei meiner VBA Idee kommt immer der Fehler 438 (Objekt unterstützt diese Eigenschaft oder Methode nicht) und kennzeichnet Vardat = ActiveSheet.LinkSources(Type:=xlLinkTypeExcelLinks) an
Hier meine VBA Idee. Wer kann mir helfen? Dank Euch im Voraus.... :-)
Sub Verknüpfungenaktualisieren()
Dim Vardat As Variant Dim strNeuerLink As String
strNeuerLink = "'06.2016'" strAlterLink = "'04.2016'"
Vardat = ActiveSheet.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsEmpty(Vardat) Then
MsgBox "Keine Verknüpfungen enthalten!"
Exit Sub
End If
For intz = 1 To UBound(Vardat)
If Vardat(intz) = ActiveSheet.Path & "(" & strAlterLink Then ActiveSheet.ChangeLink ActiveSheet.Path & "(" & strAlterLink, _ ActiveSheet.Path & "(" & strNeuerLink, xlExcelLinks
Exit For
End If
Next intz
End Sub
2 Antworten
Meine Idee dazu wäre, dass du gar nicht die Formel änderst, sondern mit =INDIREKT() arbeitest. Du wirst ja wahrscheinlich eine Art Hauptseite haben, wo du halt immer die Daten aus den anderen Tabellenblättern drin haben willst, oder?
Mach doch eine Zelle, wo du einfach den Namen des Tabellenblatts reinschreiben kannst. Z.B. in A1 schreibst du dann "04.2016" rein. In A2 kannst du dann ein ausgeblendetes oder mit weißer Schrift formatiertes Feld machen, was dir den String zum Ziel zusammenbaut (also ="'" & A1 & "'!C8") und an der Stelle, wo die Daten dann drinstehen sollen nimmst du die Formel
=WENN(ISTFEHLER(INDIREKT(A2));"";INDIREKT(A2))
Wenn du stattdessen trotzdem lieber die Formeln an sich ändern willst, geht das z.B. damit:
Public Sub LinksAktualisieren(Von As String, Nach As String) Dim alleZellen As Range Dim aktuelleZelle As Range
Set alleZellen = ActiveSheet.Range("A:IV").SpecialCells(xlCellTypeFormulas) ' Auf dem aktuellen Tabellenblatt nach allen Zellen mit Formeln suchen
For Each aktuelleZelle In alleZellen If InStr(1, aktuelleZelle.Formula, Von) > 1 Then ' Bei jeder Zelle, die zu ändernde Formel ändern aktuelleZelle.Formula = Replace(aktuelleZelle.Formula, Von, Nach) End If Next aktuelleZelle
End Sub
Wenn ich das richtig sehe, ist das Datum doch der Name des Tabellenblatts, auf das du zugreifen willst, oder?
Dann ist das Datum ein Teil der Formel. Genau das kann man mit der Prozedur, die ich geschrieben habe, umändern.
Im Prinzip kannst du sie so nehmen, wie sie ist und einfach mit
LinksAktualisieren("04.2016", "06.2016")
aufrufen. Dadurch wird in den Formeln von allen Zellen des aktuellen Formulars der Teil mit "04.2016" ersetzt durch "06.2016". Und dadurch ändert sich natürlich auch die Verknüpfung vom Tabellenblatt '04.2016' auf das Tabellenblatt '06.2016', ohne dass die grundlegende Funktion der Formel verändert wird.
Ich habe mich jetzt mal etwas mit LinkSources
beschäftigt.
Ich denke, das funktioniert nur bei Verknüpfungen zu anderen Arbeitsmappen.
Schau mal ob Dir hiermit:
Sub Test()
Dim varDat As Variant
varDat = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not IsEmpty(varDat) Then
For i = 1 To UBound(varDat)
MsgBox "Link " & i & ":" & Chr(13) & varDat(i)
Next i
End If
End Sub
...deine Verknüpfungen überhaupt angezeigt werden.
DaCyphers Lösungsweg scheint mir eigentlich zu passen.
Leider ist es nicht ganz das, was ich brauche. Trotzdem vielen Dank für Deine prompte Antwort.
Die Formel muss so bleiben. VBA soll mir "nur" das Datum in allen Verknüpfungen in meinem aktiven Tabellenblatt von '04.2016' auf '06.2016' ändern.