Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1232to1236
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

externer Bezug mit Variablen

externer Bezug mit Variablen
Wolfango
Hallo,
ich habe eine Tabelle mit ganz vielen externen Bezügen (zu anderen Excel-Dateien).
Angenommen ein solcher Bezug sieht wie folgt aus:
='z:\aaa\bbb\2005\[ccc.xlsx]Tabelle1'!$D$7
Wenn ich nun den externen Bezug auf ein anderes Jahr abändern möchte sehe ich dazu zwei Möglichkeiten:
1. Suchen-Ersetzen (z.b. suchen nach "2005\" Ersetzen durch "2006\")
2. Daten - Verknüpfungen bearbeiten - Quelle ändern
Beides funktioniert, ist aber aufwendig und fehleranfällig wenn ich häufig wechseln möchte.
Gibt es eine Möglichkeit, in den oben genannten Bezug einen Verweis auf eine andere Zelle (z.B. A1)einzubauen, in welche ich dann nach Belieben eine andere Jahreszahl einsetzen kann?
Also, sinngemäß etwa so:
='z:\aaa\bbb\A1\[ccc.xlsx]Tabelle1'!$D$7
(ich weiß, dass die obige Schreibweise nicht funktionieren kann...sie dient nur dazu mein Anliegen zu verdeutlichen)
Jemand eine Idee?
Danke und Gruß,
Wo.

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
mit INDIREKT
07.10.2011 10:48:10
WF
Hi,
im Prinzip funktioniert das mit INDIREKT.
"im Prinzip" deshalb, da die betr. Datei geöffnet sein muss.
Da Du aber wechselnde Verzeichnisse ansprechen willst, ist das sicher nicht der Fall.
Salut WF
evtl. eine VBA Lösung?
07.10.2011 11:57:01
Tino
Hallo,
würdest Du auch eine VBA Lösung verwenden?
Gruß Tino
AW: evtl. eine VBA Lösung?
07.10.2011 14:41:47
Wolfango
...evtl käme auch eine VBA-Lösung in Betracht......gibt es denn da eine?
AW: evtl. eine VBA Lösung?
07.10.2011 15:54:27
Daniel
HI
natürlich.
du kannst alle aktionen, die du von Hand ausführst, auch per VBA ausführen lassen.
beispielsweise das Suchen-Ersetzen oder auch das ändern der Verknüpfung über den Menüpunkt "Verknüpfungen".
um rauszufinden wie das geht, kannst du die Aktionen von Hand ausführen und zeichnest das mit dem Recorder auf.
mit den Recorder aufgezeichneter Code bedarf zwar meistens noch einer Überarbeitung, aber zumindest siehst du mal, wie die Befehle heißen, welche Parameter sie haben und du kannst mit Hilfe der F1-Taste vom aufgezeichneten Code schnell die Hilfeseite des markierten Befehls wechseln, um dir dort die notwendigen Informationen zu dem Befehl zu holen.
Gruß, Daniel
Anzeige
hier eine VBA Variante
07.10.2011 16:44:41
Tino
Hallo,
kannst mal diesen Code testen.
Der Aufruf erfolgt über Makro1, diese kannst Du einem Button zuweisen.
Das Makro Bearbeite_Verknuepfung wird mit folgenden Parametern aus dieser Sub aufgerufen.
1. Parameter wo das neue Jahr steht.
2. Parameter in welchen Bereich gesucht werden soll.
Die Tabelle bei With Sheets("Tabelle1"), müsstest Du natürlich auch anpassen.
kommt als Code in Modul

Option Explicit 
Sub Makro1() 
'Tabelle anpassen 
With Sheets("Tabelle1") 
    '1. Parameter Zelle Jahr 
    '2. Parameter Bereich der durchsucht und ersetzt werden soll 
    Bearbeite_Verknuepfung .Range("A1"), .Range("A2:A500") 
End With 
End Sub 
 
Sub Bearbeite_Verknuepfung(rngValue As Range, rngBereich As Range) 
Dim ArrayData, n&, nn&, nCount& 
Dim strJahr$ 
 
strJahr = rngValue.Value 
 
'Eingabe überprüfen 
If Not strJahr Like "[2][0][0-5][0-9]" Then 
    MsgBox "Geben Sie in " & rngValue.Address(0, 0) & " eine Jahreszahl von 2000 bis 2059 an", vbQuestion 
    Exit Sub 
End If 
 
'Bereich der durchsucht werden soll, 
With rngBereich 
    ArrayData = .FormulaR1C1 
    strJahr = "\" & strJahr & "\" 
    If Not IsArray(ArrayData) Then 
        ArrayData = .Resize(, 2).FormulaR1C1 
        Redim Preserve ArrayData(1 To 1, 1 To 1) 
    End If 
 
    With CreateObject("Vbscript.Regexp") 
      .MultiLine = True 
      .Pattern = "\\\d{4,4}\\" 
      .Global = True 
         
        For n = 1 To Ubound(ArrayData) 
            For nn = 1 To Ubound(ArrayData, 2) 
                If .test(ArrayData(n, nn)) Then 
                    If .Execute(ArrayData(n, nn))(0) <> strJahr Then 
                        ArrayData(n, nn) = .Replace(ArrayData(n, nn), strJahr) 
                        nCount = nCount + 1 
                    End If 
                End If 
            Next nn 
        Next n 
    End With 
     
    If nCount > 0 Then 
        Application.EnableEvents = False 
        Application.DisplayAlerts = False 
             
            .FormulaR1C1 = ArrayData 
         
        Application.DisplayAlerts = True 
        Application.EnableEvents = True 
         
        MsgBox "Es wurde(n) " & nCount & " Formel(n) geändert!", vbInformation 
    Else 
        MsgBox "Es wurden keine Formeln geändert!", vbExclamation 
    End If 
End With 
End Sub 
Man könnte dies auch gleich direkt über Private Sub Worksheet_Change in der Tabelle machen
Dann kommt in die entsprechende Tabelle dieser Code anstatt Makro1.

Private Sub Worksheet_Change(ByVal Target As Range)
'Code abbrechen sollte eingabe nicht in A1 erfolgen 
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
With ActiveSheet
    '1. Parameter Zelle Jahr 
    '2. Parameter Bereich der durchsucht und ersetzt werden soll 
    Bearbeite_Verknuepfung .Range("A1"), .Range("A2:A500")
End With
End Sub
Gruß Tino
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige