Stop links changing when file name changes.

mickyh

New Member
Joined
Jul 10, 2012
Messages
14
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.

VBA Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
412
VBA Code:
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

Active Member
Joined
Nov 7, 2011
Messages
412
If you don't want the popup window and you know that you don't want the updating of links you could delete:

VBA Code:
Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False

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

VBA Code:
, UpdateLinks:=False

Example:

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

mickyh

New Member
Joined
Jul 10, 2012
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top