Skip to content

Instantly share code, notes, and snippets.

@sancarn

sancarn/.DataLoadErrors.md

Last active January 3, 2023 15:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sancarn/ff014cf79f178c5f433520e4018d82cb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sancarn/ff014cf79f178c5f433520e4018d82cb to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sancarn revised this gist Jan 3, 2023. 1 changed file with 10 additions and 2 deletions.
    12 changes: 10 additions & 2 deletions .DataLoadErrors.md
    View file
    Original file line number Diff line number Diff line change
    @@ -20,7 +20,9 @@ or this:

    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](https://gcits.com/knowledge-base/microsoft-excel-cannot-access-file-sharepoint/#how-to-clear-your-office-document-cache) --> Again, unlikely this would be the root cause, or fix the issue.

    ## When the problem can occur

    @@ -37,4 +39,10 @@ Supposedly these errors can occur for many different reasons and the fact that E

    ## 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.
    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.
  2. sancarn created this gist Jan 3, 2023.
    40 changes: 40 additions & 0 deletions .DataLoadErrors.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,40 @@
    # 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](https://user-images.githubusercontent.com/7938900/210385599-5e1d6ab7-78dc-4294-b6ba-38d3341e15ca.png)

    or this:

    ![image](https://user-images.githubusercontent.com/7938900/210386534-cfc8168f-3645-4c0a-91d9-c77a92933566.png)

    ## The problem

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



    ## 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](https://techlogon.com/how-to-fix-a-cannot-download-the-information-you-requested-error-message-on-hyperlinks#1.%20If%20the%20hyperlink%20is%20to%20an%20encrypted%20(secure)%20https://%20webpage:~:text=1.%20If%20the%20hyperlink%20is%20to%20an%20encrypted%20(secure)%20https%3A//%20webpage)
    [- 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`.](https://learn.microsoft.com/en-US/office/troubleshoot/office-suite-issues/cannot-locate-server-when-click-hyperlink#cause)

    ## 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.
    22 changes: 22 additions & 0 deletions LoadErrorChecker.bas
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    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