Change all Links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shorticake - 12 Dec 2007 22:24 GMT

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 to this Message

sebastienm - 12 Dec 2007 23:18 GMT

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

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> 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
[quoted text clipped - 15 lines]
>
> End Sub

Reply to this Message

shorticake - 13 Dec 2007 14:43 GMT

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.

> Hi,
> using a loop, something like:
[quoted text clipped - 29 lines]
> >
> > End Sub

Reply to this Message

sebastienm - 13 Dec 2007 17:27 GMT

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)?

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> 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
[quoted text clipped - 38 lines]
> > >
> > > End Sub

Reply to this Message