VBA Jahr in Verknüpfung erkennen und aktualisieren

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 03.12.2015 01:10:22

Hallo Gemeinde!
Folgendes Problem:
Ich habe in einer Arbeitsmappe VERWEIS-Formeln mit Verknüpfungen zu einer anderen Excel-Datei, welche im Dateinamen das aktuelle Jahr besitzt.
Es existiert jeweils immer eine Datei des aktuellen Jahres im immer gleichen Pfad.
Um dies jedes Jahr komfortabel aktualisieren zu können, würde ich gerne eine Schaltfläche mit Makro einbauen, die in den Verknüpfungen nach der Jahresangabe sucht und entweder das aktuelle Jahr oder das gefundene +1 setzt.
Kann da jemand helfen?
Ein Teil des Verweises:
=VERWEIS(B8;'[KALENDER_2015.xlsx]Jan-Jun'!$C$3:$GA$3...
Hier sollte das Makro im Zellbereich (z.B.) E4:E34 die Zahl 2015 bzw.. 20xx erkennen (kommt in der Formel mehrfach vor, dies in jeder Zelle des Bereiches) und dann 2016 oder die aktuelle Jahreszahl eintragen.
Mit dem Rekorder habe ich es geschafft, ein Jahr bzw. die "5" in eine "6" ersetzen zu lassen, so müsste ich aber trotzdem jedes Jahr das Makro anpassen oder halt über die Standard-Funktion "Suchen/Ersetzen" arbeiten, was ich mir gerne sparen würde ;-) bzw. möchte ich anderen Personen die Datei zur Verfügung stellen, welche wiederum völlig Excel-Kenntnisfrei nur durch Betätigung einer Schaltfläche das Ganze auf das aktuelle Jahr stellen können sollen.
Hier der Code des Rekorders:

