Aug 20 2019 07:44 PM
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
Aug 21 2019 03:26 AM
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
...
Aug 21 2019 04:24 AM - edited Aug 21 2019 04:25 AM
@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?
Aug 21 2019 05:31 AM
It looks like links are in cache for A1 data validation.
If copy/paste any empty cell to A1 links disappear.
Aug 21 2019 06:00 AM
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.
Aug 21 2019 03:14 PM
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.
Aug 22 2019 12:18 AM
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.
Aug 22 2019 01:39 AM - edited Aug 22 2019 01:44 AM
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.