PDA

View Full Version : Update links when file names are different each time....



kellymarie
12-03-2007, 08:21 AM
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
12-03-2007, 11:28 AM
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
12-04-2007, 03:10 AM
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
12-04-2007, 03:47 AM
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
12-05-2007, 03:27 AM
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
12-05-2007, 03:42 AM
I suspect it should be:

ChangeLinkAndCopy Cstr(vFilename(lCount))

kellymarie
12-05-2007, 04:17 AM
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?