Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Links in Excel with VBA
Does anyone know how to change links in Excel with VBA and
using variable names ?? I have an application that moves from one server to several others. I can create the path that is the current link as Current_link_name I can also create the target link to change to as New_link_name My problem is in getting VBA to change the links with the two variable names, without "hard-wiring" the names into the command. Any thoughts ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Links in Excel with VBA
You could do something along the lines of this:
Sub Tester20() Dim OldPath As String Dim NewPath As String Dim sStr As String, sStr1 As String Dim i As Long Dim Alinks as Variant OldPath = "whatever" NewPath = "whatever" Alinks = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(Alinks) Then For i = LBound(Alinks) To UBound(Alinks) If InStr(1, Alinks(i), OldPath, vbTextCompare) Then sStr = Alinks(i) sStr1 = Application.Substitute(sStr, OldPath, NewPath) ActiveWorkbook.ChangeLink sStr, sStr1, xlLinkTypeExcelLinks End If Next End If End Sub -- Regards, Tom Ogilvy Tom Hickey wrote in message ... Does anyone know how to change links in Excel with VBA and using variable names ?? I have an application that moves from one server to several others. I can create the path that is the current link as Current_link_name I can also create the target link to change to as New_link_name My problem is in getting VBA to change the links with the two variable names, without "hard-wiring" the names into the command. Any thoughts ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Data Links | Excel Discussion (Misc queries) | |||
Changing Database links? | Excel Discussion (Misc queries) | |||
Changing Links in 2007 | Links and Linking in Excel | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
Changing Links | Excel Discussion (Misc queries) |