Edit Excel LInk for cell (not worksheet or workbook)

Using ActiveWorkbook.ChangeLink I can change all the links inthe workbook to a new source.  This is not what we want.  I need to be able to change the ActiveCell ONLY.  Worksheets have cells that link to multiple workbooks outside the active workbook - if I edit a link I want to edit th elink using a button on the worksheet for just the activecell.

I am not using hyperlinks.

This code is for all - not ACTIVECELL

    lnk = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(lnk) Then
        varNewLink = Application.GetOpenFilename("Excel Files (*.xl*;*.xls;*.xlsx;*.xltx), *.xlsx")
        If varNewLink <> False Then
            For i = 1 To UBound(lnk)
                ActiveWorkbook.ChangeLink Name:=lnk(i), NewName:=varNewLink, _
                Type:=xlExcelLinks
            Next i
        End If
    End If
BirdWatchersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
You could try using a the replace function to edit the formula directly.

ActiveCell.Formula = Replace(ActiveCell.Formula, "Old File.xlsx", "New File.xlsx")

Open in new window

0
BirdWatchersAuthor Commented:
How do you get the old file name from the cell? there are tons of cell swith links - each have different exetrnal cell names.  Take for example the current cell is pointing Book2 and I selected Book4 for the new file? Hoe do I get Book2?  I already get the new filename with varNewLink = Application.GetOpenFilename("Excel Files (*.xl*;*.xls;*.xlsx;*.xltx), *.xlsx").
0
BirdWatchersAuthor Commented:
How do we get the source of the active cell?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

BirdWatchersAuthor Commented:
I can't use the ActiveCell.Formula unless I can define the active cel's source ..  I have no idea what the source is to hard code it as you did in the example. Please assist?
0
Michael FowlerSolutions ConsultantCommented:
How complex are your formulas. Could they have multiple instances of the reference to the other workbook?
0
BirdWatchersAuthor Commented:
The cells point to another workbook but the locations stay the same. The user canhave multiple versions of data in four different workbooks.  The cells will point to one of the four.  I jsut wnat to change the link from  say, book1 to book3.  They choose Book3 from the dialog box.  How do I get Book1 name to replace it?
0
Michael FowlerSolutions ConsultantCommented:
Here is a function to get the old link

Function GetCurrentLink(str As String) As String
    Dim startPoint As Long, endPoint As Long
    
    startPoint = InStr(str, "[")
    endPoint = InStr(str, "]")
    GetCurrentLink = Mid(str, startPoint + 1, endPoint - 2)
    
End Function


Sub test(newLink As String)
    ActiveCell.Formula = Replace(ActiveCell.Formula, GetCurrentLink(ActiveCell.Formula), newLink)
End Sub

Open in new window

1
BirdWatchersAuthor Commented:
I am getting a run-time error. Application-defined or object-defined error.

The active cell formula is
=[Book3.xlsx]Sheet1!$C$2

The formula I am using:
ActiveCell.Formula = Replace(ActiveCell.Formula, GetCurrentLink(ActiveCell.Formula), NewLink)

IUmmediate window values:
Old LInk  Book3.xlsx
New LInk C:\Users\deborah.silverman\Desktop\Book4.xlsx
0
BirdWatchersAuthor Commented:
The location of the cells will be the same - the workbook name will change.  the path may be there.  Whenthey choose a workbook they do so using a file dialog box that gets the entire path.
0
BirdWatchersAuthor Commented:
I was able to take the code from our expert comments and work it out - THANK YOU!

Sub RefreshLink()
    Dim NewLink, OldLink
    WBUnprotect
    NewLink = Application.GetOpenFilename("Excel Files (*.xl*;*.xls;*.xlsx;*.xltx), *.xlsx")
    NewLink = NewLink '& "!C2"
    OldLink = GetCurrentLink(ActiveCell.Formula)
    OldLink = OldLink '& "!C2"
    ActiveWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
    WBProtect
End Sub

Function GetCurrentLink(str As String) As String
    Dim startPoint As Long, endPoint As Long
    startPoint = InStr(str, "[")
    endPoint = InStr(str, "]")
    GetCurrentLink = Mid(str, startPoint + 1, endPoint - 3)
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BirdWatchersAuthor Commented:
the expert was able to guide me in the right direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.