{"id":11,"date":"2009-03-01T21:31:06","date_gmt":"2009-03-01T20:31:06","guid":{"rendered":"http:\/\/niktation.de\/Niktation\/?p=11"},"modified":"2015-01-13T22:36:27","modified_gmt":"2015-01-13T21:36:27","slug":"excel-vba-followhyperlink-einschrankung-umgehen","status":"publish","type":"post","link":"https:\/\/niktation.de\/Niktation\/?p=11","title":{"rendered":"Excel VBA: FollowHyperlink Einschr\u00e4nkung umgehen"},"content":{"rendered":"

Der FollowHyperlink Befehl kann eine URL mit \u00dcbergabeparametern aufrufen. Allerdings d\u00fcrfen URL und Parameter insgesamt nur 256 Zeichen lang sein. Wenn man eine gro\u00dfe Menge Parameter \u00fcbertragen will, muss man tricksen.<\/p>\n

Das VBA script legt hierf\u00fcr einen HTML-file im Temp-Ordner des Windows-Systems an und f\u00fcllt diesen mit einem Java-Script, das automatisch einen Link aufruft, der beliebig lang sein darf. Dieser lokale File wird dann mit FollowHyperlink aufgerufen.<\/p>\n

Dabei wird die Funktion TempDirektory von www.kulpa-online.com<\/a> benutzt.<\/p>\n

\n

Sub<\/span> callHyperlink()<\/p>\n

\u00a0\u00a0\u00a0\u00a0‘ Dimension all\u00a0 variables.<\/p>\n

\u00a0\u00a0\u00a0\u00a0Dim<\/span> DestFile As<\/span> String<\/span><\/p>\n

\u00a0\u00a0\u00a0 Dim<\/span> FileNum As<\/span> Integer
\n<\/span><\/p>\n

\u00a0\u00a0\u00a0 Dim<\/span> i As<\/span> Integer<\/span><\/p>\n

\u00a0\u00a0\u00a0 Dim<\/span> param As<\/span> String<\/span><\/p>\n

\u00a0\u00a0\u00a0 ‘initialize webadress param<\/p>\n

\u00a0\u00a0\u00a0\u00a0param = <\/span>“<\/span>http:\/\/mypage.de\/myfile.aspx?<\/span>“<\/span>
\n<\/span><\/p>\n

\u00a0\u00a0\u00a0 ‘fill param string like \u201ea1=1&a2=2…&a256=256\u201c<\/p>\n

\u00a0\u00a0\u00a0\u00a0For<\/span> 1 = 1<\/span> To<\/span> 256<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 param = param + “&a”<\/span> + CStr<\/span>(i) + “=”<\/span> + CStr<\/span>(i)<\/p>\n

\u00a0\u00a0\u00a0 Next<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0‘ define destination html file\u00a0 name.<\/p>\n

\u00a0\u00a0\u00a0\u00a0DestFile = TempDirectory + “foreward.html”<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 ‘ Obtain next free file handle number.<\/p>\n

\u00a0\u00a0\u00a0\u00a0FileNum = FreeFile()<\/p>\n

\u00a0\u00a0\u00a0\u00a0‘ create HTML file with redirection<\/p>\n

\u00a0\u00a0\u00a0\u00a0Print<\/span> #FileNum, “<html>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<head><script language=””javascript””>”<\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<!–“<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “var weitergeleitet = “”” + param + “””;”<\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “function weiterleitung() {window.location = weitergeleitet;}”<\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “setTimeout(“”weiterleitung()””, 3000);”<\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “\/\/ –>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<\/script>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<\/head>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<body>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “Please wait, you will be taken to the destination”<\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<\/body>”<\/span><\/p>\n

\u00a0\u00a0\u00a0 Print<\/span> #FileNum, “<\/html>”<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0‘ Close destination file.<\/p>\n

\u00a0\u00a0\u00a0\u00a0Close<\/span> #FileNum<\/p>\n

\u00a0\u00a0\u00a0 ActiveWorkbook.FollowHyperlink Address:=DestFile, _<\/p>\n

\u00a0\u00a0\u00a0 NewWindow:=True<\/p>\n

End Sub<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Der FollowHyperlink Befehl kann eine URL mit \u00dcbergabeparametern aufrufen. Allerdings d\u00fcrfen URL und Parameter insgesamt nur 256 Zeichen lang sein. Wenn man eine gro\u00dfe Menge Parameter \u00fcbertragen will, muss man tricksen. Das VBA script legt hierf\u00fcr einen HTML-file im Temp-Ordner des Windows-Systems an und f\u00fcllt diesen mit einem Java-Script, das automatisch einen Link aufruft, der…<\/p>\n Read More<\/span> Excel VBA: FollowHyperlink Einschr\u00e4nkung umgehen<\/span><\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":"","footnotes":""},"categories":[7,8],"tags":[],"class_list":["entry","author-kai_n","post-11","post","type-post","status-publish","format-standard","category-computer","category-programmieren"],"_links":{"self":[{"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/posts\/11","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11"}],"version-history":[{"count":3,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":14,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=\/wp\/v2\/posts\/11\/revisions\/14"}],"wp:attachment":[{"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/niktation.de\/Niktation\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}