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

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

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

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" wrote in message ...
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

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

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
-----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" wrote in message
...
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
.

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

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

Regards,
Tom Ogilvy

"Sandy" wrote in message
...
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
-----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" wrote in message
...
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
.

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

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" wrote in message
...
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
.