VBA update links

[kris01]

Hiya,

Need some help on my coding below.

I'm trying to edit the links when the file copies over the formulas and change the source book from the xlsm file to the xlsx. I've attempted the coding but it has no impact when I play through the code, which would indicate that I'm missing something or its not in the right order, but I simply can't figure out where I'm going wrong. Any ideas?

I've highlighted the code where I want to edit the links.

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As RangeDim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting"
With Sheets("Control")
sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
End With
Set w = Sheets("Mainstay Master")
Set X = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

With w
lr = .Cells(Rows.Count, 1).End(xlUp).Row
lc = .Cells(1, Columns.Count).End(xlToLeft).Column
.Cells(1, 1).Resize(lr, lc).Copy
b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
.Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
X.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"

Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
X.Range("b5").PasteSpecial Paste:=xlPasteValues

newLink = ThisWorkbook.Path & "\Mainstay Master.xlsx"
linkSources = ThisWorkbook.linkSources(xlLinkTypeExcelinks)
If IsArray(linkSources) Then
For Each link In linkSources
If InStr(link, "Mainstay Master Template.xlsm") Then _
ThisWookbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
Next
End If

With X
Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Sitenames"
End With

 

[GlennUK]

Did you put a breakpoint inside the For Each link loop? (and examine what is going on, by using Watches and/or Immediate Window ... while stepping through one line of code at a time?)