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

  • Thread starter Thread starter Sandy
  • Start date Start date
S

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
 
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
 
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
 
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
.
Click to expand...
 
Back
Top