My hyperlink address change?

[hyperlink]

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.

 

[JLatham]

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