P
Prashanth
Hi Excel Gurus,
I'm stuck with a prb creating hyperlinks in Excel.
The way we do is -
1. We load the data in an vb array eg: arr(rows, cols)
2. Supply this array to the excel using the below method,
excelapp.Range("A1:G1000").Value = arr
This will copy all the data in the array to excel sheet.
3. One of the columns in the array "arr" will have a hyperlink code.
Eg: arr(0) = "Data0", arr(1) = "Data1", arr(2) = "Data2"
arr(3) = "Data3", arr(4) = "Data4", arr(5) = "Data5"
arr(6) = "=hyperlink(""javascript:window.open('http://www.msn.com','newwin','toolb...dth=600,height=600,top=10,left=400');void(0);"","Data6")"
arr(7) = "Data7"
4. This will create Hyperlinks perfectly, i've tested it and it works.
But the prb is there is limitation to the url character size is 256
for excel hyperlink method. Our url will be more than 256 for sure.
5. The reason i'm using the window.open is we need to open this in a
new window. If the file is .xls it will open in a new window for all
hyperlinks, but we actually save it as .HTM and the url that is
created by excel is
<a href="http://www.msn.com"> it wudnt have traget="_new" etc..So
window.open is the only alternative for us.
6. Is there any other efficient way to create hyperlinks in excel when
the data is loaded in an array. We dont want to goto each cell and
create a hyperlink. I know that we can do this way using -
excelapp.ActiveSheet.Hyperlinks.Add
anchor:=xlsapp.ActiveSheet.Range("a1:a1"), _
Address:=sURL, ScreenTip:=sToolTip
Anyone who knows the answer plz respond.
Tks in advance
Prashanth
I'm stuck with a prb creating hyperlinks in Excel.
The way we do is -
1. We load the data in an vb array eg: arr(rows, cols)
2. Supply this array to the excel using the below method,
excelapp.Range("A1:G1000").Value = arr
This will copy all the data in the array to excel sheet.
3. One of the columns in the array "arr" will have a hyperlink code.
Eg: arr(0) = "Data0", arr(1) = "Data1", arr(2) = "Data2"
arr(3) = "Data3", arr(4) = "Data4", arr(5) = "Data5"
arr(6) = "=hyperlink(""javascript:window.open('http://www.msn.com','newwin','toolb...dth=600,height=600,top=10,left=400');void(0);"","Data6")"
arr(7) = "Data7"
4. This will create Hyperlinks perfectly, i've tested it and it works.
But the prb is there is limitation to the url character size is 256
for excel hyperlink method. Our url will be more than 256 for sure.
5. The reason i'm using the window.open is we need to open this in a
new window. If the file is .xls it will open in a new window for all
hyperlinks, but we actually save it as .HTM and the url that is
created by excel is
<a href="http://www.msn.com"> it wudnt have traget="_new" etc..So
window.open is the only alternative for us.
6. Is there any other efficient way to create hyperlinks in excel when
the data is loaded in an array. We dont want to goto each cell and
create a hyperlink. I know that we can do this way using -
excelapp.ActiveSheet.Hyperlinks.Add
anchor:=xlsapp.ActiveSheet.Range("a1:a1"), _
Address:=sURL, ScreenTip:=sToolTip
Anyone who knows the answer plz respond.
Tks in advance
Prashanth