Need To Change Link via Macro

[GA81848]

I have a spreadsheet called Master which is currently linked to a file called Customer. In another directory I have over 500 files which are identical in format as the Customer file.

I need to change the link of the Master file by clicking on a button then being able to select the folder and then the file I want to link to. Is this possible and if so how?

I've tried searching this forum but am unable to find a solution.

 

[GA81848]

Anyone have any ideas on this?

 

[Yard]

Don't think you need anything special for this.

Go to the Edit Links dialog box, select the link you want to change, click Change Source and navigate to the new file you want to link to.

 

[GA81848]

I appreciate your reply, but I need to set up some other method as all the menus/taskbars have to be locked.

I had thought about something on the lines of:-

1. An input box so they name the file they need to link to.
2. Followed by changing the directory some how.

Unfortunatley I still cannot fathom this out?

 

[jasonb75]

Am I right in thinking that your sheet contains a number of formula that would reference data in the selected file, meaning you need to change the filename in multiple cells?

If I'm right then I would import a directory listing into another sheet, then insert a dropdown box to allow the user to pick the filename to reference from this list. (this is untested theory).

The downside is that this would mean you would need to rewrite the formula in the sheet as indirect.

@YARD. do you think this would actually work? I've not tried using indirect with an external source file previously.

 

[Yard]

INDIRECT will only work if the source file is open. Nice idea though - the OP can decide if the source file will be open.

If not, try this as a hashed version of the Edit Links idea. You'll need:

- a UserForm (in my example called ufmListLinks)
- on that UserForm a ListBox (in my example called lstBox1)
- on that UserForm a Command Button (in my example called btnOK)

Double click the Command Button and paste this into the code window:

Option Explicit
Private Sub btnOK_Click()
strLinkOld = lstBox1.Value
Unload ufmListLinks
End Sub

Now in a standard code module paste the following:

Option Explicit
Public strLinkOld As String
Sub UpdateLinks()
Dim strNewFile As String, vLinks As Variant, lngLinkCount As Long
 
vLinks = ThisWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not IsArray(vLinks) Then Exit Sub
 
For lngLinkCount = LBound(vLinks) To UBound(vLinks)
    ufmListLinks.lstBox1.AddItem (vLinks(lngLinkCount))
Next lngLinkCount
ufmListLinks.Show vbModal
 
 
strNewFile = Application.GetOpenFilename
If strNewFile = "False" Then Exit Sub
 
ThisWorkbook.ChangeLink Name:=strLinkOld, NewName:=strNewFile, Type:=xlExcelLinks
 
End Sub

Run the UpdateLinks code and you should firstly get the userform displaying all the existing links. Choose one, click OK and you should then get a file dialog box. Choose the new link and click OK.

Have only tested it a little bit so see what happens.....

PS. Always test on a backup copy!

 

[GA81848]

Thanks for the response, but the code stops at "strLinkOld = lstBox1.Value" and is highlighted in yellow.

I've managed to come up with this piece of code (shown below) that works so long as the Name and NewName are not the same.

ActiveWorkbook.ChangeLink Name:=Range("A1").Value, NewName:=Range("A2").Value, Type:= _
xlExcelLinks

Range("A1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents

 

[Yard]

So the list box gets populated with the existing file links, but you get an error when you have selected one and clicked OK?

Give me some more detail rather than just "the code stops".

That's assuming you want a working solution as per your original request