How do I create a Hyperlink in Excel from Wor

Microsoft Microsoft word 2007
March 17, 2010 at 04:28:44
Specs: Windows XP
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:

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

'Adds Hyperlink

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

MyXL.Close SaveChanges:=True

See More: How do I create a Hyperlink in Excel from Wor

Report •

March 17, 2010 at 06:50:02

Here is a macro based on your code.

I have changed the filename and path, so you will need to replace it with yours. I used a single path & filename combination to get the Excel file and access it with it's default application, i.e., Excel.

There is no need to 'Activate' the Excel file, and as is the case for most VB statements in Excel, it is not necessary to 'Select' a cell before accessing it.

I used the Word document's filename and path properties to create the hyperlink, so that the hyperlink will always be correct even if you choose to save the Word document somewhere else or with a different name.

I also access the worksheet by name ("Sheet1") rather than ActiveSheet, so that there is no ambiguity.

Here is the code:

Public Sub LinkXL()

'get file object opened by Excel (default application by file extension association)
Set MyXl = GetObject("C:\temp\WordLinked.xls")

'Add Hyperlink
MyXl.Worksheets("Sheet1").Hyperlinks.Add _
                Anchor:=MyXl.Worksheets("Sheet1").Range("A6"), _
                Address:=ActiveDocument.Path & "\" & ActiveDocument.Name, _
                TextToDisplay:="Test Document"

'make sure file is set to visible, so that user can see it in Excel
MyXl.Windows(1).Visible = True

'save and close the file
MyXl.Close SaveChanges:=True
End Sub

The above is in a standard sub routine, but I am assuming you are placing it in a 'Before Close' event. It should work the same in a before close, although I haven't tested it.


Report •
Related Solutions

Ask Question