Thema: externe Verknüpfung per Makro einfügen

Hallo Kollege
Ich habe eine Datei, in der ich per Makro genau 500 Verknüpfungen auf externe Dateien einbringe.
In einem Feld gebe ich einen Dateinamen ein, auf den dann die Verknüpfungen erstellt werden.
Per "indirekt" geht das nicht, weil die externe Datei geöffnet sein müsste.
Also aktualisiere ich die einzelnen Felder mit den Verknüpfungen per Makro.
Momentan mach ich das über einen range-Befehl
1. Selektieren des Range (Bereich)
2. for each zelle in range.....
Damit dauert die Aktualisierung bei meinem Laptop etwa 2,5 Minuten. Da ich zwei dieser Bereiche habe, dauert eine komplette Aktualisierung etwa 5 Minuten.
Frage:
welche Möglichkeit gibt es, um das Einbringen der Verknüpfungen schneller vorzunehmen?
Danke. Gruß, Michael

Hallo Michael,
1. kein Select
2. Hängt davon ab, welche Datenstruktur Du hast u. was konkret gemacht werden soll.
(For each... Schleifen sind die langsamsten)
Gruß Gerd

So, hab eure Tips eingearbeitet. Mit vollem Erfolg:
1. ohne Änderung: Dauer 2:44 min
2. statt meiner "for each zelle...."-Schleife habe ich die "Bereich.Formula"-Version verwendet. Dauer: 1:33 min
Also schon wesentlich schneller.
3. dann habe ich zusätzlich die Datei vor dem einbringen der Verweise geöffnet und anschließend wieder
geschlossen. Dauer: 0:04 min.
In der jetzigen Version dauern 500 externe Verweise gerade einmal noch 4 Sekunden.
Danke für eure nutzbringenden Tips.

Hallo Michael,
zeig' uns doch deinen Code.

im Grunde ganz simpel:
'Definition der Bereiche und Werte
Set bereich_LAB = Range("B10:V19") 'hier werden die Verknüpfungen eingestellt
kw1a = Cells(2, 2).Value 'hier steht der Dateiname
'Definition des Vergleichspfades
kw1 = "'" & pfad & "[KW_" & kw1a & ".xls]" & Blatt & "'!C1:C63," 'das ist der Pfad inkl. Dateiname
'LAB-Werte über Verknüpfung ermitteln
For Each zelle In bereich_LAB
zelle.Value = "=IF(ISNA(VLOOKUP(RC1," & kw1 & "R9C,FALSE)) = TRUE,0,VLOOKUP(RC1," & kw1 & "R9C,FALSE))"
Next zelle
Der Lookup prüft, ob der Vergleichswert in der externen Datei gefunden wird. Wenn ja, dann wird der Wert in der Zelle angezeigt.

Hallo Michael,
das sollte ohne schleife so gehen.
bereich_LAB.Formula = "=IF(ISNA(VLOOKUP(RC1," & kw1 & _
    "R9C,FALSE)) = TRUE,0,VLOOKUP(RC1," & kw1 & "R9C,FALSE))"

Super.
Besten Dank. Ich werde das morgen in der Arbeit gleich testen.
Rückmeldung folgt dann.
Gruß, Michael

Hi Michael
hab grad noch nen Speed-Killer gesehen (bei grossen Datenmengen)
der Vlookup() bzw SVerweis mit 4. Parameter = FALSE bzw 0 ist im vergleich zu seinem Kollgegen mit 4. Paramter = TRUE bzw 1 sehr langsam.
solltest du grossen Datenmengen haben, wäre es besser hier umzustellen.
musst du dabei folgendes beachten:
- die Suchmatrix muss aufsteigend sortiert sein
- es gibt keine Fehlermeldung, wenn der suchwert nicht vorhanden ist, dh. du musst dies prüfen.
am einfachten macht man nen SVerweis auf die Spalte 1, dann muss der Suchbegriff mit dem Rückgabewert übereinstimmen (ansonsten ist der Suchbegriff nicht vorhanden)
ist zwar ein bischen aufwand, aber es lohnt sich
(nur mal als beispiel, bei 1000 Datenzeilen braucht der Sverweis(,,,falsch) 1 bis 1000 vergleiche (im Schnitt 500), der Sverweis(,,,wahr) braucht dagegen maximal 12 (oder 13) Vergleiche, bis er am Ziel ist)
Gruß, Daniel

Hallo.
Danke schon mal. Ich werde das morgen in die Datei integrieren.

Hi
da beim Einbringen der externen Verknüpfung diese auch gleich ausgeführt werden, würde es dein Makro sehr beschleunigen, wenn du für diese Aktion die Externen Dateien geöffnet hast.
für dein Makro hätte das auch den Vorteil, daß du den Dateipfad der externen Verknüpfungen nicht in der Formel mit angeben musst, sondern nur den Dateinamen.
der Dateipfad wird dann von Excel automatisch beim schließen der Verknüpften Datei ergänzt.
Gruß, Daniel

Hallo.
Ich werde das Makro mal entsprechend anpassen und die Zeiten beobachten.
Danke für den Tip.