VBA code for updating one specific linked file

[DutchKevin]

Hello,
I'm looking for a way to refresh the "ABC file" that my workbook is linked to.
The new ABC file will always be in the same folder and have specific naming convention. Like "ABC Week2013_03.xlsm"
My workbook also has links to other files, but these remain unchanged.
So far i've been able to come up with:

            Sub UpdateABC()
            Set ThisBook = ActiveWorkbook
            ChDrive "S:\"
            ChDir "S:\ABC files\"
            LatestDTR = Application.GetOpenFilename(Title:="Please choose the most recent ABC file", FileFilter:="XLS Files *.xls* (*.xls*),")
            If LatestABC = False Then
                MsgBox "No file specified.", vbExclamation, "Error"
            Else
            'Workbooks.Open Filename:=LatestABC 'for checking the file if needed
            End If
            ThisBook.Activate
            ActiveWorkbook.ChangeLink "[COLOR=#ff0000][I]I need a way to get the current linked ABC file name here[/I][/COLOR]", LatestABC, xlExcelLinks
            'ActiveWorkbook.UpdateRemoteReferences = True

            End Sub

Can anyone suggest a way to make the current ABC file name into a variable? It has always a certain filenaming format ("ABC Week2013_03.xlsm") so it must be quite easy to filter out of the linked files list. But How??

Thanks in advance

Kevin

 

[Andrew Poulsom]

You can loop through the LinkSources of Type xlExcelLinks and use Instr to check that the name contains eg "ABC Week". If it does that's the current link.

 

[DutchKevin]

hi andrew, thanks
I'll have a try tomorrow, using your earlier post http://www.mrexcel.com/forum/excel-questions/269650-visual-basic-applications-loop.html as a start. I have to figure it out