Forum Discussion

LaurenceLau's avatar
LaurenceLau
Copper Contributor
Aug 21, 2019

Workbook Keeps Asking to Update Links - Workbook Contains No External Links

Hello,

 

I have stripped a very large workbook and have attached the sanitized data. There is one resulting cell (A1), which is shaded with no data. The data validation is set to allow any value.

 

The workbook keeps asking to update links upon open, which causes a problem when sharing this workbook externally. What is the problem, and how can this be identified / fixed going forward?

 

I am using Office 365 for Business Version 1907 Build 11901.20176 Click To Run

excel
Formulas and Functions
office 365

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 21, 2019

    LaurenceLau 

    First, it asks external credentials

    Second, external file

    and some others.

    All links are within your VBA code, in particular

    ...
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists("F:\SHAREBOX\SALES\Pricing Tool\Extracts\Mining\") Then
    path = "F:\SHAREBOX\SALES\Pricing Tool\Extracts\Mining\"
Else   'for Houston PC's that link H instead of F for sharebox
    If FileThere("H:\SHAREBOX\SALES\Pricing Tool\Extracts\Mining\") Then
        path = "H:\SHAREBOX\SALES\Pricing Tool\Extracts\Mining\"
    Else
    'generic for people who don't have linked drive letters
        path = "\\ZEUS\SYS\SHAREBOX\SALES\Pricing Tool\Extracts\Mining\"
    End If
End If
...

     

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Aug 22, 2019

        LaurenceLau 

         

        Once the window, which asks for the credentials, is loaded, close it and do it for 3 times and your workbook will be opened normally.

         

        The next step is, you will need to break all the existing links and to do that run the following macro when the problematic workbook is the ActiveWorkbook.

         

        Sub RemoveExternalLinks()
Dim wb As Workbook
Dim exLinks As Variant
Dim i As Long
Set wb = ActiveWorkbook
exLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(exLinks)
  wb.BreakLink Name:=exLinks(i), Type:=xlLinkTypeExcelLinks
Next i
End Sub

         

         

        And then change the Startup Prompt settings for the external links as shown in the image below.

        Follow the steps exactly shown in the image.

         

Resources