Microsoft Excel

Herbers Excel/VBA-Archiv

externe Verknüpfung per Makro einfügen

Betrifft: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 09.12.2007 17:13:09

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

  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Gerd L
Geschrieben am: 09.12.2007 17:22:01

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


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 10.12.2007 20:37:41

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.


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Josef Ehrensberger
Geschrieben am: 09.12.2007 17:22:19

Hallo Michael,

zeig' uns doch deinen Code.


Gruß Sepp



  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 09.12.2007 18:26:55

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.


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Josef Ehrensberger
Geschrieben am: 09.12.2007 18:31:56

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))"



Gruß Sepp



  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 09.12.2007 18:43:01

Super.

Besten Dank. Ich werde das morgen in der Arbeit gleich testen.
Rückmeldung folgt dann.

Gruß, Michael


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Daniel
Geschrieben am: 09.12.2007 18:49:43

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


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 09.12.2007 20:18:59

Hallo.

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


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Daniel
Geschrieben am: 09.12.2007 18:35:56

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


  

Betrifft: AW: externe Verknüpfung per Makro einfügen von: Michael Kerschl
Geschrieben am: 09.12.2007 18:42:51

Hallo.

Ich werde das Makro mal entsprechend anpassen und die Zeiten beobachten.

Danke für den Tip.


 

Beiträge aus den Excel-Beispielen zum Thema "externe Verknüpfung per Makro einfügen"