Solved: Macro extract external links from Edit>Links and update

[xluser2007]

Hi All,

At work, often I find myself manually updating external links for large workbooks using Edit Links sthen selecting link and Changing source to the new target link.

I want to design a macro to do the following:

1. In the activeworkbook, I want to extract external link sources from the Edit> Links in a new worksheet called "External links Actwksht" column A in the Activeworksheet. These are essentially the source links required to be updated.

2. Then I column B I will update the equivalent target links to update these to for each of the corresponding links extracted in Column A e.g. source link in A1 as extracted is C:\Feb2008.xls then I will enter C:\Mar2008.xls in B1 manually as the target link to update to.

3. The second macro should open each of the target links and then simply carry out the manual update procedure through a macro i.e. Edit Links Change source link (Column A) to Target link (Column B).

- The target links (workbooks) in Column B should be opened using VBA to increase efficiency of links update).

Any help on helping me carry this out is much appreciated, as I'm quite confused how to go about this one.

[Bob Phillips]

Bill Manville has written a utility, FindLink, for working with links. You can get it at http://www.oaltd.co.uk/MVP/Default.htm

[xluser2007]

Hi Bob,

Thanks for your reply. I just checked out the Addin.

I applied to a file with 5 links e.g. C:\SpreadsheetA.xls, D:\SpreadsheetB.xls, E:\SpreadsheetD.xls etc.

When I applied the utility on the workbook it gave a very helpful detailed summary of all the graphs series that came from these links and the associated formulae. This is useful for other use, but npot quite for my purpose.

The output I require is essentially in a new worksheet,

A1 = C:\SpreadsheetA.xls,
A2 = D:\SpreadsheetB.xls
A3 = E:\SpreadsheetD.xls

etc

Maybe I'm not using it correctly. I just selected Tools>Findlink, And the second option, "No messages or deletion - just make a list of all the links found"

This didin't give the simplified output above. Is there a way to write the simplified macro. to extract just the high level links as above. I've searched all round but couldn;t find anything. Any guidance would be great.

Thanks again.

[Bob Phillips]

I have never used Bill's addin as I never use links, I just thougt it might do whtat you required.

I can try and knock you something up, but can you post a workbook for me to work with?

[xluser2007]

Hi Bob, thanks for your prompt reply.

I've attached a sample workbook with 3 worksheets sourcing external links from 3 locations (from 3 random files), just to give you an idea.

If possible, I just wanted to create ina new worksheet the listing of the 3 target links, namely:

A1 = C:\sumif_countif.xls
A2 = C:\Documents and Settings\All Users\Test with Notes.xls
A3 = C:\Documents and Settings\All Users\Folder1test\address.xls

Again, thanks for the offer to help, if there is a way to do this process I;m sure a lot of people who spend close to 2 hrs updating links would find it useful and effective.

regards,

[xluser2007]

Would be a useful thing if I attached the file!

[Bob Phillips]

Try this little addin

[Bob Phillips]

BTW, if you change the name in the List Links spreadsheet, it will do the update for you.

[xluser2007]

Hi Bob,

That was legendary, was that just a days work for you!

Exactly what I require. I'll now have a play as to the second part of my problem which is manually listing the update links in column B and writing a macro to change source (Column A) to target (Column B). Will keep you posted with any queries on this when they arise.

Thanks again, coming to this forum makes the improbable possible!

regards

[Bob Phillips]

I have already done it, not quite like that, but it can be done.

Just change the value of A2 and see the links update to that new value.

[xluser2007]

Hi Bob, I didn't realise earlier that you had achieved the second aprt of my question already withinh your addin, very skillful indeed.

However, in the case of allowing other people to chage their links, it is probably easier for them to input thier targetlinks in Column B next to the corresponding sourcelink in Columna (that come from your nifty addin). So that they (and also me ) can transparently see that we have replaced correctly.

The program, after extracting your links in columnA will look something like this:
[vba]
Sub Change_Links_In_ActiveWorkbook()

Dim Thiswbk as Workbook

Dim UpdateSht as Worksheet

Set Thiswbk = Thisworkbook

Set UpdateSht = Thiswbk.Sheets("Links Sheet")

For i = 2 to [number of links here]

Call Changelinks(UpdateSht.Range("A"&i), UpdateSht.Range("B"&i))

Next i

End Sub[/vba]
I can't figure out how to get the [number of links here] in teh amcro above i.e. how many links that are in the document as extracted from your addin.

Also an aside, when I first install your addin, I can use Ctrl+Shift+L. After first installation, I can't get teh shortcut to work. So I go through Alt + F11 and Run it through there and it Works a treat! I just wanted to check that it wasn;' an issue with my Excel settings that was causing me to do this.

Again, thanks for your time on this challenge.

regards

[Bob Phillips]

A few questions.

However, in the case of allowing other people to chage their links, it is probably easier for them to input thier targetlinks in Column B next to the corresponding sourcelink in Columna (that come from your nifty addin). So that they (and also me ) can transparently see that we have replaced correctly.

The way that I had it was to perform the update actively, that is as they made the change. Is this okay, or is your preference to have them add the new link and then sya do Ctrl-Shift-U to fire a batch type update of all changes

How about if the select column B, and column A is not empty, we throw up a folder dialog for them to pick the folder. They can't get a bad directory that way.

Also an aside, when I first install your addin, I can use Ctrl+Shift+L. After first installation, I can't get teh shortcut to work. So I go through Alt + F11 and Run it through there and it Works a treat! I just wanted to check that it wasn;' an issue with my Excel settings that was causing me to do this.

