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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Excel hyperlink base in VBA
In message of Mon, 6 May 2013
08:02:20 in microsoft.public.excel.programming, Walter Briscoe writes 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_origi n=stop&name_origin="&D3 5&"&type_destination=stop&name_destination="&E35& IF(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&sessi onID=0&itdDate="&$A$2&" &type_origin=stop&name_origin="&D35&"&type_destin ation=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. I believe I have answered my own question. I googled title subject author manager excel vba The first hit was <http://www.knowexcel.com/view/1417462-vba-set-file- properties-summary-title.html <http://www.ozgrid.com/forum/showthread.php?t=25175 showed ThisWorkbook.BuiltinDocumentProperties("title") = "YourTitle" I found For Each q In ThisWorkbook.BuiltinDocumentProperties: debug.Print q.name: next listed about 30 names. The penultimate one was "Hyperlink base". I will apply my new-found knowledge. Readers may take it I have solved my difficulty, unless I write otherwise within a day. ;) -- Walter Briscoe |
Reply |
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) |