Stop links changing when file name changes.

[mickyh]

Evening
I have a spreadsheet that saves data as a link on another spreadsheet but the problem is that each time I run the template all the previous links update to the new file name. is their a way to prevent this or am I missing something obvious? Thanks in advance the relevant bit of code shown below.

If wbook Is Nothing Then
    Workbooks.Open FileName:=Environ("USERPROFILE") & "\Dropbox\SPSL Shared\Estimates 2021\JobList.xlsx" ' substitute for directory location of workbook and correct Filename
     
    Workbooks("JobList.xlsx").Activate
    Worksheets("JobList").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
End If
Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
    
    ThisWorkbook.Activate
    With Sheets("Markup")
    ActiveSheet.Unprotect "sandym0unt"

    wbNam = Range("B102")
    dt = Format(CStr(Now), "dd_mm_yyyy")
    ChDir Environ("USERPROFILE") & "\Dropbox\SPSL Shared\Estimates 2021\Instadoor Estimates" ' Directory wher you want file saved.
    ActiveWorkbook.SaveAs FileName:=wbNam & " " & dt, FileFormat:=(52)
    
    Worksheets("Markup").Range("C100:I100").Copy
    Workbooks("JobList.xlsx").Activate
    Worksheets("JobList").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(0, 3).PasteSpecial Paste:=xlPasteValues
    
    ThisWorkbook.Activate
    Worksheets("Markup").Range("B101:D101").Copy
    Workbooks("JobList.xlsx").Activate
    Worksheets("JobList").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(0, 11).Select
    ActiveSheet.Paste Link:=True
    Worksheets("JobList").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(0, 12).FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]*1.2)"
    
    dt = Format(CStr(Now), "dd mmmm yyyy")
    Workbooks("JobList.xlsx").Activate
    Worksheets("JobList").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(0, 14).Value = dt
    Cells(1, 1).Select

 

[johnnyL]

Application.AskToUpdateLinks = False

Try setting that to True.

When it is set to 'False', links will be updated when the file is opened.

When it is set to 'True', when you open the file, you will be asked if you want to update the links, respond with NO.

 

[johnnyL]

If you don't want the popup window and you know that you don't want the updating of links you could delete:

Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False

and simply append the following to the end of your 'open' line:

, UpdateLinks:=False

Example:

Application.Workbooks.Open Filename:=Environ("USERPROFILE") & "\Dropbox\SPSL Shared\Estimates 2021\JobList.xlsx", UpdateLinks:=False

 

[mickyh]

Hi JohnnyL
Thanks for the reply and my apologies for not responding sooner. I possibly worded my question badly, I do want links to update, but to the workbook, they were originally saved on. the problem I was having was that all of the links updated to the last saved workbook.
I have found a solution (or workaround) which is to close the "Joblist" wbook after saving the filename then opening it again to save the link. it obviously runs a bit slower.
I'm always grateful for any advice and it was while trying your suggestions that I figured it out so kudos for that.