vba excel file

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA - Excel file links

VBA - Excel file links

resolvedResolved · Low Priority · Version 2007

Peter has attended:
Excel VBA Intro Intermediate course

VBA - Excel file links

I have a mission updating external spreadsheet links using the (data - edit links) funcition in excel.

The spreadsheet links to approx 100 other excel files, all of which are stored in the same directory but within a different monthly file. When I use the "edit links" function I can highlight all the files and "update links" but when "changing source" I am forced to edit each file one by one.

Ideally I would like to have a marco, that allows me to hold the external link names on a new sheet, which I can then edit and then process with the marco to update the external links

Im sure this is possible, but not sure where to start?

RE: VBA - Excel file links

Hi Peter, thanks for your query. A very interesting request, if I may say so. I can't write the whole code for you, but here's what I'd suggest.

You're going to need two subroutines. One to loop through all the links and dump them onto another sheet. Another to restore your edits to the original sheet.

This is how you loop through links:

-----------------------

Sub ListLinks()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub

-----------------------

Your problem then becomes how do you marry up your subsequent changes with the original linked cell? For that, I would include the address of the cell with the original link in it when I exported the links. That way, it becomes a simple task of looping through your edits and marrying them back up to the original cells.

If you get this up and running, turn the thing into an Add-In because it's the sort of thing other people will want!

Hope this helps,

Anthony

Wed 8 Aug 2012: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Move to edge of data block

When data is held in a block, however large, use the Ctrl key with your cursors to move quickly to the far edge of the block. Works with all 4 direction keys.

View all Excel hints and tips


Server loaded in 0.07 secs.