Update source in VBA
 

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

Update source in VBA
 

Try using the =HYPERLINK() function

If this post helps click Yes
---------------
Jacob Skaria


"Lost" wrote:

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

Update source in VBA
 

Do your external files have only one sheet ?
Daniel

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

Update source in VBA
 

yes i have 7 external files, 6 of them have 6 sheets and the last one has 14.

"Daniel.C" wrote:

Do your external files have only one sheet ?
Daniel

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

Update source in VBA
 

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.

"Daniel.C" wrote:

Do your external files have only one sheet ?
Daniel

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

Update source in VBA
 

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" wrote:

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.

"Daniel.C" wrote:

Do your external files have only one sheet ?
Daniel

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

Update source in VBA
 

Sorry kinda new to this VB thing -

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

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

Update source in VBA
 

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

"Daniel.C" wrote:

Sorry kinda new to this VB thing -

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

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