Listing external links and modifying network path in VBA

excel_user123

New Member
Joined
Jul 19, 2016
Messages
7
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

New Member
Joined
Jul 19, 2016
Messages
7
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

New Member
Joined
Jul 19, 2016
Messages
7
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!
 

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
891
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

New Member
Joined
Jul 19, 2016
Messages
7
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

Well-known Member
Joined
Jan 19, 2006
Messages
891
This should cycle through the links in a workbook and change the drive letters

Code:
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
 
Last edited:

excel_user123

New Member
Joined
Jul 19, 2016
Messages
7
This should cycle through the links in a workbook and change the drive letters

Code:
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

New Member
Joined
Jul 19, 2016
Messages
7
This should cycle through the links in a workbook and change the drive letters

Code:
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!
 
Last edited:

excel_user123

New Member
Joined
Jul 19, 2016
Messages
7
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!
 

Forum statistics

Threads
1,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top