Sverweis mit variablem Dateiname in Excel
Schritt-für-Schritt-Anleitung
-
Öffne Deine Excel-Datei und drücke ALT + F11, um den VBA-Editor zu öffnen.
-
Füge ein neues Modul hinzu: Klicke mit der rechten Maustaste auf „VBAProject (DeineDatei.xlsm)“, wähle „Einfügen“ und dann „Modul“.
-
Kopiere den folgenden VBA-Code in das Modul:
Sub SverweisMitVariablemDateiname()
Dim Datei As String
Dim Pfad As String
Dim FO As String
Pfad = "G:\u\v\w\x\y\z\"
Datei = InputBox("Gib den Dateinamen ein (z.B. 20180509):")
If Dir(Pfad & Datei & ".xlsm") = "" Then
MsgBox "Falscher Dateiname"
Else
FO = "=IFERROR(VLOOKUP(B2, '" & Pfad & "[" & Datei & ".xlsm]Tabelle1'!$A:$AI, 35, 0), """")"
Range("AE2").Formula = FO
End If
End Sub
-
Schließe den VBA-Editor und kehre zu Excel zurück.
-
Führe das Makro aus: Drücke ALT + F8, wähle „SverweisMitVariablemDateiname“ aus und klicke auf „Ausführen“.
Häufige Fehler und Lösungen
Alternative Methoden
Eine alternative Methode ist die Verwendung von Formeln direkt in Excel, wenn Du den Dateinamen manuell eingeben möchtest. Du kannst die INDIREKT-Funktion verwenden, um auf die Zelle in einer geöffneten Datei zuzugreifen:
=INDIREKT("'G:\u\v\w\x\y\z\[" & A1 & ".xlsm]Tabelle1'!A1")
Hierbei steht A1 für die Zelle, in der der Dateiname gespeichert ist.
Praktische Beispiele
Angenommen, Du hast eine Datei namens „20180509.xlsm“ im angegebenen Pfad. Du kannst den SVERWEIS in der Zelle AE2 wie folgt nutzen:
Range("AE2").Formula = "=IFERROR(VLOOKUP(B2, 'G:\u\v\w\x\y\z\[20180509.xlsm]Tabelle1'!$A:$AI, 35, 0), """")"
Diese Formel sucht in der Datei „20180509.xlsm“ nach dem Wert in B2 und gibt das Ergebnis zurück, wenn es gefunden wird.
Tipps für Profis
- Debugging: Wenn der Code nicht funktioniert, setze Haltepunkte im VBA-Editor und nutze die Funktion „Schritt für Schritt ausführen“, um den Fehler zu finden.
- Dummy-Daten: Verwende Dummy-Daten für Tests, um sicherzustellen, dass Deine Formeln korrekt sind, bevor Du sie in die Produktionsdatei einfügst.
- Formel-Optimierung: Wenn Du mit langen und komplexen Formeln arbeitest, ist es oft hilfreich, die Formel in mehrere Teile zu zerlegen und diese in separaten Variablen zu speichern.
FAQ: Häufige Fragen
1. Frage
Kann ich den Dateinamen auch ohne Makro ändern?
Antwort: Ja, Du kannst den Dateinamen manuell in die Formel eingeben, aber das ist nicht so flexibel wie die Verwendung eines Makros.
2. Frage
Warum funktioniert die INDIREKT-Funktion nicht mit geschlossenen Dateien?
Antwort: Die INDIREKT-Funktion benötigt eine offene Datei, um den Bezug korrekt herzustellen. Für geschlossene Dateien ist die Verwendung eines Makros erforderlich.