.DataLoadErrors.md

The Excel Error

It is quite common at least in our business that when people are building spreadsheets they accidentally link data without properly acknowledging the linkage. This can cause error messages later on which can irritate users. The error messages you might recieve often contian the text:

Microsoft Excel cannot access the file 'https://.../myfile.xlsx'. There are several possible reasons:

or

Cannot download the information you requested

and might look like this:

or this:

The problem

Looking for solutions for this problem online can be difficult. Things I've seen people suggest are:

• Check Excel for viruses --> But viruses are unlikely to be the root cause, or fix the issue.
• Repair the excel file --> Again, unlikely this would be the root cause, or fix the issue. - Clear your office document cache --> Again, unlikely this would be the root cause, or fix the issue.

When the problem can occur

Supposedly these errors can occur for many different reasons and the fact that Excel doesn't provide a neat utility for finding and fixing these issues is beyond me. The problems can be:

• When refreshing a pivot table (or opening a workbook)
  • A pivot table exists who's datasource cannot be accessed
  • A pivot table exists who's datasource no longer exists
    • File missing
    • File present but sheet/range missing
• When clicking a hyperlink - A hyperlink exists which links to an encrypted (secure) HTTPS webpage - A hyperlink is clicked where the ForceShellExecute registry key isn't present or set to 1 in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\9.0\Common\Internet or HKEY_LOCAL_MACHINE\Software\Microsoft\Office\9.0\Common\Internet.

Solution

Currently this solution doesn't provide a fix for all the above issues however that is the long term intention of this gist. However you can at least currently use the below script to find the locations of pivot tables which can't be refreshed.

Next steps

• If you can think of other root causes, please mention them in the comments.
• Implement searches for hyperlink issues
• Create a workbook/UI which can be used to fix the above issues.

LoadErrorChecker.bas

Sub CheckForDataLoadErrors(ByVal wb As Workbook)
    Dim sErrors As String: sErrors = ""
    
    Dim ws As Worksheet
    Application.DisplayAlerts = False
        For Each ws In wb.Sheets
            On Error GoTo ErrorOccurredPivot
                Dim pv As PivotTable
                For Each pv In ws.PivotTables
                    Call pv.PivotCache.Refresh
                Next
            On Error GoTo 0
        Next
    Application.DisplayAlerts = True
    
    MsgBox sErrors, vbOKOnly
    
    Exit Sub
ErrorOccurredPivot:
    sErrors = sErrors & "Pivot table (" & pv.name & ") on worksheet '" & ws.name & "' had data load error." & vbCrLf
    Resume Next
End Sub