Find links with code; change from G drive to C drive

[Sandy]

Does anyone know how to find links with code and then
change all of them to always say "G" instead of them being
changed to "C"?

Please be as specific as possible in your reply. I'm a
little rusty on my VBA.

Sandy

 

[Andrew]

If your links are only to a handful of workbooks you could just do it
manually using Edit/Links/Change Source.

If you want/need to do it via code then use the following:

Sub EditLinks()

Dim astrLinks()
Dim strLink As String
Dim strNewLink As String

' Retrieve the links as an array
astrLinks = ActiveWorkbook.LinkSources

If Not IsEmpty(astrLinks) Then
For i = 1 To UBound(astrLinks)
strLink = astrLinks(i)

' Only update links starting with 'C'
If Left(strLink, 1) = "C" Then
' Replace the 'C' with 'G'
strNewLink = "G" & Right(strLink, Len(strLink) - 1)
' Update the link
ActiveWorkbook.ChangeLink Name:=strLink _
, NewName:=strNewLink
End If

Next i
End If

End Sub

Regards,
Andrew

 

[Sandy]

Thanks for your reply.

I ran your code, but put in "Dim i As Variant" because it
was screaming about "variable not defined."

It kept popping me into a "File Not Found" Save As dialog
box for each link. Any ideas for a way around that?

Sandy

 

[Tom Ogilvy]

Make sure you are changing the link to an existing file.

Regards,
Tom Ogilvy

 

[Sandy]

Thank you very much, Andrew!

-----Original Message-----
If your links are only to a handful of workbooks you could just do it
manually using Edit/Links/Change Source.

If you want/need to do it via code then use the following:

Sub EditLinks()

Dim astrLinks()
Dim strLink As String
Dim strNewLink As String

' Retrieve the links as an array
astrLinks = ActiveWorkbook.LinkSources

If Not IsEmpty(astrLinks) Then
For i = 1 To UBound(astrLinks)
strLink = astrLinks(i)

' Only update links starting with 'C'
If Left(strLink, 1) = "C" Then
' Replace the 'C' with 'G'
strNewLink = "G" & Right(strLink, Len (strLink) - 1)
' Update the link
ActiveWorkbook.ChangeLink Name:=strLink _
, NewName:=strNewLink
End If

Next i
End If

End Sub

Regards,
Andrew


"Sandy" <[email protected]> wrote in message
.