Not sure if this can be done. There are word files in a directory with all familiar names. i.e. fr001, fr002, fr003, etc... all with the extension .doc. I would like to know is there a code that will link an excel file to a directory and then be able to type the file name within a cell and it is automatically hyperlinked to that cell?

Recommended Answers

All 4 Replies

In your Word document, do ctrl-F9. Within the braces that word gives you, type the following:

HYPERLINK "C:\\My Documents\\MyExcelFile.xls"

So it looks like
{ HYPERLINK "C:\\My Documents\\MyExcelFile.xls" }

But note that you can't type the { } braces by hand. You must use ctrl-F9 and type between the braces that Word gives you.

Obviously, you need to replace the text between the quote marks with your own folder and file name. Be sure to use double back slashes ("\\") in your path name.

When you're finished, press F9, which toggles between displaying the field codes (that you just typed) and the field result (which is the hyperlink).

When you click the link, Excel will run and will open your Excel workbook.

If you want to link to a specific sheet in workbook -

In your Word document, do ctrl-F9. Within the braces that Word gives you, type so that it looks like the following:

{ HYPERLINK "C:\\My Documents\\MyExcelFile.xls#MySheet!A1" }

If your sheet name contains a space, then you need to enclose it in single quote marks, like this:

{ HYPERLINK "C:\\My Documents\\MyExcelFile.xls#'My Sheet'!A1" }

Alternatively, you can use the \l (that's a lower-case L) switch to identify the range name:

{ HYPERLINK "C:\\MyDocuments\\MyExcelFile2.xls" \l "'Sheet 1'!A1" }

I'm sorry I didn't explain fully. I neeed to be in excel and type the word doc name so that the word document is hyperlinked in the excel file, sorry not the word file. So in a column you can type the word document file name and it's hyperlinked in excel.

Congratulations! You're no longer a DaniWeb newbie.<br /> <br />
Your DaniWeb account has just been upgraded from newbie status and now you have the ability to take advantage of everything the community has to offer.<br /> <br />
You can now enjoy an advertisement-free DaniWeb by ticking the checkbox to Disable Ads in your profile. You will no longer have to fill out the human verification check when you post. You can also now send unlimited private messages, contribute new code snippets, and tag articles with never-before-used tags.

Thank you. I found my answer:

'Put this in the worksheet module: 
Private Sub Worksheet_Change(ByVal Target As Range) 
'change "c:\tmp\" to whatever reference you need 
'a cell, a public variable, a fixed string 

If Target.Column = 7 Then 'If by column use this, if not just delete IF part of statement but then any cell will hyperlink.
   MakeHyperLink Target, "c:\tmp\" 
   End If
    End Sub 

'Put this in the standard module: 
Public Function MakeHyperLink(InRange As Range, _ 
ToFolder As String, _ 
Optional InSheet As Worksheet, _ 
Optional WithExt As String = "doc") As String 
Dim rng As Range 
Dim Filename As String 
Dim Ext As String 
'check to see if folder has trailing \ 
If Right(ToFolder, 1) <> "\" Then 
Filename = ToFolder & "\" 
Filename = ToFolder 
End If 
'check to see if need ext 
If WithExt <> "" Then 
'check to see if ext has leading dot 
If Left(WithExt, 1) <> "." Then 
WithExt = "." & WithExt 
End If 
End If 

Thank you AndreRet for you input but believe I can use that too. Thank you again.

commented: Well done in solving your problem. :) +12
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.