"The following data range failed to refresh ExternalData_1" - but the data is not external

We need to see the file to answer that.

IMPORTANT: Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Sorry, can't do that. The records contain information about hospital patients.

If you had the file in front of you, what would you be looking at/for?

I would first test if I can reproduce the issue on one of my systems. Then I would look at the query codes.

If your file contains confidential data, make a copy of your original file and anonymize the necessary data.

For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Andreas.

If your file contains confidential data, make a copy of your original file and anonymize the necessary data. For this please download this filehttps://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1Open your Excel fileRight-click on the sheet tabChoose "View Code"Press CTRL-MSelect the downloaded file and importClose the VBA editorSelect the cells with the confidential dataPress Alt-F8Choose the macro AnonymizeClick Run

Thanks for your suggestion and this code.  Unfortunately it's not practical in my workbook, because the correct functioning of the sheets and queries depends upon having the right format for records in the various fields, including valid NHS numbers, specific diagnoses, etc.

Would you mind being more specific in this please:  "Then I would look at the query codes." .? 

Surely this error message relates to Excel trying to find something external, when in fact the connections are all to local tables - ie those stored in the workbook, like this:

There is nothing exotic about the way the data for the queries is structured.  There is a main Table from which the source of the queries is referenced like this:

Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="tbl_Main_Table_Name"]}[Content]), 

Queries are used to organise that data and then those queries are saved as connections and then merged into a report query with this kind of reference:

#"Merged in Triage" = Table.NestedJoin(#"Merged in Referrals", {"Referral Reference"}, #"q Triage from Events in report range", {"Referral Reference"}, "q Triage from Events in report range", JoinKind.FullOuter),

I have a small VBA code that refreshes the data like this:

Sub Refresh_Full_Report_data()

Dim cn As WorkbookConnection

Dim RefreshState As Variant

Application.Calculation = xlManual ' avoiding messing something up by calculating before refresh has finished

Application.ScreenUpdating = False ' stops flickering

Sheets("Report").Unprotect Password:="xyz"

ActiveWorkbook.Unprotect "xyz"

For Each cn In ThisWorkbook.Connections

    If cn = "Query - Full Report" Then

        With cn.OLEDBConnection

        RefreshState = .BackgroundQuery 'Save the current BackgroundQuery state

        .BackgroundQuery = False 'Turn off BackgroundQuery

        .Refresh 'Refresh this query

        .BackgroundQuery = RefreshState 'Restore the BackgroundQuery state

         End With

         End If

Next cn

Application.Calculation = xlAutomatic ' Turning it back on

Application.ScreenUpdating = True  

 Sheets("Report").Protect Password:="xyz" 

ActiveWorkbook.Protect "xyz"

End Sub

All of this normally runs without issue in both Office365 and Excel 2010.  However, for some reason Excel 2010 sometimes throws up this error when the report table is refreshed:

"The following data range failed to refresh ExternalData_1"

What would you be looking for to explain why this message is produced?

Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="tbl_Main_Table_Name"]}[Content]), 

It is obvious that someone has modified the MCode and, as always in life, a chain of circumstances is likely to cause the 

problem.

Without seeing the file, these are all guessing games.

Andreas.