Microsoft Excel

Herbers Excel/VBA-Archiv

Sverweis auf Dateien mit wechslenden Dateinamen | Herbers Excel-Forum


Betrifft: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 04.01.2012 03:40:51

Hallo zusammen,

ich habe eine Datei (nennen wir mal Bericht) in welche täglich Daten eingetragen werden.
In drei andere Dateien (nennen wir mal Daten 1, 2, 3) werden ebenfalls täglich Daten eingetragen.
In der Datei Bericht nehme ich mittels Sverweis Bezug auf die Dateien Daten 1, 2, 3 allerdings nicht immer auf die gleiche, das wechselt.

Nun zu meinem Problem.

Die Dateien Daten 1, 2, 3 erhalten jedes Jahr einen neuen Dateinamen-Zusatz (die aktuelle Jahreszahl). Ebenfalls wird ein neues Verzeichnis angelegt in welchem die Dateien Daten 1, 2, 3 liegen, ebenfalls mit einem Jahreszusatz. Die Datei Bericht liegt in einem anderen Verzeichnis.

Die Sverweisfunktion sieht folgendermaßen aus:

SVERWEIS($U$1;'Y:\Bereich\Daten 2012\[Daten Bereich 3 2012.xls]Reihe 12'!$A$7:$M$370;13)

In 2011 sah die Formel so aus:

SVERWEIS($U$1;'Y:\Bereich\Daten 2011\[Daten Bereich 3 2011.xls]Reihe 12'!$A$7:$M$370;13)

An der Zellposition U1 befindet sich ein Datum.
Der Verzeichnis und Dateiname ändert sich jedes Jahr und daher muss ich alle Sverweise manuell ändern.
Ich hätte daher gerne die Möglichkeit das Jahr der Zelle U1 als Jahreszahl im Verzeichnis und Dateinamen einzubinden. Dann entfällt komplett die manuelle Änderung.

Mit INDIREKT hab ich das nicht hinbekommen. Geht wohl auch ohnehin nicht, da die Datiene Daten 1, 2, 3 nicht geöffnet werden sollen.

Wer kann mir hier helfen?
Im Archiv bin ich leider nicht fündig geworden.

Danke schon mal!

Gruß Heinz

  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Hajo_Zi
Geschrieben am: 04.01.2012 06:11:14

Hallo Heinz,

warum änderst Du nicht einmal die Quelle über Daten, Verbindung, Verknüpfung bearbeiten?

GrußformelHomepage


  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 05.01.2012 00:04:03

Hallo Hajo,

danke erst mal für deine Antwort.
Die Möglichkeit geht schon mal schneller als alle Verknüpfungen einzeln anpassen.
Allerdings wäre mir eine automatisierte Lösung sehr viel genehmer.
Falls ich mal nicht da sein sollte müssen die Dateien ja trotzdem funktionieren.

Hast du noch ne Idee wie das anders gelöst werden könnte.

Die Dateinamen und Verzeichnisse (von Daten 1, 2, 3) ändern ist leider keine Option da ich darauf keinen Einfluß habe.

Gruß Heinz


  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Josef Ehrensberger
Geschrieben am: 05.01.2012 00:49:54


Hallo Heinz,

teste mal diese Möglichkeit.

Der Code kommt in das Modul der Tabelle mit dem Jahr in U1.

' **********************************************************************
' Modul: Tabelle6 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

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






« Gruß Sepp »



  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 05.01.2012 01:03:01

Hallo Sepp,

danke für das Makro.
Hab das jetzt mal ins Modul kopiert.
Allerdings hab ich keinen Plan wie ich das jetzt in meine Formeln einbinde.

SVERWEIS($U$1;'Y:\Bereich\Daten 2012\[Daten Bereich 3 2012.xls]Reihe 12'!$A$7:$M$370;13)

Wo kommt denn was hin?

Kannst du mir da noch mal nen Tip geben?

Danke dir.

Gruß Heinz


  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Josef Ehrensberger
Geschrieben am: 05.01.2012 01:19:05


Hallo Heinz,

du musst gar nichts "einbinden", wenn du in U1 die Jahreszahl änderst, werden die Verknüpfungen angepasst.




« Gruß Sepp »



  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 05.01.2012 02:15:07

Hallo Sepp,

dann funzt das leider nicht.
Ich hab da zwar ne Formel in U1 drinne aber ich hab mal manuell das Datum eingegeben aber auch das hat leider nicht funktioniert. Weder als Jahreszahl noch als komplettes Datum.


Gruß Heinz


  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Josef Ehrensberger
Geschrieben am: 05.01.2012 02:19:49


Hallo Heinz,

von Formel in U1 war bisher noch nicht die Rede!

Bei manueller Eingabe der Jahreszahl in U1 läuft der Code, sofern du in auch wirklich in das richtige Tabellenmodul eingefügt hast.

Welche Formel steht den in U1?




« Gruß Sepp »



  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 05.01.2012 03:16:13

Hallo Sepp,

die Formel in U1 lautet wie folgt:

=JETZT()-0,26

Aber auch wenn ich manuell eine Jahreszahl dort eingebe läuft das Makro bei mir nicht.
Zu der verwendeten Formel ist zu sagen das ich anhand dieses Datums in den Datendateien suchen lasse.
Die "-0,26" wird benötigt damit der Datumssprung nicht zu früh erfolgt.

Ich hab das Makro jetzt mal Versuchsweise ins aktuelle Tabellenblatt sowie in "Diese Arbeitsmappe" und in Modul1 eingefügt. Aber ich hab bei allen Versuchen keinen Erfolg erkennen können.


Gruß Heinz


  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Josef Ehrensberger
Geschrieben am: 05.01.2012 10:13:26


Hallo Heinz,

anbei eine Beispieldatei mit neuem Code, der auf die Formel in U1 abgestimmt ist, teste mal.

https://www.herber.de/bbs/user/78222.xls


« Gruß Sepp »



  

Betrifft: AW: Sverweis auf Dateien mit wechslenden Dateinamen von: Heinz
Geschrieben am: 05.01.2012 23:23:58

Hallo Sepp,

hab gerade getestet. Funzt Super. Genau so wie ich mir das vorgestellt habe.
Ich kann mich nur bedanken.

Ihr "Profis" macht hier einen genialen Job.

Was wären wir "Anfänger, Laien etc" ohne euch.

Vielen Dank. Gruß Heinz


Beiträge aus den Excel-Beispielen zum Thema "Sverweis auf Dateien mit wechslenden Dateinamen"