Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Creating Relative Links in one Excel spreadsheet to multiple Word files...

    Hey guys,

    I've been searching around trying to figure out how best to manage this, and I've found a few things about creating relative links in Word to link to Excel, but not the other way around.

    I have a large Excel "tracker" spreadsheet, as I've come to call it, that lists a bunch of file names and then links to files (all Word docs) on our servers at Work. However, these links were just created with a basic hyperlink > Select File function so if I were to send this spreadsheet and the Word docs to another computer outside of our network, the links are all broken.

    I want to be able to place the Excel doc in the same folder as all of the Word files, and then have relative links in the spreadsheet to each of the Word files so when I click on each link, it opens up the corresponding Word doc regardless of what computer you're on (as long as you have the entire folder present).

    Is there a simple solution for this?

    Needs to be both Mac and Windows friendly, I'm running Office 2011 Mac at work and Office 2010 at home.

    Let me know, thanks!
    Jonathan

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Jonathan,

    If I understand you correctly, you want to be able to open a workbook that contains links to Word Documents that are always located in the same directory as the workbook. If the files are moved to a different computer, you want the paths to the linked documents automatically changed to accommodate the new location.

    The following code looks at the current path of the workbook and assigns it to a variable. Since the path is the same for the docx files, the variable is used in a statement to open all the hyperlinked documents. You can place your workbook in any folder and the code will automatically build the hyperlink to open them. This code needs to be placed in the Workbook_Open Event Subroutine to create the new links when the workbook opens (Workbook Module.xlsm). In the other attached file (Standard Module.xlsm), I placed it in a standard module so you will be able to see it happen by clicking the "Create Hyperlinks" Button

    I have set up the spreadsheet with a looping routine that builds the hyperlinks from cells in column A (see image). If the linked cells are scattered about the sheet, use the second bit of code and duplicate the commented line for each linked cell. Of course, you need to replace the files in the cells with actual files to avoid an error. Move the files from folder to folder. You will see that the paths for the hyperlinks will be rebuilt where ever the files are placed and are able to be opened by clicking on them.

    HTH,
    Maud

    Hyperlinks.jpg

    CODE WITH LOOPING CELLS
    Code:
    Private Sub Workbook_Open()
    'ROUTINE WILL UPDATE HYPERLINKS WHEN FILE OPENS
    Dim path As String
    
    With Worksheets("Sheet1")  'CHANGE SHEET NAME TO NAME OF SHEET
    path = ActiveWorkbook.path  'FIND PATH OF WORKBOOK AND ASSIGN TO VARIABLE
    For I = 1 To 5   'CYCLE THROUGH CELLS IN COLUMN A.  ADJUST 5 TO NUMBER OF CELLS TO LOOP
        file = .Cells(I, 1).Value  'ASSIGN FILE NAME TO VARIABLE
        .Cells(I, 1).Hyperlinks.Add Anchor:=.Cells(I, 1), Address:=path & "\" & file  'USE VARIABLES TO BUILD PATH OF DOCUMENTS
    Next I
    End With
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'REMOVE THE HYPERLINK BEFORE CLOSING
    With Worksheets("Sheet1")
    For I = 1 To 5
        .Cells(I, 1).Hyperlinks.Delete
    Next I
    End With
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'REMOVE THE HYPERLINK BEFORE SAVING
    With Worksheets("Sheet1")
    For I = 1 To 5
        .Cells(I, 1).Hyperlinks.Delete
    Next I
    End With
    End Sub
    CODE FOR SCATTERED CELLS
    Code:
    Private Sub Workbook_Open()
    Dim path As String
    
    path = ActiveWorkbook.path
    
    Range("A1").Hyperlinks.Add Anchor:=Range("A1"), Address:=path & "\" & Range("A1").Value  'DUPLICATE FOR EACH LINKED CELL
    Range("H7").Hyperlinks.Add Anchor:=Range("H7"), Address:=path & "\" & Range("H7").Value
    
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-06-05 at 06:07.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    JonathanAlstad (2013-06-07)

  4. #3
    New Lounger
    Join Date
    May 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you so much sir! I did not anticipate that anyone would reply, especially with such an amazing amount of helpfulness. I'm going to try this out as soon as I get home and see how it works!

    Thanks again,
    Jonathan

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •