VBA - Change linked file

[Hadj]

I require the VBA code for the user to choose and then change a linked file

[Richard Buttrey]

Hi, and welcome to the forum.

Try

Please Login or Register  to view this content.

[Hadj]

Thanks Richard but not what I require
I am teaching myself VBA and am perhaps not explaining myself clearly

I have a workbook "A" which is linked to Workbook "B"
The user is working in workbook "A" and the code already written requires them to change the link from workbook "B" to one chosen from the same dir

[Richard Buttrey]

Hi,

In that case, untested but try this slight modification

Please Login or Register  to view this content.

[Hadj]

This makes more sense to me but does not work fully
I can see how the code will get the "NewName"
How does the code get linked "CurrentFileName"?
Also does the "NewName" file have to be opened?

[Hadj]

I have tried this - the new "NewName" seems work as I have to choose and open
After that the code breaks down - I think it is the "stCurrentFName=ActiveWorkBook.Name" as this should actually refer to the "linked file" and not the "current workbook"

[Richard Buttrey]

Sorry, I did say it was untested!

Try instead

Please Login or Register  to view this content.

...assuming there is only one link in operation.

No, the NewName File does not need to be opened. The instruction GetOpenFileName does as it says on the tin. When the user points to it in the usual windows file dialog box it's assigned to the variable when you click on the 'Open' buttton - which I agree is confusing since it doesn't actually open the file. I suspect MS were just being lazy and used the same FileOpen dialog box as you would use if you do want to open a file.

[Hadj]

Still not working
Does the "stCurrentFName" for the link file not also give the path which would then change the last line of code?
I am going to try this again

[Hadj]

Hi Richard
I have taken the "Path" out of the code and it now works just as I want it to
Here it is for your (or other users) records

Dim stCurrentFname As String, stNewFName As String, stPathName As String
stCurrentFname = ThisWorkbook.LinkSources(XlLink.xlExcelLinks)(1)
stNewFName = Application.GetOpenFilename
ActiveWorkbook.ChangeLink Name:=stCurrentFname, NewName:=stNewFName

This code changes the "Linked File" to the "New User Chosen File"

You are correct that the code only picks the first "Linked File" - This is fine for me as there is only one Linked File at any one time
However I tried it with two "Linked Files" and when I changed the (1) in "stCurrentFName" to (2) the code picks up the second file

Thanks a lot
You have helped solve the problem I have been battling over for a few weeks now

[Hadj]

Hi Richard
Me again - I am also new to ExcelForum - how do I rate the service I have received?
Thanks again
Hadj

[Richard Buttrey]

Hi Richard
Me again - I am also new to ExcelForum - how do I rate the service I have received?
Thanks again
Hadj

I guess you found it. And thanks for the rep points.