update links via vba

[daveyc18]

hi is there a way? i recorded but it's not working


ActiveWorkbook.UpdateLink Name:= _
"c:\Rpt\CORP\\Rep_Link.xls", Type:= _
xlExcelLinks

 

[sijpie]

Try:

ActiveWorkbook.UpdateLink Name:= "c:\Rpt\CORP\Rep_Link.xls", _
                                     Type:= xlExcelLinks

You had a double \ in your path

 

[daveyc18]

Try:

ActiveWorkbook.UpdateLink Name:= "c:\Rpt\CORP\Rep_Link.xls", _
                                     Type:= xlExcelLinks

You had a double \ in your path

corrected it.....says "method updatelink" of ojbect workbook failed

 

[sijpie]

Read this post on Ozgrid, particular the answer from Ger Plante. It may answer your problem

 

[sijpie]

Have you tried

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

it will update all linked workbooks.

If that works OK, then there is something wrong with the path you supply.

If it works, but you only want to update using one of the workbooks and not the others, then you can loop through each and do only the one:

Dim link as Workbook, LinkSources
linkSources = ThisWorkbook.linkSources(xlExcelLinks)
If IsArray(linkSources) Then
    For Each link In linkSources
        If Instr(Link.Name,"Rep_Link") Then  
            ThisWorkbook.UpdateLink Link.Name, XlLinkType.xlLinkTypeExcelLinks
            Exit For
        End If
    Next
End If

 

[daveyc18]

Have you tried

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

it will update all linked workbooks.

If that works OK, then there is something wrong with the path you supply.

If it works, but you only want to update using one of the workbooks and not the others, then you can loop through each and do only the one:

Dim link as Workbook, LinkSources
linkSources = ThisWorkbook.linkSources(xlExcelLinks)
If IsArray(linkSources) Then
    For Each link In linkSources
        If Instr(Link.Name,"Rep_Link") Then  
            ThisWorkbook.UpdateLink Link.Name, XlLinkType.xlLinkTypeExcelLinks
            Exit For
        End If
    Next
End If

first macro worked fine..thanks!