How do I create a Hyperlink in Excel from Word Macro

**jrp_167** · 03-17-2010, 07:39 AM

Hi All,

I'm creating a macro in Word.

When I close down the Word document I am trying to get the Macro to open an excel spreadsheet and create a hyperlink in a cell so that when I next load the excel spreadsheet, I can just click on the hyperlink to take me to the word document.

I know I'm doing something wrong with the creation of the hyperlink. I feel it has something to do with the anchor but I am just not experienced enough to figure out the problem.

The Code I have is:

Code:

Set MyXL = GetObject("M:\Folder\Documents Register.xls")
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True
      
'Adds Hyperlink

        MyXL.Activate
        MyXL.activesheet.Range("A6").Select
        MyXL.activesheet.Hyperlinks.Add Anchor:=ActiveDocument, Address:= _
        "Test%20Modification%20Eng%20Plan%20issue%20A.doc", _
        TextToDisplay:="Test Document"

MyXL.Close SaveChanges:=True

**antoka05** · 03-17-2010, 09:33 AM

Try with:

Code:

    MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("a6"), 
        Address:="Test%20Modification%20Eng%20Plan%20issue%20A.doc", _
        TextToDisplay:="Test Document"

Regards,
Antonio

**jrp_167** · 03-17-2010, 10:14 AM

Originally Posted by antoka05

Try with:

Code:

    MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("a6"), 
        Address:="Test%20Modification%20Eng%20Plan%20issue%20A.doc", _
        TextToDisplay:="Test Document"

Regards,
Antonio

That worked perfectly!! Thanks very much for your help!!

**jrp_167** · 03-18-2010, 11:32 AM

Hi there!

I ended up getting the code working like you said using :

Code:

MyXL.ActiveSheet.Range("A6").Select
        MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("A6")., Address:= _
        myString, _
        TextToDisplay:=myString

However, I then tried to expand the code so that rather than always placing the hyperlink in A6, It looked for the next blank cell in Column A and added the hyperlink there.

Remembering that this Macro is embedded in the Word document, I came up with the following (But it doesn't work):

Code:

Set MyXL = GetObject("M:\Mission Systems Controlled Docs\EW Specifications Documents Register.xls")
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True

'Adds Hyperlinks
        MyXL.Activate
        
        Row_Num = MyXL.ActiveSheet.Range("A65536").End(xlUp).Row + 1 'Determines next blank row
                
        Ref_cell = "A" & Row_Num
        MyXL.ActiveSheet.Range(Ref_cell).Select
        MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range(Ref_cell), Address:= _
        myString, _
        TextToDisplay:=myString

But it doesn't like the line :

Code:

 Row_Num = MyXL.ActiveSheet.Range("A65536").End(xlUp).Row + 1 'Determines next blank row

Any ideas?

**Daniel1234** · 03-18-2010, 12:45 PM

The Code Really looks good however i Cant get it going

Code:

    MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("a6"), 
        Address:="Test%20Modification%20Eng%20Plan%20issue%20A.doc", _
        TextToDisplay:="Test Document"

I have Simply Cut and Paste and using a test excell file and a test word file and it has slight Issues with the

:= in line one and the , at the end of line one.

(Sorry I really am at the bottom of this learning curb)

Daniel1234

**shg** · 03-18-2010, 12:49 PM

Daniel, please take a few minutes to read the forum rules, and then start your own thread.

**jrp_167** · 03-18-2010, 12:50 PM

Originally Posted by Daniel1234

The Code Really looks good however i Cant get it going

Code:

    MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("a6"), 
        Address:="Test%20Modification%20Eng%20Plan%20issue%20A.doc", _
        TextToDisplay:="Test Document"

I have Simply Cut and Paste and using a test excell file and a test word file and it has slight Issues with the

:= in line one and the , at the end of line one.

(Sorry I really am at the bottom of this learning curb)

Daniel1234

needs a _ at the end of line one to signify it continues on the next line down

i.e.

Code:

MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range("a6"), _

Just to keep the context of the thread correct. I still need help on what is wrong with:

Code:

Row_Num = MyXL.ActiveSheet.Range("A65536").End(xlUp).Row + 1 'Determines next blank row

**antoka05** · 03-18-2010, 01:41 PM

When you run macro from Word you can't use Excel constants as 'xlUp'.
I think the best way is you go on Word, open VB Editor (alt + F11), choose Tools, Reference and select 'Microsoft Excel xx.x Object Library'.
Regards,
Antonio

**jrp_167** · 03-19-2010, 11:58 AM

Originally Posted by antoka05

When you run macro from Word you can't use Excel constants as 'xlUp'.
I think the best way is you go on Word, open VB Editor (alt + F11), choose Tools, Reference and select 'Microsoft Excel xx.x Object Library'.
Regards,
Antonio

Ah!! I did that in excel to run the Microsoft Word Object Library but I didnt do it for the 'Microsoft Excel xx.x Object Library' in Word! Thanks for the help!!

My final Question regarding this is that I want a Msgbox to appear over the excel sheet once the info is written to the cells to say that the Register has been updated

So as you see, I wrote the last line in the code to create a msgbox:

Code:

        Row_Num = MyXL.ActiveSheet.Range("A65536").End(xlUp).Row + 1 'Determines next blank row
               
        Ref_cell = "A" & Row_Num
        MyXL.ActiveSheet.Range(Ref_cell).Select
        MyXL.ActiveSheet.Hyperlinks.Add Anchor:=MyXL.ActiveSheet.Range(Ref_cell), Address:= _
        myString, _
        TextToDisplay:=myString
        Issue_cell = "C" & Row_Num
        MyXL.ActiveSheet.Range(Issue_cell).Value = New_Issue 'Updates Issue Number
    
    MsgBox "Register Updated"

However, The Message box, I guess cause it is in the Word Macro Is not shown immediately as it is hovering over the word document (But the current view is of the populated cells in the excel sheet). To click ok, the user then has to select the word document to see the message box.

I want the message box to hover over the excel view. Is this possible?