Change Link Source Prompt

[ukkz001]

Is there a way to write a macro to bring up the "change link source" input box so the user does not have to click: Data>Edit Links>Change Source?

In addtiton to that, can the "change source" window default to a specified directory path?

Thanks,
Kevin

 

[GlennUK]

I'm sure that I've done this in the past ... I'll have a look for the dialog id and how to display it today ( unless someone else out there knows right now of course ).

 

[ukkz001]

Appreciate the effort Glenn.

Thanks,
Kevin

 

[GlennUK]

One more question ... will this be for choosing a link from the list, or will there only be one link in the workbook?

Actually this would do multi or single:

Sub linktest()
    ChDrive "W"             ' set the current Drive letter - CHANGE AS REQUIRED
    ChDir "\Applications"   ' set the working directory    - CHANGE AS REQUIRED
    alink = ThisWorkbook.LinkSources
    If IsEmpty(alink) Then
        MsgBox "No links found"
    ElseIf UBound(alink) = 1 Then
        Application.Dialogs(xlDialogChangeLink).Show alink(1), "", 1
    Else
        Application.Dialogs(xlDialogOpenLinks).Show
    End If
End Sub

 

[ukkz001]

It will be for the user to click the Change Source button and browse out on our network to choose the source.

Thanks,
Kevin

 

[GlennUK]

OK ... have a look at my previous reply ... I altered it after posting to give a solution.

 

[ukkz001]

most of the code is working however, when the dialog box appears and I select the file to update the Source Links, the program hangs and becomes unresponsive and I have to kill the Excel process.

I think it is doing this because it leaves the the prior link source and then essentially there are two link sources and it doesn't know what to do with them.

 

[GlennUK]

How many links do you think you are starting off with?

Are they normal Excel files that are linked?

Which dialog is shown when you run the code?

 

[ukkz001]

Nevermind the Source File I was selecting during the macro testing was locked for editing by another user.

Thanks so much for your time, you helped me out big time.

Cheers,
Kevin

 

[GlennUK]

Phew, am glad it's working.