When you say after first installation, do you mean when you open Excel a second time? Di you get the message?

[xluser2007]

Hi Bob,

In reply to your questions.

The way that I had it was to perform the update actively, that is as they made the change. Is this okay, or is your preference to have them add the new link and then sya do Ctrl-Shift-U to fire a batch type update of all changes

How about if the select column B, and column A is not empty, we throw up a folder dialog for them to pick the folder. They can't get a bad directory that way.

I think the Batch type update to do all the changes would be super, once they added the link in column B. I was just trying to improvise some code to do the process in a not such a cool way.

When you say after first installation, do you mean when you open Excel a second time? Di you get the message?

Yes, this is precisely what happened. Basically installed it, and the Msgbox came up with the Ctrl+Shift+L message. Once this shortcut was used it worked fine. For all subsequent time I clicked the shortcut, no change was observed (until I ran it from the VBIDE).

Also whenever I opened a new Excel, I couldn;t get it to work using teh shortcut.

It works great though when you run it, I was trying to understand whether it was a setting issue in my Excel that caused the shortcut to fail.

regards,

[xluser2007]

In post #11, I forgot to add in the following bit of code that is called:

[vba]Sub Changelinks(Sourcelink As String, Targetlink As String)

ActiveWorkbook.Changelink Name:=Sourcelink, NewName:=Targetlink, Type:=xlExcelLinks

End Sub[/vba]

[Bob Phillips]

It works great though when you run it, I was trying to understand whether it was a setting issue in my Excel that caused the shortcut to fail.

No I think it was because I put it in the Addin install procedure not workbook open.

I think the logic should be

Addin Install: throw up the information message

Workbook Open: set the keyboard sortcut

Workbook Close: disable keyboard shortcut

[xluser2007]

Hi Bob,

I was just trying to get the above code to work to update the links, from posts #11 and #14.

The code is as below in full:

[vba]Sub Change_Links_In_ActiveWorkbook()

Dim Thiswbk As Workbook

Dim TargetlinkWbk As Workbook

Dim UpdateSht As Worksheet

Set Thiswbk = Thisworkbook

Set UpdateSht = Thiswbk.Sheets("Links Sheet")

For i = 2 To [number of links here]

TargetlinkWbk = Workbooks.Open(UpdateSht.Range("B"&i), UpdateLinks:=0)

Call Changelinks(UpdateSht.Range("A"&i), UpdateSht.Range("B"&i))

Next i

End Sub

Sub Changelinks(Sourcelink As String, Targetlink As String)

ActiveWorkbook.Changelink Name:=Sourcelink, NewName:=Targetlink, Type:=xlExcelLinks

End Sub[/vba]
I can't get the [number of links here] in the above code figure out i.e.e get the macro to work out how many rows of links there are to update. Any ideas on how to rectify this?

Note in the above code, the reason for opening is so that the links update is considerable faster. Is there a way to make it as fast without opeining the targetlink workbooks?

Bob, the batch file option you were suggesting sounds super, but this is update preocedure (once modified by your feedback) probably easier for most people at work to understand and apply and see the update more transparently.

Your valued thoughts/ guidance on the above code and querieswould be really appreciated.

[Bob Phillips]

I thought I had posted a new version, but it seems not

[xluser2007]

Bob,

Thanks so much for your revised Add-in, it works brilliantly (as expected ).

Thanks a ton. Always learn something new from you every time.

I'll mark it solved, but if I have additional queries, I'll keep you posted.

regards.

[xluser2007]

Hi All,

Bob had posted this awesoem add-in which lists and helps replace links in a spreadsheet.

Using the first part of Bob's addin, I can get a listing of all existing links in the new "Links Sheet" Wrokshet that teh addin creates.

For a particular workbook it lists the external links (as strings ) in A2:A5 i.e.e. 4 links.

In the corresponding rows in column B, I put in the new string paths to change to.

I wrote the following macro to open the targetlink workbooks first and then update links as follows:

[VBA]Sub Change_Links_In_ActiveWorkbook()

Dim Thiswbk As Workbook
Dim UpdateSht As Worksheet
Dim targetwbk As Workbook
Dim Sourcelink As String
Dim targetlink As String


Set Thiswbk = ThisWorkbook
Set UpdateSht = Thiswbk.Sheets("Links Sheet")

Debug.Print UpdateSht.Range("A2").Value

For i = 2 To 5

Set targetwbk = Workbooks.Open(UpdateSht.Range("B" & i).Value, UpdateLinks:=0)

ActiveWorkbook.ChangeLink Name:=UpdateSht.Range("A" & i).Value, NewName:=UpdateSht.Range("B" & i).Value, Type:=xlExcelLinks



Next i

End Sub[/VBA]

It keeps failing in the bold line with the error 'Method 'Changelink' of object '_Workbook' failed
.

Although Bob's Addin can achieve the above, i waould like to know why the above is failing.

Could anyone please shed light and help to correct this please.

[xluser2007]

Hi All,

I think as logic is concerened the macro is fine, all it doing is the following:

[VBA]ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks[/VBA]


The only issue, is that my workbooks have the same name but different paths e.g:

[VBA] ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel_folder_different\book1.xls", xlExcelLinks [/VBA]

that is the folders are different, but the booknames are not i.e. book1.xls.

Could anyone please explaina the workaround for this situation?

regards