CODEWITHSUNDEEP
vbaexcel
Luiza
Luiza

Reputation: 55

Excel VBA macro - Change link name

I have a excel macro routine that I need to prep a file for another routine which is run daily. This prepping involves changes the links of t-1 file to a t0 file.

The code I usually do is:

ActiveWorkbook.ChangeLink Name:= _
        "file path", NewName:=new_file_path, Type:=xlExcelLinks

My trouble now is that for this particular routine the file path to be changed to a new routine is not always the same, thus I would need a way to automatize finding out what are the current links to replace them all. The new file path I now because it is the worksheet that is calling this routine and opening this file, so first thing I do Is

new_file_path = "C:\...."& ActiveWorkbook.Name & ".xlsm"

What would help me is if there is a trick to replace all links for a new one, without the need to say the name/path of the old links. Does any one know?

Thanks

Upvotes: 0

Views: 17710

Answers (1)

EEM
EEM

Reputation: 6659

To change all the excel links in a workbook try this procedure:

    Sub WbThs_ChangeLink_Excel()
    Dim wbTrg As Workbook
    Dim sLinkNew As String
    Dim aLinks As Variant, vLink As Variant

        sLinkNew = "@@VBA Links Replace - Target 3.xlsb"   'Change as required

        Set wbTrg = ThisWorkbook   'Change as required

        Rem Set array with all excel links
        aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

        Rem Replace each excel Link
        If Not IsEmpty(aLinks) Then
            For Each vLink In aLinks
                wbTrg.ChangeLink Name:=vLink, NewName:=sLinkNew, Type:=xlExcelLinks

        Next: End If

        End Sub

See the following pages for additional information on the resources used:
Workbook.ChangeLink Method (Excel)
Workbook.LinkSources Method (Excel)
XlLink Enumeration (Excel)

Upvotes: 2

Related Questions