Insert hyperlink

[Edward]

I import some data from Excel and insert them in tables in Word. To do this I
use an array and first populate the array with data in Excel and then insert
those data one by one from the array to my tables in Word. I use variant type
for my array and onde of the filed which i import is email address ,. When I
insert those email addresses in word they don'tt come as hyperlink but when I
go back and put a space before and after they become hyperlinke. Is there any
way in VBA to make sure they are inserted as hyperlink ?

 

[Edward]

Hi everybody,
Sorry I thought to add the following code to my question maybe it will be
more clear
I have the following code
..Cell(2, 2).Range.Text = "Phone:" & excelArray(num, 8) & vbCr & "Fax:" & _
excelArray(num, 9) & vbCr & "E-Mail:" & excelArray(num, 10)
now if I can replace excelArray(num,10) with
something like
ActiveDocument.Hyperlinks.Add ? , excelArray(num, 10)
it should work but I don't know how ?

 

[Doug Robbins - Word MVP]

Use

Dim arange as Range

..Cell(2, 2).Range.Text = "Phone:" & excelArray(num, 8) & vbCr & "Fax:" & _
excelArray(num, 9) & vbCr & "E-Mail:"
Set arange = .Cell(2, 2).Range
arange.Collapse wdCollapseEnd
ActiveDocument.Hyperlinks.Add arange, excelArray(num, 10)


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Edward]

Thanks. I inserts the email address but just like my original code its not
hyperlinked ( blue with underline) I need to manually add a space before and
immidietly after and then it becomes hyperlinked. Any suggestions?

 

[Edward]

Sorry disregard my last posting .I ran the code line by line
Cell(2, 2).Range.Text = "Phone:" & vbTab & excelArray(num, 8) & vbCr &
"Fax:" & vbTab & excelArray(num, 9) & vbCr & "E-Mail:"
Set arange = .Cell(2, 2).Range
arange.Collapse wdCollapseEnd
ActiveDocument.Hyperlinks.Add arange, excelArray(num, 10) <----
this line adds the hyperlink into the next cell ( cell (2,3) not into the
current cell ( cell (2,2) ) but its hyperlinked, and becuse I have something
else to add in cell (2,3) it is replaced by the new text. Is there a way to
make sure hyperlink is added after email: and not into the next cell?
Thanks
--
Best regards,
Edward

Thanks. I inserts the email address but just like my original code its not
hyperlinked ( blue with underline) I need to manually add a space before and
immidietly after and then it becomes hyperlinked. Any suggestions?

 

[Jay Freedman]

Sorry disregard my last posting .I ran the code line by line
Cell(2, 2).Range.Text = "Phone:" & vbTab & excelArray(num, 8) & vbCr &
"Fax:" & vbTab & excelArray(num, 9) & vbCr & "E-Mail:"
Set arange = .Cell(2, 2).Range
arange.Collapse wdCollapseEnd
ActiveDocument.Hyperlinks.Add arange, excelArray(num, 10) <----
this line adds the hyperlink into the next cell ( cell (2,3) not into the
current cell ( cell (2,2) ) but its hyperlinked, and becuse I have something
else to add in cell (2,3) it is replaced by the new text. Is there a way to
make sure hyperlink is added after email: and not into the next cell?
Thanks

Two possibilities: Either after the Set arange statement add the line

arange.MoveEnd Unit:=wdCharacter, Count:=-1

or after the .Collapse statement add the line

arange.Move Unit:=wdCharacter, Count:=-1

What's happening is that when you select the range of an entire cell (which
includes the cell marker) and then collapse to its end, the resulting range
falls into the next cell (or possibly into the no-man's land just to the left of
the row marker if you were in the row's last cell). Either of the moves will
pull the range back into the starting cell.

 

[Edward]

Thank you guys. Yes it inserts the hyperlink into the correct cell, however
there is one more thing missing . when I was not using this code to insert as
hyperlink I was adding a spce before an after the text manually and it was
becoming as email address and I was able to clcik on it and it was showing
the outlook email page , ready to email . Now, that I use this code to show
the hyperlink , link is refeering to the folder where my files are and they
don't open outlook email page ( can not open the specific file) . This
follwoing code works fine

ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
"mailto:[email protected]"
so I don't know why is not working with those additions you guys suggeseted .
As I mentioned before I'm using the variant type of array to keep the data
but also to make sure Im converting it to text I used
Trim$(excelArry(num,10)) but no success.
Any suggestion?
Thank you.
Best regards,
Edward

 

[Doug Robbins - Word MVP]

Replace the excelArray(num, 10) in the code that I gave you (modified as
suggested by Jay)

With

"Mailto: " & excelArray(num, 10), , , excelArray(num, 10)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Edward]

Awesome ! that you so much guys !