Listing external links and modifying network path in VBA

[excel_user123]

Hi everyone,

Solution: I have a need for creating a simple message box that prompts a user with each mapped network drive in a file for an external link that shows them what the current drive is and a cell in the message box that lets them choose where the new drive needs to be mapped to. In other words I need to be able to change an external link's mapped network drive from whatever it currently is to what it now needs to be!

Problem: We are on-boarding a new company and they are going to have their Microsoft Office files brought into our network. I'm specifically concerned with their Excel files and any links that may be brought over in them. For example if they currently have a file that has an external link that is mapped like this:

='G:\Corporate\Administration\[Letters of Credit.xls]Sheet1'!$A$7

I want to be able to update, modify or change the mapped drive to this:

='T:\Corporate\Administration\[Letters of Credit.xls]Sheet1'!$A$7

Is this even possible? I don't want to have to go into find and replace and physically look for the file and re-link it as that will take forever. If someone knows some simple code that will search for the links for me and create a message box that shows what the current location is and a spot in that message box to simply put in a letter and it changes the file path for me, that would be simply amazing!

I cannot wait to see someone come up with this solution. You guys are great!

Thanks,

excel_user123

 

[excel_user123]

I should also add that I'm looking to have each search highlight the cell when it finds a link and once you change each individual external link the message box updates indicating that all changes have been made.

If the user wants to run through the same process and modify again the same links this same process should have no restrictions on their ability to do it over and over again.

Thanks again!

 

[excel_user123]

I should clarify again....

Only after all the changes are updated the message should say "All changes have been made!"

Also once the letter is selected for what drive it should be there should be an OK beside the cell and it automatically changes things from the "G" drive to the "T" drive as in my example. I just need it to change the letter reference in the network drive and the field should be forced as a capital and not lower case.

Thanks again!

 

[RedBeard]

This is the tool I used when migrating servers once: ExcelPipe Find and Replace for Excel

 

[sericom]

Do you want to change all links to the same new drive letter, or do you want it to ask for a new drive letter for each link?

 

[excel_user123]

Do you want to change all links to the same new drive letter, or do you want it to ask for a new drive letter for each link?

Thank you for getting back to me so quickly folks!

Great question. I would like to have the flexibility to change each one individually in case the files are for whatever reason located on a different drive.

Thanks!

 

[sericom]

This should cycle through the links in a workbook and change the drive letters

Sub adjustLinks()
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            dLetter = InputBox("Choose new drive letter for link:" & vbNewLine & aLinks(i), "Link updater")
            nName = UCase(dLetter) & Right(aLinks(i), Len(aLinks(i)) - 1)
            ThisWorkbook.ChangeLink aLinks(i), nName, 1
        Next
        MsgBox "All links updated"
    Else
        MsgBox "No links found"
    End If
End Sub

 

[excel_user123]

This should cycle through the links in a workbook and change the drive letters

Sub adjustLinks()
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            dLetter = InputBox("Choose new drive letter for link:" & vbNewLine & aLinks(i), "Link updater")
            nName = UCase(dLetter) & Right(aLinks(i), Len(aLinks(i)) - 1)
            ThisWorkbook.ChangeLink aLinks(i), nName, 1
        Next
        MsgBox "All links updated"
    Else
        MsgBox "No links found"
    End If
End Sub

Wow this is fantastic! I haven't had the chance to use the code yet but I will first thing in the morning. I'm so glad to know to there are good folks out there willing to help us other folks out who are in need of very useful things! I'll be sure to let you know how it turns out as soon as I run it!

Thank you very much!

 

[excel_user123]

This should cycle through the links in a workbook and change the drive letters

Sub adjustLinks()
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            dLetter = InputBox("Choose new drive letter for link:" & vbNewLine & aLinks(i), "Link updater")
            nName = UCase(dLetter) & Right(aLinks(i), Len(aLinks(i)) - 1)
            ThisWorkbook.ChangeLink aLinks(i), nName, 1
        Next
        MsgBox "All links updated"
    Else
        MsgBox "No links found"
    End If
End Sub

So this works well! It's doing what I need it to do but it doesn't loop through each individual link and go through each link one at a time. Is it possible to do this?

I also noticed that as soon as you change or update the link it tries to open the "Change Source..." section that you would normally use when updating manually the new location of where the file resides. Is there a way for it to not open this screen each time a link is updated or is this default Excel behavior? As soon as you hit cancel it basically updates the links and maybe this can't be changed but if it can that would be awesome!

I'm guessing there needs to be a loop here that runs through things one at a time?

Thanks again!

 

[excel_user123]

I should add to this for further clarification...When you make the change in the mapped network drive it treats references in the same workbook as one entire change. This is why I mention above that if possible it would be excellent if it would go through each external link one at a time to provide the flexibility for those who will need to use this macro.

So if I have a two or more linked locations referenced from the same file path or worksheet and make a change it just makes the change for them without asking me one by one. I hope this helps to clarify!

Thanks again!