Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when I put my flash drive in, the computer does not give me drive | New Users to Excel | |||
Links to mapped drive change to refer to local hard drive | Links and Linking in Excel | |||
Obtain drive letter assignment of CD/DVD drive? | Excel Discussion (Misc queries) | |||
Can I save to hard drive AND my flash drive at the same time? | Excel Discussion (Misc queries) | |||
Link workbooks-C drive to network drive | Excel Worksheet Functions |