Sub Makro1()
    Range("E4").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(LOOKUP(RC[-3],'[KALENDER_2016.xlsx]JanJun'!R3C3:R3C183,'[KALENDER_2016.xlsx]
Jan-Jun'!R5C3:R5C183)>0,LOOKUP(RC[-3],'[KALENDER_2016.xlsx]JanJun'!R3C3:R3C183,
'[KALENDER_2016.xlsx]Jan-Jun'!R5C3:R5C183),"""")"
    Range("E4").Select
    Selection.AutoFill Destination:=Range("E4:E34"), Type:=xlFillDefault
    Range("E4:E34").Select
End Sub
Vielen Dank schon einmal,
Werner

Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: fcs
Geschrieben am: 03.12.2015 08:07:38
Hallo Werner,
das Ändern der Verknüpfung funktioniert einfacher via Menü Daten -- Verknüpfungen - im Dialog dann die Quelle wechseln.
Wenn nur eine Verknüpfung auf eine externe Datei vorhanden ist, dann kann man das auch via Makro realisieren. Mit etwas Analyse des Dateinamen kann man auch die Jahreszahl im Dateinamen um 1 erhöhen.
Gruß
Franz

Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 04.12.2015 00:59:38
Hallo..
Richtig, es gibt nur eine Verknüpfung. Ich bin jetzt soweit, dass mein Code die Verknüpfung ändert:

Sub Verknuepfung_aktualisieren()
    
    Sheets("Januar").Select
    Range("B2").Select
    ChDir "D:\KALENDER"
    ActiveWorkbook.ChangeLink Name:="D:\KALENDER_2015.xlsx", NewName _
        :="D:\KALENDER_2016", Type:=xlExcelLinks
End Sub

Nun habe ich aber noch zwei Probleme:
1.) Dass im Namen der Ausgangsdatei die Jahreszahl am besten durch Platzhalter ersetzt werden sollte und
2.) bei NewName die Jahreszahl durch die in Zelle B2 eingegebene Zahl (das jeweilige Jahr) eingetragen werden muss..
.. und da komme ich gerade nicht weiter, habe zwar schon etwas gesucht (einsetzen von
"D:\KALENDER_" &"Range("B2"), bei NewName), bekomme aber dann noch den Fehler "Erwartet: Anweisungsende" und "B2" wird markiert:
Sub Verknuepfung_aktualisieren()
    
    Sheets("Januar").Select
    Range("B2").Select
    ChDir "D:\KALENDER"
    ActiveWorkbook.ChangeLink Name:="D:\KALENDER_2016.xlsx", NewName _
        :="D:\KALENDER_" &"Range("B2"), Type:=xlExcelLinks
End Sub
Kann mich da jemand auf den richtigen Weg bringen?
Ciao
Werner

Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 04.12.2015 02:19:31
Ok, meinen Fehler habe ich wohl gefunden, das aktuelle Jahr lasse ich jetzt auch mal automatisch setzen.

Sub Verknuepfung_aktualisieren()
    
    Sheets("Januar").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = Year(Now)
    ActiveWorkbook.ChangeLink Name:="D:\KALENDER_2015.xlsx", NewName _
    :="D:\KALENDER_" & (Range("B2")), Type:=xlExcelLinks
End Sub
Bleibt jetzt nur noch das Problem, dass ich gerne beim Namen der Ausgangsverknüpfung Platzhalter für die Jahresangabe hätte (ActiveWorkbook.ChangeLink Name:="D:\KALENDER_****.xlsx",).
Hat jemand eine Lösung hierfür?
Gruß
Werner

Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: fcs
Geschrieben am: 04.12.2015 06:49:25
Hallo Werner,
mit einfach suchen und ersetzen geht es leider nicht.
Man muss da mit den VBA-Textfunktionen ein wenig herumjonglieren, um den neuen Dateinamen des Links zusammenzubauen.
Außerdem sind ein paar Prüfungen erforderlich, damit das Makro nicht in Fehlern abbricht.
Gruß
Franz

Sub Verknuepfung_aktualisieren()
    Dim strJahr As String
    Dim varLinks, strLinkAlt As String, strLinkNeu As String
    Dim wkb As Workbook
    Dim msgPrompt$, msgTitle$, msgButtons As Long
    
    Set wkb = ActiveWorkbook
    msgTitle = "Aktualisierung Verknüpfung in " & wkb.Name
    msgButtons = vbInformation + vbOKOnly
    
'alle Verknüpfungen zu Excel-Dateien einer Variablen zuweisen
    varLinks = wkb.LinkSources(Type:=xlExcelLinks)
    
'Verknüpfung prüfen
    If Not IsArray(varLinks) Then
        msgPrompt = "Die Arbeitsmappe enthält keine Verknüpfungen."
        MsgBox msgPrompt, msgButtons, msgTitle
    Else
        strLinkAlt = varLinks(1)
        If Not LCase(strLinkAlt) Like "*kalender_####.xlsx" Then
            msgPrompt = "Die 1. Verknüfung " & strLinkAlt & vbLf _
                & "enthält nicht den Ausdruck ""Kalender_####.xlsx"""
            MsgBox msgPrompt, msgButtons, msgTitle
        Else
'Jahr für Link aus Zelle auslesen
            strJahr = wkb.Sheets("Januar").Range("B2").Text
'neue Verknüpfung zusammenbauen
            strLinkNeu = VBA.Replace(strLinkAlt, _
                Find:=Right(strLinkAlt, 9), Replace:=strJahr & ".xlsx")
'Prüfung ob Datei existiert
            If Dir(strLinkNeu) = "" Then
                msgPrompt = "Die Datei für das Jahr " & strJahr & vbLf _
                    & strLinkNeu & vbLf _
                    & "existiert nicht." & vbLf _
                    & "Link-alt: " & strLinkAlt
                MsgBox msgPrompt, msgButtons, msgTitle
            Else
                If LCase(strLinkAlt) = LCase(strLinkNeu) Then
                    msgPrompt = "Die Verknüpfung ist schon aktuell." & vbLf _
                        & "Dateiname: " & strLinkAlt
                    MsgBox msgPrompt, msgButtons, msgTitle
                Else
'Verknüpfung ersetzen
                    wkb.ChangeLink Name:=strLinkAlt, _
                                   NewName:=strLinkNeu, Type:=xlExcelLinks
                End If
            End If
        End If
    End If
End Sub


Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 05.12.2015 15:39:16
Hallo Franz,
vielen Dank für Deine Arbeit, das sieht richtig gut aus! ich habe Deinen Code eingebaut und lasse dann gleich die aktuelle Datei abspeichern:

Sub Verknuepfung_aktualisieren_und_neue_Datei()
'
    Dim i As Integer
    Dim j As Integer
    Dim strJahr As String
    Dim varLinks, strLinkAlt As String, strLinkNeu As String
    Dim wkb As Workbook
    Dim msgPrompt$, msgTitle$, msgButtons As Long
    Set wkb = ActiveWorkbook
    For i = 1 To Sheets.Count
    ActiveWorkbook.Sheets(i).Unprotect Password:=""
    Next
        Sheets("Januar").Select
        Range("B2").Select
        ActiveCell.FormulaR1C1 = Year(Now) + 1
        msgTitle = "Aktualisierung Verknüpfung in " & wkb.Name
    msgButtons = vbInformation + vbOKOnly
    
'alle Verknüpfungen zu Excel-Dateien einer Variablen zuweisen
    varLinks = wkb.LinkSources(Type:=xlExcelLinks)
    
'Verknüpfung prüfen
    If Not IsArray(varLinks) Then
        msgPrompt = "Die Arbeitsmappe enthält keine Verknüpfungen."
        MsgBox msgPrompt, msgButtons, msgTitle
    Else
        strLinkAlt = varLinks(1)
        If Not LCase(strLinkAlt) Like "*kalender_####.xlsx" Then
            msgPrompt = "Die 1. Verknüfung " & strLinkAlt & vbLf _
                & "enthält nicht den Ausdruck ""Kalender_####.xlsx"""
            MsgBox msgPrompt, msgButtons, msgTitle
        Else
'Jahr für Link aus Zelle auslesen
            strJahr = wkb.Sheets("Januar").Range("B2").Text
'neue Verknüpfung zusammenbauen
            strLinkNeu = VBA.Replace(strLinkAlt, _
                Find:=Right(strLinkAlt, 9), Replace:=strJahr & ".xlsx")
'Prüfung ob Datei existiert
            If Dir(strLinkNeu) = "" Then
                msgPrompt = "Die Datei für das Jahr " & strJahr & vbLf _
                    & strLinkNeu & vbLf _
                    & "existiert nicht." & vbLf _
                    & "Link-alt: " & strLinkAlt
                MsgBox msgPrompt, msgButtons, msgTitle
            Else
                If LCase(strLinkAlt) = LCase(strLinkNeu) Then
                    msgPrompt = "Die Verknüpfung ist schon aktuell." & vbLf _
                        & "Dateiname: " & strLinkAlt
                    MsgBox msgPrompt, msgButtons, msgTitle
                Else
'Verknüpfung ersetzen
                    wkb.ChangeLink Name:=strLinkAlt, _
                                   NewName:=strLinkNeu, Type:=xlExcelLinks
                End If
            End If
        End If
    End If
        ActiveSheet.Shapes.Range(Array("Schaltfläche 1")).Select
        Selection.Delete
        ActiveWorkbook.SaveAs Filename:= _
        "D:\KALENDER\Zuschläge_" & (Range("B2")), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    For j = 1 To Sheets.Count
    ActiveWorkbook.Sheets(j).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,  _
Password:=""
    Next
ActiveWorkbook.Save
End Sub
Noch mal Dank an Dich, hat mir auch gleich noch einen bisschen besseren Einblick in VBA verschafft :-)
Gruß,
Werner

Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 09.12.2015 16:56:04

