Sverweis auf Dateien mit wechselnden Dateinamen
Schritt-für-Schritt-Anleitung
-
Öffne die Datei Bericht, in der du die Daten aus den Dateien Daten 1, 2 und 3 abrufen möchtest.
-
Gehe zu Zelle U1 und trage dort das Jahr manuell ein oder stelle sicher, dass die Formel =JETZT()-0,26 korrekt eingegeben ist.
-
Füge den VBA-Code in das Modul der Tabelle ein, die U1 enthält. Gehe dazu wie folgt vor:
- Drücke
ALT + F11, um den VBA-Editor zu öffnen.
- Füge den folgenden Code in das entsprechende Tabellenmodul ein:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vntLink As Variant
Dim lngIndex As Long
Dim strReplace As String, strNewLink As String
If Target.Address(0, 0) = "U1" Then
If IsNumeric(Target) Then
If Target >= 1900 And Target <= 3999 Then
strReplace = " " & CStr(Target)
vntLink = ThisWorkbook.LinkSources
If Not IsEmpty(vntLink) Then
For lngIndex = 1 To UBound(vntLink)
strNewLink = regReplace(vntLink(lngIndex), " [0-9]{4}", strReplace)
If strNewLink <> CStr(vntLink(lngIndex)) Then
ThisWorkbook.ChangeLink Name:=vntLink(lngIndex), _
NewName:=strNewLink, Type:=xlExcelLinks
End If
Next
End If
End If
End If
End If
End Sub
Private Function regReplace(ByVal Text As String, ByVal Pattern As String, ByVal Replacement As String, Optional IgnoreCase As Boolean = True) As String
Dim objRegExp As Object
Set objRegExp = CreateObject("Vbscript.regexp")
With objRegExp
.Pattern = Pattern
.IgnoreCase = IgnoreCase
.Global = True
regReplace = .Replace(Text, Replacement)
End With
Set objRegExp = Nothing
End Function
-
Teste die Funktion, indem du das Jahr in U1 änderst. Die Verknüpfungen zu den externen Dateien sollten nun automatisch angepasst werden.
Häufige Fehler und Lösungen
-
Fehler: Das Makro wird nicht ausgeführt.
- Lösung: Stelle sicher, dass der Code im richtigen Tabellenmodul eingefügt wurde. Der Code muss im Modul der Tabelle stehen, in der U1 ist.
-
Fehler: U1 enthält eine Formel und nicht die Jahreszahl.
- Lösung: Die Zelle U1 sollte eine manuelle Eingabe der Jahreszahl enthalten. Die Formel
=JETZT()-0,26 kann zu Problemen führen, da sie kein festes Jahr zurückgibt.
-
Fehler: Die Verknüpfungen werden nicht aktualisiert.
- Lösung: Überprüfe, ob die Zelle U1 tatsächlich geändert wird und ob die Verknüpfungen in den Daten 1, 2 und 3 korrekt definiert sind.
Alternative Methoden
Eine alternative Methode zur Automatisierung von Verknüpfungen könnte die Verwendung von Power Query sein. Diese Funktion erlaubt es dir, Daten aus verschiedenen Quellen zu importieren und dabei dynamische Parameter zu verwenden, um die Dateinamen anzupassen. Du kannst den Abfrage-Editor nutzen, um die aktuellen Verzeichnisse und Dateinamen festzulegen.
Praktische Beispiele
Angenommen, du hast die Jahreszahl 2023 in U1. Deine SVERWEIS-Formel könnte wie folgt aussehen:
=SVERWEIS($U$1;'Y:\Bereich\Daten 2023\[Daten Bereich 3 2023.xls]Reihe 12'!$A$7:$M$370;13)
Wenn du das Jahr in U1 änderst, wird die Verknüpfung automatisch aktualisiert, sodass du nicht mehr manuell die Dateien anpassen musst.
Tipps für Profis
-
Verwende definierte Namen: Definiere einen Namen für den Bereich, der die dynamische Verknüpfung enthält. So kannst du die Lesbarkeit deiner Formeln verbessern.
-
Regelmäßige Überprüfung: Überprüfe regelmäßig deine Makros und Formeln, um sicherzustellen, dass sie mit den neuesten Änderungen in der Datenstruktur kompatibel sind.
-
Backup: Mache regelmäßig Backups deiner Excel-Dateien, insbesondere wenn du mit VBA arbeitest, um Datenverluste zu vermeiden.
FAQ: Häufige Fragen
1. Kann ich auch andere Funktionen neben SVERWEIS verwenden?
Ja, du kannst auch andere Funktionen wie WVERWEIS oder INDEX/VERGLEICH verwenden, solange die Verknüpfungen korrekt angepasst werden.
2. Funktioniert das auch in Excel Online?
Die Verwendung von VBA ist in Excel Online nicht möglich. Für Online-Versionen empfehle ich, die Power Query-Funktionalität zu nutzen.
3. Wie kann ich sicherstellen, dass die Verknüpfungen immer aktuell sind?
Stelle sicher, dass die Zelle U1 immer die aktuelle Jahreszahl enthält und dass der VBA-Code im richtigen Modul platziert ist.