Update links when file names are different each time....

[kellymarie]

Hi!

I am trying to write some code to automatically update links within a workbook;

I have a workbook with links to just one other workbook and I would like the code to go through each workbook within a specified folder and update the links one by one for each of them. Ideally after updating the links i would like it to paste the information on another sheet and do the same for every one.

However - the workbook names will always be different so I want to just point it to a folder?...

Is this possible? I know you can updatelinks when you know the name but can you when you do not?

I hope that all made sense!!
Thank you!!!

[Jan Karel Pieterse]

Well, for starters you could use this code to select and run through the files:

Sub GetOpenFileNameExample3()
    Dim lCount As Long
    Dim vFilename As Variant
    Dim sPath As String
    Dim lFilecount As Long
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to open", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = 1 To UBound(vFilename)
        Workbooks.Open vFilename(lCount)
        'Do something with the workbook here, like:
        'Change link, save and close
    Next
End Sub

[kellymarie]

Thanks Jan,

However i dont want to change multiple links at once;

The orginal workbook has links to just one other file, I want to change this source (There are about 90) to each file within a folder copy and paste then move onto the next file etc etc?

[Jan Karel Pieterse]

OK.

Replace this part:

Workbooks.Open vFilename(lCount)
'Do something with the workbook here, like:
'Change link, save and close

With:
ChangeLinkAndCopy vFilename(lCount)

And add this routine:

Sub ChangeLinkAndCopy(sNewLink As String)
    Dim vLink As Variant
    Dim vLinks As Variant
    vLinks = oBook.LinkSources(xlExcelLinks)
    If IsEmpty(vLinks) Then Exit Sub
    Application.DisplayAlerts = False
    oBook.ChangeLink vLink(1), sNewLink, xlLinkTypeExcelLinks
    Application.DisplayAlerts = True
    'Now here comes your copying code
End Sub

[kellymarie]

Thanks Jan, I have given this a go but I seem to be getting an error on;

ChangeLinkAndCopy vFilename(lCount)

any ideas?

Thanks!

[Jan Karel Pieterse]

I suspect it should be:

ChangeLinkAndCopy Cstr(vFilename(lCount))

[kellymarie]

Hi Jan,
This still does not work?
Is there a way of Changing the Source link to each file within the folder without having to open the folder?