Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-814130%22%20slang%3D%22en-US%22%3EWorkbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814130%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20stripped%20a%20very%20large%20workbook%20and%20have%20attached%20the%20sanitized%20data.%20There%20is%20one%20resulting%20cell%20(A1)%2C%20which%20is%20shaded%20with%20no%20data.%20The%20data%20validation%20is%20set%20to%20allow%20any%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20workbook%20keeps%20asking%20to%20update%20links%20upon%20open%2C%20which%20causes%20a%20problem%20when%20sharing%20this%20workbook%20externally.%20What%20is%20the%20problem%2C%20and%20how%20can%20this%20be%20identified%20%2F%20fixed%20going%20forward%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Office%20365%20for%20Business%20Version%201907%20Build%2011901.20176%20Click%20To%20Run%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-814130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814636%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395628%22%20target%3D%22_blank%22%3E%40LaurenceLau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20it%20asks%20external%20credentials%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20671px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127777iC0C378E25D755B05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESecond%2C%20external%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127778i720E570EAD98B273%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20some%20others.%3C%2FP%3E%0A%3CP%3EAll%20links%20are%20within%20your%20VBA%20code%2C%20in%20particular%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E...%0ASet%20fso%20%3D%20CreateObject(%22Scripting.FileSystemObject%22)%0AIf%20fso.FolderExists(%22F%3A%5CSHAREBOX%5CSALES%5CPricing%20Tool%5CExtracts%5CMining%5C%22)%20Then%0A%20%20%20%20path%20%3D%20%22F%3A%5CSHAREBOX%5CSALES%5CPricing%20Tool%5CExtracts%5CMining%5C%22%0AElse%20%20%20'for%20Houston%20PC's%20that%20link%20H%20instead%20of%20F%20for%20sharebox%0A%20%20%20%20If%20FileThere(%22H%3A%5CSHAREBOX%5CSALES%5CPricing%20Tool%5CExtracts%5CMining%5C%22)%20Then%0A%20%20%20%20%20%20%20%20path%20%3D%20%22H%3A%5CSHAREBOX%5CSALES%5CPricing%20Tool%5CExtracts%5CMining%5C%22%0A%20%20%20%20Else%0A%20%20%20%20'generic%20for%20people%20who%20don't%20have%20linked%20drive%20letters%0A%20%20%20%20%20%20%20%20path%20%3D%20%22%5C%5CZEUS%5CSYS%5CSHAREBOX%5CSALES%5CPricing%20Tool%5CExtracts%5CMining%5C%22%0A%20%20%20%20End%20If%0AEnd%20If%0A...%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814780%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814780%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20have%20removed%20the%20macro%2C%20saved%20the%20file%20as%20.xlsx%20(see%20attached)%2C%20but%20it%20is%20still%20asking%20for%20external%20credentials.%20Can%20you%20assist%20me%20as%20to%20why%20this%20is%20happening%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814912%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395628%22%20target%3D%22_blank%22%3E%40LaurenceLau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20links%20are%20in%20cache%20for%20A1%20data%20validation.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20399px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127794iDA94BAC39AEB324C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20copy%2Fpaste%20any%20empty%20cell%20to%20A1%20links%20disappear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814955%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20click%20on%20Cell%20A1%20--%26gt%3B%20Data%20Validation%2C%20the%20prompt%20informs%20me%20that%20cell%20A1%20allows%20any%20value.%3C%2FP%3E%3CP%3EAlso%2C%20I%20can%20enter%20the%20value%20%22123%22%20that%20you%20entered%20in%20your%20screenshot.%20You%20can%20still%20see%20the%20%22Enable%20Content%22%20prompt%20in%20my%20screenshot%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20something%20wrong%20with%20my%20version%20of%20Excel%3F%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127797i9D60A11D17537C91%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816094%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395628%22%20target%3D%22_blank%22%3E%40LaurenceLau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20checked%20on%20another%20PC%20-%20behavior%20is%20approximately%20as%20in%20your%20case.%20Nonetheless%2C%20if%20unzip%20your%20file%20and%20check%20xml%20inside%20-%20it%20has%204%20external%20links%20and%20lot%20of%20related%20data%2C%20it%20looks%20like%20they%20are%20connected%20to%20data%20validation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20say%20exactly%20why%20it's%20so.%20Practical%20step%20-%20if%20you%20clean%20cell%20A1%20(replace%20it%20by%20any%20empty%20cell)%2C%20external%20references%20disappear%20and%20file%20works%20without%20errors.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816557%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816557%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20unzip%20an%20Excel%20file%20and%20check%20the%20XML%20inside%3F%20I%20didn't%20know%20you%20can%20unzip%20an%20XLSX%20file.%20I%20am%20asking%20because%20I%20would%20like%20to%20identify%20and%20correct%20this%20issue%20in%20my%20other%20files%20as%20well%2C%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816644%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Keeps%20Asking%20to%20Update%20Links%20-%20Workbook%20Contains%20No%20External%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395628%22%20target%3D%22_blank%22%3E%40LaurenceLau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20the%20window%2C%20which%20asks%20for%20the%20credentials%2C%20is%20loaded%2C%20close%20it%20and%20do%20it%20for%203%20times%20and%20your%20workbook%20will%20be%20opened%20normally.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20step%20is%2C%20you%20will%20need%20to%20break%20all%20the%20existing%20links%20and%20to%20do%20that%20run%20the%20following%20macro%20when%20the%20problematic%20workbook%20is%20the%20ActiveWorkbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20RemoveExternalLinks()%0ADim%20wb%20As%20Workbook%0ADim%20exLinks%20As%20Variant%0ADim%20i%20As%20Long%0ASet%20wb%20%3D%20ActiveWorkbook%0AexLinks%20%3D%20wb.LinkSources(Type%3A%3DxlLinkTypeExcelLinks)%0AFor%20i%20%3D%201%20To%20UBound(exLinks)%0A%20%20wb.BreakLink%20Name%3A%3DexLinks(i)%2C%20Type%3A%3DxlLinkTypeExcelLinks%0ANext%20i%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20change%20the%20Startup%20Prompt%20settings%20for%20the%20external%20links%20as%20shown%20in%20the%20image%20below.%3C%2FP%3E%3CP%3EFollow%20the%20steps%20exactly%20shown%20in%20the%20image.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127974i48E782A3D8FCB86F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22BreakLinks.jpg%22%20title%3D%22BreakLinks.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
LaurenceLau
New Contributor

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

7 Replies

@LaurenceLau 

First, it asks external credentials

image.png

Second, external file

image.png

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
...

 

@Sergei Baklan I have removed the macro, saved the file as .xlsx (see attached), but it is still asking for external credentials. Can you assist me as to why this is happening?

@LaurenceLau 

It looks like links are in cache for A1 data validation.

image.png

If copy/paste any empty cell to A1 links disappear.

@Sergei Baklan 

 

When I click on Cell A1 --> Data Validation, the prompt informs me that cell A1 allows any value.

Also, I can enter the value "123" that you entered in your screenshot. You can still see the "Enable Content" prompt in my screenshot below.

 

Is there something wrong with my version of Excel? Thanks.

 

Capture.PNG

@LaurenceLau 

I checked on another PC - behavior is approximately as in your case. Nonetheless, if unzip your file and check xml inside - it has 4 external links and lot of related data, it looks like they are connected to data validation.

 

I can't say exactly why it's so. Practical step - if you clean cell A1 (replace it by any empty cell), external references disappear and file works without errors. 

 

 

How do I unzip an Excel file and check the XML inside? I didn't know you can unzip an XLSX file. I am asking because I would like to identify and correct this issue in my other files as well, thanks.

@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.

 

BreakLinks.jpg

Related Conversations