Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Excel hyperlink base in VBA
I am running Excel 2003.
I have many .xls files, each of which has many HYPERLINK calls. e.g. <=HYPERLINK("http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?la nguage=en&sessionID=0&itdDate="&$A$2&"&type_origin =stop&name_origin="&D3 5&"&type_destination=stop&name_destination="&E35&I F(A35<"","&itdTripDat eTimeDepArr=arr&itdTime="&A35),"Trip") Occasionally, such calls are too long and "#VALUE!" is rendered rather than "Trip". [HYPERLINK has an undocumented restriction of about 255 characters on the length of its first (link_location) argument. If I write "http://journeyplanner.tfl.gov.uk/user/" to File\Properties\Summary\Hyperlink base: and remove it from my HYPERLINK call so it is shortened to <=HYPERLINK("XSLT_TRIP_REQUEST2?language=en&sessio nID=0&itdDate="&$A$2&" &type_origin=stop&name_origin="&D35&"&type_destina tion=stop&name_destina tion="&E35&IF(A35<"","&itdTripDateTimeDepArr=arr& itdTime="&A35),"Trip") The shorter HYPERLINK call is rendered as "Trip". Hyperlink base must be a path. e.g. If it is written as "http://journeyp lanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?language=en&sessionID=0", anything after the last backslash [XSLT_TRIP_REQUEST2?language=en&sessio nID=0] is ignored. How do I use VBA to set the data, displayed by File\Properties\Summary\H yperlink base: I tried recording a macro to do this. It contained no relevant information. I had a similar failure with a macro recording a HYPERLINK call. I have also tried Google Groups and Google itself without success. Even <http://www.cpearson.com/excel/mainpage.aspx has nothing to say about HYPERLINK calls. -- Walter Briscoe |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Hyperlink base removed when opening in Excel 2002 | Links and Linking in Excel | |||
Hyperlink Base. | Excel Programming | |||
Hyperlink Base | Excel Programming | |||
hyperlink base did not save | Links and Linking in Excel | |||
Excel Hyperlink Base Directory | Excel Discussion (Misc queries) |