Hallo, ich schon wieder ;-)
Die o.g. Sachen funktionieren ja wirklich wunderbar, nun zusätzlich die Frage:
Wie würde man es realisieren, statt einer ZWEI Verknüpfungen zu aktualisieren?
Eine Verknüpfung ein Jahr vor, 2015 zu 2016 bzw. Zelle B2 = Year(Now) +1 (bereits oben gelöst),
eine weitere das vorhergehende Jahr einen vor (hier dann 2014 zu 2015)? (Dateiname beinhaltet "Stunden_xxxx.xlsm")
Ich habe schon ein bisschen experimentiert, u.a. habe ich versucht eine Kopie des oben stehenden Codes (Variablenzuweisungen angepasst) zu verwenden. Dabei bekomme ich allerdings die Prüfung der Verknüpfungen ("Die 1. [bzw. 2.] Verknüpfung enthält nicht den Ausdruck..") nicht separiert und dementsprechend die Fehlermeldung.
Kann mir da jemand weiterhelfen?
Dank im Voraus
Werner


Bild

Betrifft: AW: VBA Jahr in Verknüpfung erkennen und aktualisieren
von: Werner
Geschrieben am: 10.12.2015 17:23:50
Habe scheinbar vergessen den vorigen Eintrag als "offen" zu markieren

 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA Jahr in Verknüpfung erkennen und aktualisieren"