[SOLVED] tcom tcl: Error when trying to add hyperlink with link of length more than 255 characters

tcom tcl: Error when trying to add hyperlink with link of length more than 255 characters

I am running following script on windows 10, with TCL8.4 and tcom installed. I have Excel 2016 and empty excel file 'clone.xls'. I am trying to create a hyperlink with link of length 256 characters in the first cell

package require tcom

set application [::tcom::ref createobject "Excel.Application"]
$application Visible 1

set workbooks [$application Workbooks]
set workbook [$workbooks Open [file nativename [file join [pwd] clone.xlsx]]]
set worksheets [$workbook Worksheets]
set worksheet [$worksheets Item [expr 1]]
set cells [$worksheet Cells]

set a 1234567890
set a [string repeat $a 25]
$cells Item 1 A "=HYPERLINK(\"[set a]123456\",\"Google\")"

$workbook Save
$application Quit

I tried some combinations and found that whenever link in the hyperlink goes more than 255 characters it is throwing following error.

0x800a03ec {Unknown error} while executing "$cells Item 1 A "=HYPERLINK("[set a]123456","Google")"" (file "trial4.tcl" line 14)

If 'Google' is replaced with any number of characters error is not seen. Can someone please tell how it can be resolved. Thanks in advance. --uv.

Solution

Excel has a 255-character limit for the HYPERLINK function (It's 2023, Excel 365 Hyperlink function is limited to 255 characters?, How do I do a hyperlink that exceeds 255 characters?). Workarounds are provided in both articles:

A way around the 255 character limit in the formula bar would be to split the URL into two cells e.g. half the URL in cell A1 and the other half in cell A2. Then in another cell put your hyperlink formula... =HYPERLINK(A1&A2,"Search Google")