Re: Updating Links on Excel

Giganews Newsgroups
Subject: Re: Updating Links on Excel
Posted by:  Ardus Petus (ardus.pet…@laposte.net)
Date: Mon, 22 May 2006

Assuming your monthly data file names end with mm.xls (eg: 06.xls for june),
apply following macro:

HTH
--
AP

'----------------------------------------------
Sub NewMonth()
    Dim sMonthId As String
    Dim rDept As Range
    Dim aLinks As Variant
    Dim iLink As Integer
    Dim sOldLink As String
    Dim snewlink As String

    sMonthId = Format(Range("A1").Value, "mm")
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For iLink = LBound(aLinks) To UBound(aLinks)
            sOldLink = aLinks(iLink)
            snewlink = Application.Replace( _
                    sOldLink, _
                    InStrRev(sOldLink, ".") - 2, _
                    Len(sMonthId), _
                    sMonthId)
            ActiveWorkbook.ChangeLink _
                Name:=sOldLink, _
                newname:=snewlink
        Next iLink
    End If
End Sub
'--------------------------------------------------
"Update Link Question" <Update Link Questi…@discussions.microsoft.com> a
écrit dans le message de news:
B1031577-66FB-4133-9E7D-75BC91AEB2…@microsoft.com...
>I have a spreadsheet that summarizes data from 300+ different workbooks.
> Every month a new file is created and we need to update the summary with
> the
> new month data.  Is there a better way to update the links on the
> summarizing
> spreadsheet?  Currently, we need to edit links and update links for each
> (300+) file name.  The only thing that changes in the file names is the
> month.
> Thanks!

Replies

None

In response to

Updating Links on Excel posted by Update Link Question on Mon, 22 May 2006