Macro to change source to external linked files with multiple file links

Longs

New Member
Joined
Dec 29, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hello all - longtime lurker, first post! I have five different Excel "source" files with different structures, locations of data, that create financial projections for different products. The five source files I want to combine information from the five source files into a single "consolidated" Excel file using external links. I then want users to be able to update the links via Macro, so that when they need to update, all they get is a "file open" dialog that they can use to navigate to the new source file or files. Essentially I am using a macro to replace the "Data", "Edit Links", "Change Source" then select the link and navigate to the new source file or files. I cannot combine the five source files into one, they are used by different audiences with different levels of knowledge, not a feasible approach at this point.

Using information from this site, the macro below works when there's only one External Link source, but if I have multiple sources, it attempts to update all of the external links from the five different source files with a single new source file, which doesn't work. The premise of the macro was to go to a cell with a link to one of the five source files (the named range "CentPharm") then change the link name by having the user navigate to a directory and file, click the name and it updates. The premise was that when updating the link manually (via Data, Edit Links) every link to the source file is updated if ANY link is updated. The manual way works fine if there's more than one source file, but the macro tries to update every link to all of the different source files with the name chosen from the dialog.

Sub UpdateCentralPharmacy()
Dim varNewLink As Variant
Dim lnk As Variant

Application.Goto Reference:="CentPharm"
' get all links
lnk = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(lnk) Then
' prompt for the new file for the link
varNewLink = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
' if user didn't cancel, refresh the link
If varNewLink <> False Then

ActiveWorkbook.ChangeLink Name:=lnk(1), NewName:=varNewLink, _
Type:=xlExcelLinks

End If
End If
End Sub

I don't want to "loop" all five source files, which doesn't seem possible anyway. My plan was to have five unique macro that would update each of the five corresponding source files when they're run, so that if a user only needed to update the external links for source files 1 and 4, they'd run Macro 1 and Macro 4.

Thanks in advance for any thoughts anyone can share!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top