Update source in VBA

  • Thread starter Thread starter Lost
  • Start date Start date
L

Lost

Im looking for a code that will do the
Edit Link>Update source and change the sheet names of the external files to
the correct names in my formulas?
 
Try :

Sub test()
Dim lk, mySheet As String, sh As Worksheet
Dim f As String, p As String, Addr As String
Dim oldTxt As String, newTxt As String, c As Range
Dim Pos As Integer, ResAdr As String
Application.ScreenUpdating = False
For Each lk In ActiveWorkbook.LinkSources(xlExcelLinks)
f = Dir(lk)
Workbooks.Open lk
mySheet = Sheets(1).Name
rep = InputBox("If needed, change the sheet name for workbook "
& f, , mySheet)
If rep <> "" Then mySheet = rep

p = ActiveWorkbook.Path
ActiveWorkbook.Close False
oldTxt = "='" & p & "\[" & f & "]"
For Each sh In Sheets
Set c = sh.Cells.Find(oldTxt, , xlFormulas)
If Not c Is Nothing Then
Do
ResAdr = c.Address
Pos = InStr(1, c.Formula, "!")
Adr = Right(c.Formula, Len(c.Formula) - Pos)
newTxt = "='" & p & "\[" & f & "]" & mySheet & "'!"
& Adr
c.Formula = newTxt
Set c = sh.Cells.FindNext(c)
Loop While c <> "" And c.Address <> ResAdr
End If
Next sh
Next lk
Application.ScreenUpdating = True
End Sub

HTH
Daniel
 
Sorry kinda new to this VB thing -

Where in the VB editor do i put this and do I have to change anything?

Daniel.C said:
Try :

Sub test()
Dim lk, mySheet As String, sh As Worksheet
Dim f As String, p As String, Addr As String
Dim oldTxt As String, newTxt As String, c As Range
Dim Pos As Integer, ResAdr As String
Application.ScreenUpdating = False
For Each lk In ActiveWorkbook.LinkSources(xlExcelLinks)
f = Dir(lk)
Workbooks.Open lk
mySheet = Sheets(1).Name
rep = InputBox("If needed, change the sheet name for workbook "
& f, , mySheet)
If rep <> "" Then mySheet = rep

p = ActiveWorkbook.Path
ActiveWorkbook.Close False
oldTxt = "='" & p & "\[" & f & "]"
For Each sh In Sheets
Set c = sh.Cells.Find(oldTxt, , xlFormulas)
If Not c Is Nothing Then
Do
ResAdr = c.Address
Pos = InStr(1, c.Formula, "!")
Adr = Right(c.Formula, Len(c.Formula) - Pos)
newTxt = "='" & p & "\[" & f & "]" & mySheet & "'!"
& Adr
c.Formula = newTxt
Set c = sh.Cells.FindNext(c)
Loop While c <> "" And c.Address <> ResAdr
End If
Next sh
Next lk
Application.ScreenUpdating = True
End Sub

HTH
Daniel
yes i have 7 external files, 6 of them have 6 sheets and the last one has 14.
Click to expand...
Click to expand...
 
Sorry kinda new to this VB thing -
Where in the VB editor do i put this and do I have to change anything?
Click to expand...

You'll have to put it in a standard module of the workbook you need to
change the links. In the VBE window, right click in the project
correspponding to the workbook, click "Insert" and "module". Then,
paste the code on the right of the window.
Here is a sample file :
http://www.filedropper.com/lost
Daniel
 
I cant get into the filldroper file. Ok i understand where to put it now.
Sorry since I cant get into the filedropper i dont know/understand where the
links are in the code.

Lost
 
Back
Top