David_Wallis

Anybody got any apps / utils that can find linked excel spreadsheets and change them automatically??

Or any ideas on how this could be done?

David

David_Wallis

Any Ideas?

David

David_Wallis

think I maybe getting somewhere, this is what I have so far....

Sub Macro1()
alinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(alinks) Then
For i = 1 To UBound(alinks)

iStartPos = (InStr(1, alinks(i), "|"))
iLength = ((Len(alinks(i)) - iStartPos) - 2)
strfileName = Mid$(alinks(i), iStartPos + 1, iLength)

MsgBox "Link " & i & " location is:" & Chr(13) & strfileName
strNewLocation = InputBox("Enter New Location", "Change")

ActiveWorkbook.ChangeLink alinks(i), strNewLocation, xlLinkTypeOLELinks
Next
End If
End Sub

but it doesnt seem to actually change / refresh the link...

David

Crispin

Heres what I did, I created one sheet, and then linked a value to another dell in another sheet and added 3 to it for a simple formula......


Then I saved both sheets and moved the linked sheet to my D drive root.....


Then I implemented your code in a command button on the linker sheet, and stepped through it, I changed a couple of bits and it worked fine for me, changed the link(s) and saved em, then refreshed the sheet.....heres your code Version 2....
(What type of links are you changing exactly though - OLE or Excel - though you could loop the code for both of these types if you wanted)


Public Sub ChangeLinks()
alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(alinks) Then
For i = 1 To UBound(alinks)

'not sure why you are using the following three lines, they seem to chop off the filename for me........
iStartPos = (InStr(1, alinks(i), "|"))
iLength = ((Len(alinks(i)) - iStartPos) - 2)
strfileName = Mid$(alinks(i), iStartPos + 1, iLength)

MsgBox "Link " & i & " location is:" & Chr(13) & strfileName
strNewLocation = InputBox("Enter New Location", "Change")

ActiveWorkbook.ChangeLink alinks(i), strNewLocation, xlLinkTypeExcelLinks
ActiveWorkbook.Save
ActiveWorkbook.RefreshAll
Next
End If
End Sub

David_Wallis

I was using that to find the | as the result i get if I do

Debug.print alinks(i) is the following

Excel.Sheet.8|\\cplntmbpcs03\dwallis$\My Documents\Notes Migration - Upto 4.6.3a Machines.xls!'

So I work out where the | is + 1 for the start of the file name then subtract that from the length and remove 2 off that to get the filename...

I was using ole as we were checking for linked sheets not linked cells...

David

David_Wallis

also if I change it by entering the new value as

Excel.Sheet.8|\\cplntmbpcs03\dwallis$\My Documents\Notes.xls!'

it sort of works... but it doesnt open the new file... it just sort of finds the old link even though I have deleted the other file..

if I dont put the cr@p in then I get a dde error...

When you highlight the link it shows the new path and location but doesnt actually use the new data... it somehow just keeps the other??

Does this make sense??

David

[Edited by David_Wallis - 3/27/2002 1:20:23 PM]

Crispin

Are you doing the save and RefreshAll?

David_Wallis

I am, but it doesnt make any difference..

???

David