Forum Home
Press F1
Thread ID: 109535	2010-05-12 05:46:00	Macro to change hyperlink paths	jaketrsw (15733)	Press F1

Post ID	Timestamp	Content	User
884270	2010-05-12 05:46:00	I have a macro that is supposed to change the hyperlink paths for hyperlinks I have in an Excel spreadsheet . I built it based on a macro I got off the net which did a Find & Replace on the paths of files from the ActiveSheet . Hyperlinks . The problem is it only changes some of the paths . . . not all of them . As yet I can find no reason for it discriminating between them - they were all entered in the same manner, display the same format of file path, have the required text to change etc . . . Here is the macro I am using: Private Sub CommandButton9_Click() 'Change Hyperlinks Dim hl As Hyperlink Dim Wrongadd As String Dim Newadd As String Sheets("Master Index & Search") . Select Sheets("Master Index & Search") . Activate Wrongadd = Application . InputBox(Prompt:="Enter the part of the hyperlink addresses you wish to change", _ Title:="Change All Hyperlinks", Type:=2) Newadd = Application . InputBox(Prompt:="Enter the equivalent part of the hyperlink addresses you wish to change to", _ Title:="Change All Hyperlinks", Type:=2) MsgBox ("All instances of " & Wrongadd & " Will be removed from the path of all Hyperlinks on this page and replaced with " & Newadd) Sheets("Master Index & Search") . Select Sheets("Master Index & Search") . Activate For Each hl In ActiveSheet . Hyperlinks hl . Address = Replace(hl . Address, Wrongadd, Newadd) Next End Sub Anyone have any ideas what could be going wrong? I don't fully understand the 'ActiveSheet . Hyperlinks' part of this macro - does it refer to a kind of list of hyperlinks? Is it possible to print this list so I can check it for consistency? Any help is most appreciated! Jake	jaketrsw (15733)
884271	2010-05-12 13:10:00	To break down the statement For Each hl In ActiveSheet.Hyperlinks... ActiveSheet refers to the currently selected sheet. In the example code you provided Master Index & Search is the active sheet as it is selected earlier in the code with Sheets("Master Index & Search").Select ActiveSheet.Hyperlinks refers to the Hyperlinks object collection, representing all hyperlinks on the currently selected sheet. For Each hl In refers to each individual hyperlink. Most objects within Excel are individual items but are inherently also part of a collection of the same object type, so you can assign a variable like hl and Excel will know your wanting to refer to an individual object when you use For Each, which is a method of looping through a collection. Now that's explained, what do you actually want to do Jake? Perhaps if you explain what your trying to achieve I can write some code thats more specific to your needs. regards, Graham	Parry (5696)
884272	2010-05-12 21:38:00	Graham, Thank you for explaining the ActiveSheet . Hyperlinks bit in detail - I have a better understanding of it now . What I'm trying to do is provide a Macro Button that will allow a user to enter a section of a file path (such as Documents\ Jake \) and change it to a new path (such as Documentrs\Graham\) . The macro I've constructed works but only for some of the hyperlinks in the document . What I've subsequently discovered through troubleshooting with a friend who had Office 2007 is that for some reason some of the file paths had forward slashes (/) instead of back slashes (\) in the file paths . Office 03 shows them as being the same, even though the source data is different - whereas 2007 doesn't (I think . . . ) . The function, even though it knows its changing a hyperlink path, treats the find/replace string as a text string thus differentiating between a / and a \ . Hence it only changes those paths that contain the \ and not the ones with a / . The work around is to first change all the slashes to the same (since the hyperlinks work both ways) using the function - then make the change to the paths that I want to . However - this does not answer the blindingly obvious question - why are the paths using different slashes? I can 100% guarantee that all the hyperlinks were inserted using the exact same method because there is a macro function that dictates how it is done (its a guide that uses the insert hyperlink dialog box to browse for files), plus I did them all myself . So - is there any way to ensure that the insert hyperlink function uses the correct slashes, and if not is there any way to make the code not differentiate between the two different types? Thanks for your reply Graham, your help is most appreciated . Jake	jaketrsw (15733)
884273	2010-05-13 07:04:00	Hi Jake, I've seen the use of file://C:\List.txt to link to a file, so if you entered that directly into a cell in xl2007, it automatically creates a hyperlink and the address would read C:\List.txt but the link display in the cell is file://C:\List.txt. Perhaps that's what your thinking of? I would have to look at xl2003, but if you think it's managed differently then use Application.Version which returns a number that tells you what version of Excel is being used, then the code can act accordingly. Is the problem with the code you posted (I havent tested it) that it's only looking in one sheet and you want to loop through the rest, or is it that it works in either xl2003 or 2007 and not the other version? cheers, Graham	Parry (5696)
884274	2010-05-13 21:29:00	Graham, The problem is not that it works in 03 and not 07 or vice versa (I think) . Its that the paths for the hyperlinks in the ActiveSheet . Hyperlinks vary between: "R:\Documents\Jake" and "R:\Documents/Jake" . The discrepancy only shows up in 07 for some reason, and how it managed to put different slashes in the paths is beyond me . I only need the funciton to work for the active sheet, and it does . The workaround (ie changing all the /'s to \'s which I've now written into the code using the same find & replace style function) works fine . I want to thank you for your help, its an odd problem and since I've found a workaround for it I would encourage you not to lose any sleep over it unless for the sake of furthering your own knowledge you are compelled to look into why some of the slashes were forward and some were backwards . Kind Regards, Jake	jaketrsw (15733)
1