Using VBA to Change Drive Letter in Edit Links

[SuperKrishna]

We've recently switch drive letters and I've written some code to open files, search and replace drive letters then save. What I would like to add is the ability to replace drive letters in the "Edit Links" section for all external links in the file.

I have a range, starting in K12 that contains a list of pathfilenames that Excel will sequentially open then perform a search and replace. Before the ActiveWorkbook.Save, I would like to edit all links and switch from the previous path "S:Act" to the new path "X:Act". Any insight would be appreciated.

#################################

[pre][code]Sub UpdateLinks()
Dim ws As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Range("K12").Select
Do While Not (IsNull(ActiveCell.Value))
Workbooks.Open Filename:=ActiveCell.Value
Cells.Replace What:=Range("strOldPath").Value, _
Replacement:=Range("strNewPath").Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveCell.Offset(1, 0).Select
Loop
Application.DisplayAlerts = True
End Sub

[/pre]
[EndCode]#################################

 

[Luke M]

I think you can just loop through all the links and check them.

[pre]

Sub UpdateLinks()
Dim ws As Worksheet
Dim OldLink As String
Dim NewLink As String

On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("K12").Select
Do While Not (IsNull(ActiveCell.Value))
Workbooks.Open Filename:=ActiveCell.Value
Cells.Replace What:=Range("strOldPath").Value, _
Replacement:=Range("strNewPath").Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

'New section of code
For Each l In ActiveWorkbook.LinkSources
OldLink = l
If Left(OldName, 7) = "S:Act" Then
NewLink = "X:Act" & Mid(OldLink, 8, 999)
ActiveWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
End If
Next l

ActiveWorkbook.Save
ActiveWindow.Close
ActiveCell.Offset(1, 0).Select
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Also, be careful about using a On Error Resume Next command for the whole macro. Generally, you want to only have that active for the lines that you know will cause problems, like

On Error Resume Next
Workbooks.Open Filename:=ActiveCell.Value
On Error Goto 0

[/pre]
That way if an unexpected error occurs somewhere else, you'll see it.

 

[SuperKrishna]

Little bit of tweaking and it works like a charm.

 

[Luke M]

Awesome! Hope the tweaking wasn't too much trouble. =)

 

[Jack]

Hi SuperKrishna


I really like Luke's code and this is an alternative that can work with it.


I faced this problem with a new HP Laptop - HP took up Drive E with HP Recovery software and all my VBA for opening files assumed E for an External HDD which I tend to use for all data. But sometimes I have to work on the old laptop so I need the path to be E again. My approach is for the User to choose the valid Drive at the start of a session and this being a Public variable stays valid for the session. To get this in place I wish I had had Luke's code but I did a find and replace replacing "E: with SDrive & " . Note it tests the user's input for the most common drives which you could expand for X Z. Just another approach I guess.


Public sDrive As String


Sub ChooseDrive()

sDrive = InputBox("What drive letter has the External Hard Drive loaded as?", "Important Notice!", "G")

TryAgain:

If sDrive = "E" Or sDrive = "F" Or sDrive = "G" Or sDrive = "H" Or sDrive = "I" Or sDrive = "J" Then

sDrive = sDrive & ":"

Exit Sub

Else: sDrive = InputBox("You have entered an invalid response, and need to try again...", "Try Again...")

GoTo TryAgain

End If

End Sub