Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
My hyperlink address change?
I have already make address in my excel worksheet into Network but suddenly
the address become change to c:\ prompt evenly my former address in Network..example: my original address--(In My Network Places) file:///\\Id740dc01\engineering\Drawings\Sales Drawings\... now suddenly become--file:///C:\Documents and Settings\gc000014\Application Data\Microsoft\Drawings\Sales Drawings\... Now, my big problems I must Hyperlink all worksheet in excel one by one with number of 3000 files, it's so hard. This is because of excel or else?Can I not hyperlink it one by one but mass hyperlink? please give me your advice and thank you. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
My hyperlink address change?
I'm not certain why your drawings are in Application Data, but we will assume
you have provided accurate information. Start by making a copy of your entire workbook so that if my code does not work properly, or the results are not correct, you will not have destroyed the original workbook with the code. There are some things in the code that you will have to change. You did not say what column these links are in, so I have just chosen column D to begin the code, change Const badLinkColumn = "D" to refer to the correct column: change "D" as needed. Also, change badLink and goodLink to be as each of those needs to be. Note that the space and underscore at the end of the first part of defining badLink tells Excel to treat the next row as part of that row. I broke the line there so that the editor here would not destroy the code. To put the code into the copy of your workbook: open that copy and press [Alt]+[F11] to enter the VB Editor. From the VB Editor menu, choose Insert and then choose Module. Copy the code below and paste it into the module that opens up. If you see any red text, it means something copied wrong and it will not run. Green, blue and black text is all good. Make the changes to the code that I have outlined above. Close the VB Editor and choose the sheet with the hyperlinks on it and then use Tools | Macro | Macros to select the RebuildHyperlinks macro and click the [Run] button. Test to see that the rebuilt hyperlinks work properly. Also, take a copy of the revised workbook to another system on the network and test to confirm that it works from other locations on the network. If you have any questions, post back here - the system will notify me that you have done so. Sub RebuildHyperlinks() Dim lastRow As Long Dim rOffset As Long Dim badLink As String Dim goodLink As String Dim newLink As String ' 'change to column with hyperlinks in it Const badLinkColumn = "D" 'change this phrase as needed to fix things 'just type in the part that must be changed badLink = _ "file///C:\Documents and Settings\gc000014\Application " & _ "Data\Microsoft\" 'change this phrase as needed to replace the badLink part goodLink = "file///\\Id740dc01\engineering\" lastRow = Range(badLinkColumn & Rows.Count).End(xlUp).Row - 1 Range(badLinkColumn & "D1").Select Application.ScreenUpdating = False For rOffset = 0 To lastRow If UCase$(Left$(ActiveCell.Offset(rOffset, 0), Len(badLink))) = _ UCase(badLink) Then newLink = ActiveCell.Offset(rOffset, 0).Value newLink = goodLink & Right(newLink, Len(newLink) - Len(badLink)) ActiveCell.Offset(rOffset, 0).Value = newLink ActiveSheet.Hyperlinks.Add anchor:=ActiveCell.Offset(rOffset, 0), _ Address:=ActiveCell.Offset(rOffset, 0).Value End If Next Application.ScreenUpdating = True End Sub "hyperlink" wrote: I have already make address in my excel worksheet into Network but suddenly the address become change to c:\ prompt evenly my former address in Network..example: my original address--(In My Network Places) file:///\\Id740dc01\engineering\Drawings\Sales Drawings\... now suddenly become--file:///C:\Documents and Settings\gc000014\Application Data\Microsoft\Drawings\Sales Drawings\... Now, my big problems I must Hyperlink all worksheet in excel one by one with number of 3000 files, it's so hard. This is because of excel or else?Can I not hyperlink it one by one but mass hyperlink? please give me your advice and thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
Hyperlink: Why am I getting ../../../ in the address bar? | Excel Discussion (Misc queries) | |||
multiple Hyperlink address change | Excel Discussion (Misc queries) | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
Hyperlink change address | Excel Discussion (Misc queries) |