VBA code to open the edit links option box

[lavoo]

Does anyone know how to write a simple VBA code to open the edit links option box under data - connections? I wish to have this code run and the edit links box pops over for user to select their option.

Any help would be great! Thanks!

 

[ww4612]

what is "links option box" and "links box pops"?

 

[lavoo]

It's the option under Excel --> Data --> Connections --> Edit Links, then a box pops up with "Change Source, Update Values etc. " options.

 

[lavoo]

what is "links option box" and "links box pops"?

Hi! It's the option under Excel --> Data --> Connections --> Edit Links, then a box pops up with "Change Source, Update Values etc. " options.

 

[ww4612]

i have never use that function before. are you familiar with marco recoder? you can record the macro when manually edit the link. then stop the recoder and edit the code. you may need to create a user form to facilitate the editing

 

[lavoo]

I tried using the macro recorder but no script shows up.

 

[ww4612]

it works for me. when i add a new connection. the recorded code is

    Workbooks("Book1").Connections.AddFromFile _
        "C:\Program Files\Microsoft Office\Office14\QUERIES\MSN MoneyCentral Investor Currency Rates.iqy"

and the code to change the connection is

    With ActiveWorkbook.Connections("MSN MoneyCentral Investor Currency Rates")
        .Name = "MSN MoneyCentral Investor Currency Rates"
        .Description = ""
    End With
    ActiveWorkbook.Connections("MSN MoneyCentral Investor Currency Rates").Refresh

 

[lavoo]

There are existing multiple connections built in the file. Objective is remind users to update the various connected files by "changing" their source (rather than refreshing connections).
Therefore the names of the existing connections is not constant.

I tried using

Sheets("Edit Links").Select
Application.Dialogs(xlDialogChangeLink).Show ActiveWorkbook.LinkSources(xlExcelLinks)(1)

But this led me straight to the change source window, instead of the window prior to that (edit links), where I can choose which external file to change from.

 

[mikerickson]

Perhaps

Application.Dialogs(xlDialogUpdateLink).Show

 

[lavoo]

First of all thanks for your recommendation, Mike.
Tried that but error message pops up indicating "class of dialog failed".

In addition, my objective is to show the Edit Link pop up box (which allows user to select any existing external linked file to change source),
instead of going straight to the select the name source file.

Any other suggestion perhaps?