Skip to content

Instantly share code, notes, and snippets.

@sancarn
Last active January 3, 2023 15:37
Show Gist options
  • Save sancarn/ff014cf79f178c5f433520e4018d82cb to your computer and use it in GitHub Desktop.
Save sancarn/ff014cf79f178c5f433520e4018d82cb to your computer and use it in GitHub Desktop.

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:

image

or this:

image

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:

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.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment