PDA

Vollständige Version anzeigen : Verknüpfung (Link) innerhalb einer Formel


gridlog86
20.03.2009, 09:15
Hallo Excel-Freunde,

ich habe folgendes Problem:

ich habe eine Verknüpfung, auf die ich gerne per Doppelklick zugreifen möchte. Das Problem ist aber das sich die Verknüpfung innerhalb einer Formel befindet. Man müsste ja innerhalb des Strings den Anfang und das Ende suchen und mit Hilfe des Codes Application.Goto Reference:= zur Verknüpften Datei gelangen. Habe jedoch leider keine Idee wie ich den String auslesen könnte.

Hier mal ein Beispiel für die Formel:
=WENN(D4<=$W$1;'C:\Test\[Testmappe1.xls]Tabelle1'!D10;0)

Vielen Dank im Voraus für die Mühe
Gruß
Andreas

IngGi
20.03.2009, 14:01
Hallo Andreas,

versuch es mal hiermit:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Worksheet_BeforeDoubleClick(<span class="TOKEN">ByVal</span> Target <span class="TOKEN">As</span> Range, Cancel <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>)
&nbsp;
<span class="TOKEN">Dim</span> intPfad <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWB <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWS <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
&nbsp;
<span class="TOKEN">Dim</span> strPfad <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWS <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
&nbsp;
Cancel = <span class="TOKEN">True</span>
intPfad = InStr(1, Target, &quot;[&quot;) - 1
intWB = InStr(1, Target, &quot;]&quot;) - 1
intWS = InStr(1, Target, &quot;!&quot;) - 1
&nbsp;
strPfad = Replace(Left(Target, intPfad), &quot;'&quot;, &quot;&quot;)
<span class="TOKEN">If</span> Right(strPfad, 1) &lt;&gt; &quot;\&quot; <span class="TOKEN">Then</span> strPfad = strPfad &amp; &quot;\&quot;
strWB = Mid(Target, intPfad + 2, intWB - intPfad - 1)
strWS = Replace(Mid(Target, intWB + 2, intWS - intWB - 1), &quot;'&quot;, &quot;&quot;)
&nbsp;
Workbooks.Open strPfad &amp; strWB
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(Target, Len(Target) - intWS - 1))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span>&nbsp;</pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Gruß Ingolf

gridlog86
20.03.2009, 14:09
Hallo Ingolf,

Danke erstmal für die schnelle Hilfe. Der erste test hat leider nicht funktioniert (Ungültiger Prozeduraufruf oder ungültiges Argument). Versuche aber am Wochenende den Code zu verstehen und mal auszutesten.

Ich melde mich dann wieder..

Schönes W-Ende
Andreas

gridlog86
23.03.2009, 12:53
Hallo Ingolf,

der ersehnte Durchblick blieb leider aus...
Wie gesagt: "Ungültiger Prozeduraufruf oder ungültiges Argument"

Vielleicht hast du eine Idee.

Gruß
Andreas

IngGi
23.03.2009, 13:22
Hallo Andreas,

ich habe es gerade nochmal ausprobiert. Bei mir funktioniert das einwandfrei. Die Mappe und das Tabellenblatt müssen natürlich vorhanden sein. In welcher Programmzeile tritt der Fehler denn auf? Was enthalten die Variablen für Pfad, Mappe und Tabellenblatt, also strPfad, strWB und strWS? Das kannst du dir mit folgender Ergänzung anzeigen lassen.

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Worksheet_BeforeDoubleClick(<span class="TOKEN">ByVal</span> Target <span class="TOKEN">As</span> Range, Cancel <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>)
&nbsp;
<span class="TOKEN">Dim</span> intPfad <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWB <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWS <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
&nbsp;
<span class="TOKEN">Dim</span> strPfad <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWS <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
&nbsp;
Cancel = <span class="TOKEN">True</span>
intPfad = InStr(1, Target, &quot;[&quot;) - 1
intWB = InStr(1, Target, &quot;]&quot;) - 1
intWS = InStr(1, Target, &quot;!&quot;) - 1
&nbsp;
strPfad = Replace(Left(Target, intPfad), &quot;'&quot;, &quot;&quot;)
<span class="TOKEN">If</span> Right(strPfad, 1) &lt;&gt; &quot;\&quot; <span class="TOKEN">Then</span> strPfad = strPfad &amp; &quot;\&quot;
strWB = Mid(Target, intPfad + 2, intWB - intPfad - 1)
strWS = Replace(Mid(Target, intWB + 2, intWS - intWB - 1), &quot;'&quot;, &quot;&quot;)
&nbsp;
MsgBox &quot;strPfad = &quot; &amp; strPfad &amp; Chr(10) &amp; _
&quot;strWB = &quot; &amp; strWB &amp; Chr(10) &amp; _
&quot;strWS = &quot; &amp; strWS
&nbsp;
Workbooks.Open strPfad &amp; strWB
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(Target, Len(Target) - intWS - 1))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span>&nbsp;</pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Gruß Ingolf

gridlog86
23.03.2009, 13:57
Hallo Ingolf,

Dateien sind vorhanden... :)

Der Fehler kommt bei folgender Zeile:
strPfad = Replace(Left(Target, intPfad), "'", "")

Die MsgBox zeigt dementsprechend noch keine Werte an.

Meine ExcelFormel sieht in meinem Beispiel so aus:
=WENN(D4<=$W$1;'M:\03_BR\Controlling\test\V-Ist Anpassung\verknüpfung\[test3.xls]Tabelle1'!$C$9;0)

Gruß
Andreas

IngGi
23.03.2009, 15:02
Hallo Andreas,

jetzt habe ich den Fehler entdeckt! Ich bin bei meinen Tests und Überlegungen die ganze Zeit von der falschen Annahme ausgegangen, dass in der Zelle mit der Formel der Pfad mit allen Angaben steht. Aber da steht natürlich der Inhalt der Zelle, auf die verlinkt wird. Bei mir hatte ich den Pfad bis zur Zelle immer noch in zusätzliche doppelte Anführungszeichen gesetzt. Daher stand in meiner Zelle der Pfad und dadurch hat es bei mir immer funktioniert. Ich habe das Makro jetzt nochmal etwas umgemodelt. So sollte es jetzt funktionieren:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Worksheet_BeforeDoubleClick(<span class="TOKEN">ByVal</span> Target <span class="TOKEN">As</span> Range, Cancel <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>)
&nbsp;
<span class="TOKEN">Dim</span> intPfad <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWB <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWS <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
&nbsp;
<span class="TOKEN">Dim</span> strPfad <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWS <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strLink <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
&nbsp;
Cancel = <span class="TOKEN">True</span>
strLink = Target.FormulaLocal
strLink = Right(strLink, Len(strLink) - InStr(1, strLink, &quot;'&quot;))
strLink = Left(strLink, Len(strLink) - 3)
&nbsp;
intPfad = InStr(1, strLink, &quot;[&quot;) - 1
intWB = InStr(1, strLink, &quot;]&quot;) - 1
intWS = InStr(1, strLink, &quot;!&quot;) - 1
&nbsp;
strPfad = Left(strLink, intPfad)
<span class="TOKEN">If</span> Right(strPfad, 1) &lt;&gt; &quot;\&quot; <span class="TOKEN">Then</span> strPfad = strPfad &amp; &quot;\&quot;
strWB = Mid(strLink, intPfad + 2, intWB - intPfad - 1)
strWS = Replace(Mid(strLink, intWB + 2, intWS - intWB - 1), &quot;'&quot;, &quot;&quot;)
&nbsp;
<span class="TOKEN">If</span> InStr(1, Target.FormulaLocal, &quot;;[&quot;) = 0 <span class="TOKEN">Then</span> Workbooks.Open strPfad &amp; strWB
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(strLink, Len(strLink) - intWS - 1))
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span>&nbsp;</pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Gruß Ingolf

gridlog86
23.03.2009, 18:44
Hallo Ingolf,

habe den Code mal eben zu Hause ausprobiert. Leider tritt immer noch ein Fehler auf (Anwendungs- oder objektdefinierter Fehler). Und zwar nun bei folgender Programmzeile
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(strLink, Len(strLink) - intWS - 1))

Gruß und schönen Abend
Andreas

gridlog86
24.03.2009, 08:16
Guten Morgen Ingolf,

hab den Code auf Arbeit noch mal getestet. Ich glaube der Fehler scheint bei .Range(Right(strLink, Len(strLink) - intWS - 1)) zu liegen denn er öffnet die gewünschte Datei samt Tabellenblatt. Die Msgbox zeigt das er bei range ein ";" hinter der Zelle zu stehen hat (Bsp. bei mir "C9;").

Gruß Andreas

gridlog86
24.03.2009, 09:23
Hallo Ingolf,

ich glaube ich habe eine Lösung für das Problem gefunden.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim intPfad As Integer
Dim intWB As Integer
Dim intWS As Integer

Dim strPfad As String
Dim strWB As String
Dim strWS As String
Dim strLink As String


Cancel = True
strLink = Target.FormulaLocal
strLink = Right(strLink, Len(strLink) - InStr(1, strLink, "'"))
strLink = Left(strLink, Len(strLink) - 4)

intPfad = InStr(1, strLink, "[") - 1
intWB = InStr(1, strLink, "]") - 1
intWS = InStr(1, strLink, "!") - 1

strPfad = Left(strLink, intPfad)
If Right(strPfad, 1) <> "\" Then strPfad = strPfad & "\"
strWB = Mid(strLink, intPfad + 2, intWB - intPfad - 1)
strWS = Replace(Mid(strLink, intWB + 2, intWS - intWB - 1), "'", "")

MsgBox Right(strLink, Len(strLink) - intWS - 1)

If InStr(1, Target.FormulaLocal, ";[") = 0 Then Workbooks.Open strPfad & strWB
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(strLink, Len(strLink) - intWS - 1))

End Sub


Siehst du hier irgendwelche Schwierigkeiten bei der Änderung von -3 auf -4?
Beim ersten Test hats nämlich funktioniert...


Schönen Gruß
Andreas

IngGi
24.03.2009, 09:52
Hallo Andreas,

in der von dir angegebenen Formel steht hinter dem Zellbezug (also $C$9) noch ;0). Das sind 3 Zeichen, die noch abgeschnitten werden müssen, daher -3. Offensichtlich steht in deiner jetzigen Formel noch ein viertes Zeichen, ich nehme an, an Stelle der 0 eine zweistellige Kombination. Das funktioniert dann mit -4. Sollte die Zahl der Zeichen hinter dem Zellbezug allerdings variieren, könntest du die korrekte Zahl der abzuschneidenden Zeichen mit folgender Codezeile berücksichtigen (ersetzt die fette Zeile in deinem letzten Post)
strLink = Left(strLink, InStrRev(strLink, ";") - 1)
Das setzt dann allerdings voraus, dass das Semikolon unmittelbar hinter dem Zellbebezug das letzte Semikolon der Formel ist.

Gruß Ingolf

gridlog86
24.03.2009, 10:56
Hallo Ingolf,

danke dir dafür. Das klappt super. Leider ist nicht immer gewährleistet, dass die Formel so aussieht. Manschmal ist die Formel halt viel komplexer mit mehren Verknüpfungen. Das muss und kann vielleicht auch garnicht alles berücksichtigt werden. Wichtig wäre mir jetzt nur noch, dass wenn die Verknüpfung innerhalb des Strings nicht perfekt gefunden werden kann, eine Info angezeigt wird, dass der Vorgang abgebrochen wird, weil..... is nicht (werde mir noch was Gescheites einfallen lassen).

Ich werde den Code dann auch nicht per doppelklick ausführen lassen, sondern ihn ins Kontextmenü einbinden. Das habe ich schon mal an anderer Stelle verwirklicht und denke das ich es auch hinbekomme.

gegrüßt
Andreas

IngGi
24.03.2009, 11:27
Hallo Andreas,

ich habe noch eine Meldung eingebaut, falls die Weiterleitung nicht klappt:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Worksheet_BeforeDoubleClick(<span class="TOKEN">ByVal</span> Target <span class="TOKEN">As</span> Range, Cancel <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>)
&nbsp;
<span class="TOKEN">Dim</span> intPfad <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWB <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> intWS <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
&nbsp;
<span class="TOKEN">Dim</span> strPfad <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strWS <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> strLink <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
<span class="TOKEN">Dim</span> objWB <span class="TOKEN">As</span> Workbook
<span class="TOKEN">Dim</span> blnWBOK <span class="TOKEN">As</span> <span class="TOKEN">Boolean</span>
&nbsp;
&nbsp;
Cancel = <span class="TOKEN">True</span>
<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
strLink = Target.FormulaLocal
strLink = Right(strLink, Len(strLink) - InStr(1, strLink, &quot;'&quot;))
strLink = Left(strLink, InStrRev(strLink, &quot;;&quot;) - 1)
&nbsp;
intPfad = InStr(1, strLink, &quot;[&quot;) - 1
intWB = InStr(1, strLink, &quot;]&quot;) - 1
intWS = InStr(1, strLink, &quot;!&quot;) - 1
&nbsp;
strPfad = Left(strLink, intPfad)
<span class="TOKEN">If</span> Right(strPfad, 1) &lt;&gt; &quot;\&quot; <span class="TOKEN">Then</span> strPfad = strPfad &amp; &quot;\&quot;
strWB = Mid(strLink, intPfad + 2, intWB - intPfad - 1)
strWS = Replace(Mid(strLink, intWB + 2, intWS - intWB - 1), &quot;'&quot;, &quot;&quot;)
<span class="TOKEN">On Error GoTo 0</span>
&nbsp;
<span class="TOKEN">If</span> InStr(1, Target.FormulaLocal, &quot;;[&quot;) = 0 <span class="TOKEN">Then</span>
<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
Workbooks.Open strPfad &amp; strWB
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> objWB <span class="TOKEN">In</span> Workbooks
<span class="TOKEN">If</span> objWB.Name = strWB <span class="TOKEN">Then</span>
blnWBOK = <span class="TOKEN">True</span>
<span class="TOKEN">Exit For</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> <span class="REM">'objWB</span>
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> blnWBOK <span class="TOKEN">Then</span>
MsgBox &quot;Der Link ist innerhalb der Formel nicht zu identifizieren.&quot;, vbCritical
<span class="TOKEN">Exit Sub</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
Application.Goto Workbooks(strWB).Worksheets(strWS) _
.Range(Right(strLink, Len(strLink) - intWS - 1))
<span class="TOKEN">If</span> ActiveWorkbook.Name &lt;&gt; strWB <span class="TOKEN">Then</span>
MsgBox &quot;Der Link ist innerhalb der Formel nicht zu identifizieren.&quot;, vbCritical
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Gruß Ingolf

gridlog86
24.03.2009, 11:55
Hallo Ingolf,

hab echt Vielen Dank für deine Hilfe. Funktioniert echt super.
Werde das Thema jetzt als erledigt setzen...

Bis zum nächsten Problem :)

Gruß
Andreas