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 |
Any Ideas?
David |
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 |
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 |
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 |
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] |
Are you doing the save and RefreshAll?
|
I am, but it doesnt make any difference..
??? David |
All times are GMT +1. The time now is 04:54 AM. |
© 2024 MH Sub I, LLC dba Internet Brands