Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Maybe you can help me here, I want to change the Path for all the hyperlink in a workbook. What is happening is I have and excel file on the external hard drive (F: drive) and i want the hyperlinks in that worksheet to run from the external hard drive. At the moment the links are directed to the C: drive. The thing is there are more than 1000 cells with hyperlinks, liked to different files. I need to change all of them.

    Regards
    S

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could use something like this:
    Code:
    Sub ChangeHyperlink()
    	Dim wkb As Workbook
    	Dim wks As Worksheet
    	Dim hlink As Hyperlink
    	Dim sOld As String
    	Dim sNew As String
        
    	Set wkb = ActiveWorkbook
    	Set wks = ActiveSheet
    
    	sOld = "C:\"
    	sNew = "F:\"
    	For Each wks in wkb.Worksheets
        	For Each hlink In wks.Hyperlinks
            	hlink.Address = Application.WorksheetFunction. _
                	Substitute(hlink.Address, sOld, sNew)
        	Next hlink
    	Next wkb
    End Sub
    It is essentially a find/replace in the hyperlink address. Add the path names if desired if the path as well as the drive is changing...

    Steve


  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    The code gives me an error "it cannot open specified file".
    This is what i have

    Sub changelink()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim hlink As Hyperlink
    Dim sOld As String
    Dim sNew As String

    Set wkb = ActiveWorkbook
    Set wks = ActiveSheet

    sOld = "C:\Documents and Settings\svokozela\My Documents\PDF Workstuff"
    sNew = "F:\PDF Workstuff"
    For Each wks In wkb.Worksheets
    For Each hlink In wks.Hyperlinks
    hlink.Address = Application.WorksheetFunction. _
    Substitute(hlink.Address, sOld, sNew)
    Next hlink
    Next wkb
    End Sub
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you need to add the "\" to the end of the sOld and sNew lines

    From
    sOld = "C:\Documents and Settings\svokozela\My Documents\PDF Workstuff"
    sNew = "F:\PDF Workstuff"

    to

    sOld = "C:\Documents and Settings\svokozela\My Documents\PDF Workstuff\"
    sNew = "F:\PDF Workstuff\"

Posting Permissions

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