Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all Links
Hi,
using a loop, something like: Dim OldLink, NewLink As String Dim wsInput As Worksheet Dim i as long Set wsInput = ThisWorkbook.Worksheets("Input") for i =11 to 47 OldLinks = wsInput.Range("E" & i).Value NewLink = wsInput.Range("C" & i).Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks Next i -- Regards, Sébastien <http://www.ondemandanalysis.com "shorticake" wrote: Each month I have to change the links in my workbook. I've listed the paths of the current links in cells E11:E47, and what I want the paths of the new links to be in cells C11:C47. How can I change the code below so that it will change all the links in the correct order for example, E11 to C11, E12 to C12, and so on? Thanks in advance! Sub ChangeLink() Dim OldLink, NewLink As String Dim wsInput As Worksheet Set wsInput = ThisWorkbook.Worksheets("Input") OldLinks = wsInput.Range("E15").Value NewLink = wsInput.Range("C15").Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all Links
Sebastienm, thanks so much for your quick reply. I tried this, but for some
reason I keep getting a run-time error code 1004 "Method 'ChangeLink' of object'_Workbook' failed" and the code stops at "ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks." I know very little about writing macros, and I'm not sure if this makes any sense, but it looks like the OldLinks value is not passing to Name:= Your help is very much appreciated. "sebastienm" wrote: Hi, using a loop, something like: Dim OldLink, NewLink As String Dim wsInput As Worksheet Dim i as long Set wsInput = ThisWorkbook.Worksheets("Input") for i =11 to 47 OldLinks = wsInput.Range("E" & i).Value NewLink = wsInput.Range("C" & i).Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks Next i -- Regards, Sébastien <http://www.ondemandanalysis.com "shorticake" wrote: Each month I have to change the links in my workbook. I've listed the paths of the current links in cells E11:E47, and what I want the paths of the new links to be in cells C11:C47. How can I change the code below so that it will change all the links in the correct order for example, E11 to C11, E12 to C12, and so on? Thanks in advance! Sub ChangeLink() Dim OldLink, NewLink As String Dim wsInput As Worksheet Set wsInput = ThisWorkbook.Worksheets("Input") OldLinks = wsInput.Range("E15").Value NewLink = wsInput.Range("C15").Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all Links
1. I noticed you declared OldLink as Variant:
Dim OldLink, NewLink As String VBA requires each variable from a declarative list to be associated a type, otherwise it is a Variant, ie if you need it as a string: Dim OldLink As String, NewLink As String It has probably nothing to do with the issue, but never know... 2. Output the list of current links: Sub test() Dim v For Each v In ThisWorkbook.LinkSources Debug.Print v Next End Sub 3. Within your loop , after assigning OldLink and New Link and before the ChangeLink line, output the result, just for testing purpose: Debug.Print "---" & i & "---" Debug.Print OldLink Debug.Print NewLink Anything strange in the output. Does it fail on the 1st loop iteration? or which 'i'? Any OldLink is not a link of ThisWorkbook (2)? -- Regards, Sébastien <http://www.ondemandanalysis.com "shorticake" wrote: Sebastienm, thanks so much for your quick reply. I tried this, but for some reason I keep getting a run-time error code 1004 "Method 'ChangeLink' of object'_Workbook' failed" and the code stops at "ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks." I know very little about writing macros, and I'm not sure if this makes any sense, but it looks like the OldLinks value is not passing to Name:= Your help is very much appreciated. "sebastienm" wrote: Hi, using a loop, something like: Dim OldLink, NewLink As String Dim wsInput As Worksheet Dim i as long Set wsInput = ThisWorkbook.Worksheets("Input") for i =11 to 47 OldLinks = wsInput.Range("E" & i).Value NewLink = wsInput.Range("C" & i).Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks Next i -- Regards, Sébastien <http://www.ondemandanalysis.com "shorticake" wrote: Each month I have to change the links in my workbook. I've listed the paths of the current links in cells E11:E47, and what I want the paths of the new links to be in cells C11:C47. How can I change the code below so that it will change all the links in the correct order for example, E11 to C11, E12 to C12, and so on? Thanks in advance! Sub ChangeLink() Dim OldLink, NewLink As String Dim wsInput As Worksheet Set wsInput = ThisWorkbook.Worksheets("Input") OldLinks = wsInput.Range("E15").Value NewLink = wsInput.Range("C15").Value ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change multiple links at once | Excel Worksheet Functions | |||
Change links between 2 spreadsheets | Excel Discussion (Misc queries) | |||
change links | Excel Programming | |||
change links by a macro | Excel Programming | |||
Worksheet Change event and Links? | Excel Programming |