Link source change

[optimuspye]

Hi
I have a problem with a linked file that I need to change filepath from user to user.
The cell range will remain the same as it is the same document for every user but the file path will be different for every user.
Is there a way I can change the filepath for the link to one that is typed in a cell but keep the range the same. I've tried editing the link through connections but there is no option to do what I want.
Any help would be greatly appreciated

 

[alora]

From what I understand, just username will change, otherwise path will remain same?
Use Environ$ fucntion to get current windows user nickname and make it as variable?

 

[optimuspye]

Thanks for your reply
im sorry but i dont think i explained my self correctly, i will try again.
The source document everyone has there own copy on their own pc,(this is the same document whoever has it)
I need to send out a copy of the recipient document that i have created but then use an easy way for the new user to link their own copy of the source document to the new document i have sent out.
the range of data to be linked is the same for each end user as the document is the same just not the location.

Ive used a macro for the end user to select the filepath of their own source document and pastes the filepath to a cell:

Sub GetFilePath()

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FileSelected = .SelectedItems(1)
End With

Sheets("Daily Stats").Select
ActiveSheet.Range("x2") = FileSelected
End Sub

I would like this filepath to change in the linked source location to that in the cell where the macro pasted it and keep the range ref info as this would be the same for all users.