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
> > >
> > >
|
Similar Threads
