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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,089,475
Messages
5,408,469
Members
403,208
Latest member
JFoley182

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top