"The following data range failed to refresh: ExternalData_1"

[kcmuppet]

Hi

My workbook doesn't have any external data connections (all the queries/connections are from Tables within the sheet),

however, when I refresh one of the queries that loads to a Table a the worksheet, I get this message...

The following data range failed to refresh:
ExternalData_1

...only if

1. I'm using Excel 2010 (refreshing the same query in the same file opened in 365 does not produce this error)
   
   
2. The table of data that the query is refreshing results in fewer rows than is already present on the worksheet

Can anybody think of a way to avoid that happening?

(I tried deleting all but the first couple of rows of the table before refreshing the query, but in Excel 365 it seems having done that it only refreshes those first two rows and doesn't load the rest of the rows that the query generates to the table.)

 

[Alex Blakenburg]

It might worth checking that the 2010 Excel at least has the latest Add-In that is available.
It looks like MS is no longer supporting it with upgrades though.

Download Microsoft Power Query for Excel from Official Microsoft Download Center

 

[kcmuppet]

It might worth checking that the 2010 Excel at least has the latest Add-In that is available.
It looks like MS is no longer supporting it with upgrades though.

Thanks for your reply. It does indeed have the latest Power Query add-in installed. And yes, although it would be best, we're talking here about a UK NHS Trust with no date in sight to replace the no longer supported Excel 2010 technology. Sadly and frustratingly, I have to find way to make it work for at least the next 18 months.

Unfortunately, I don't really have a thorough understanding of how power query handles updates to Tables. I know for example, that Tables loaded by power query can have columns added that are not part of the query. That's not the case with my Table but presumably it means that upon loading it has to check for adjacent formulae and table structures and so, on.

I've set the table to preserve column widths and and data validation to allow changes against a list to two of the loaded columns, which perhaps Excel 2010's power query add-in find less easy to deal with, but I can't consistently demonstrate that the data validation is the cause of the problem - i.e. it sometimes seems to fail even with data validation removed.

What I also don't understand is why the error is "ExternalData...", when there is no external data.

It seems 'ExternalData_1' is an automatically created named range. It is not listed in the regular Excel Name Manager, but if I use a third party Name Manager add-in it seems that some queries that load to sheets have a sheet specific named-ranges SheetName!ExternalData_1,ExternalData_2, etc.

If I delete them, then:

• when right-clicking on the table Refresh is greyed-out
  
  
• if I try to refresh the query from the Queries & Connections pane, [it's not greyed-out, but] nothing happens
  
  
• Refresh all from the Data menu results in an error: Unexpected error: Exception from HRESULT: 0x800A9C68. Details: Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Exception from HRESULT: 0x800A9C68 ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A9C68. And then the workbook becomes unuseable (e.g. none of the code windows show in the VBA editor)

So I'm wondering if the way to get around the problem could be in trying to automate doing something with the loaded Table to make it have fewer/ no rows before refreshing the query

 

[Alex Blakenburg]

I am not sure this will be of any help at all but just in case.
If you unzip the file (Workbook), you will find ExternalData_1 referenced in these 2 directories.
\xl > file: workbook.xml
\xl\queryTables > files: queryTable1.xml (queryTable2.xml, 3xml etc)

Note: Don't get misled (like I did) by the term HiddenTable in my screen shots, that is actually the sheet name that one of the Query Outputs is on.
Similar to the other one being SalesReport

I am wondering if the Schema references are Excel version specific.

Do you have other workbooks using Power Query and are you having the same issues with them ?
If not maybe try unzipping the one that works and the one that doesn't work and see if there are any differences in the schema.
Have you had a chance to try it on someone else's Excel 2010 ?

My sample queryTable1.xml

My sample workbook.xml

 

[kcmuppet]

Looking at the xml files, most seem to follow the same format as the example you kindly posted, like this:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<queryTable applyWidthHeightFormats="0" applyAlignmentFormats="0" applyPatternFormats="0" 
applyFontFormats="0" applyBorderFormats="0" applyNumberFormats="0" autoFormatId="16" connectionId="13" adjustColumnWidth="0"
name="[COLOR=rgb(184, 49, 47)]ExternalData_2[/COLOR]" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    -<queryTableRefresh nextId="8">
        -<queryTableFields count="1">
            <queryTableField name="Communication" tableColumnId="3" id="7"/>
        </queryTableFields>
    </queryTableRefresh>
</queryTable>

...all containing reference to schemas.openxmlformats.org/spreadsheetml/2006/main before listing the field names and ending as above.


After the Excel file has been used in Excel 2010 and then opened again in Excel 365 one of the queries starts like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<queryTable xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="xr16" xmlns:xr16="http://schemas.microsoft.com/office/spreadsheetml/2017/revision16"
name="ExternalData_1" backgroundRefresh="0" connectionId="1" ...

...i.e. with reference to schemas.microsoft.com/office/spreadsheetml/2017/revision16 as well as 2016, and ends like this:

<queryTableField id="84" name="q Events[Event Date (Month Index)]" tableColumnId="84"/>
        </queryTableFields>
    </queryTableRefresh>
    <extLst>
        <ext uri="{883FBD77-0823-4a55-B5E3-86C4891E6966}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">
            <x15:queryTable drillThrough="1"/>
        </ext>
    </extLst>
</queryTable>

....i.e. referencing schemas.microsoft.com/office/spreadsheetml/2010/11/main.

I've no idea whether that is significant as I don't know the first thing about xml.

I've also noticed that in some versions of the Excel files the workbook.xml file appears to have more than one reference to a defined connection name, e.g.

<definedName name="ExternalData_1" localSheetId="5" hidden="1">Report!$A$6:$AK$51</definedName>
<definedName name="ExternalData_1" localSheetId="7" hidden="1">Sheet1!$A$3:$CF$19</definedName>

Again, I don't know if this is significant? Maybe that's OK?

 

[Alex Blakenburg]

Apart from the last item, I am afraid that I am not able to add any insights.

For the last 1, I have done some further testing and can confirm that every Query that has been output to the spreadsheet (at least as a table), creates a hidden defined name of ExternalData.
The first output based on a datasource is ExternalData_1, if you create a 2nd using the same data source ie becomes ExternalData_2.
(I have tested a Data Source of a Table in the workbook and a csv file)

Sorry I am unable to resolve your issue. There has to be something that is not backward compatible but I don't know how to overcome it.

 

[kcmuppet]

The first output based on a datasource is ExternalData_1, if you create a 2nd using the same data source ie becomes ExternalData_2.

That makes sense to me.

What makes less sense is when I see something like this:

<definedName name="ExternalData_1" localSheetId="5" hidden="1">Report!$A$6:$AK$51</definedName>
<definedName name="ExternalData_1" localSheetId="7" hidden="1">Sheet1!$A$3:$CF$19</definedName>
<definedName name="ExternalData_2" localSheetId="4" hidden="1">KPIs!$F$130:$F$134</definedName>
<definedName name="ExternalData_2" localSheetId="6" hidden="1">'NHSE Report Data'!$A$7:$AE$30</definedName>
<definedName name="ExternalData_3" localSheetId="4" hidden="1">KPIs!$D$130:$D$135</definedName>

...where the same name ("ExternalData_2") appears to be given to different ranges and the a single range "KPIs!$F$130:$F$134" has apparently more than one name ("ExternalData_3" above overlaps one of the "ExternalData_2" ranges).

Knowing nothing about how the xml files are created or used means I don't know if that is normal or acceptable behaviour or whether it could be a symptom of the failure to refresh issues.

There has to be something that is not backward compatible but I don't know how to overcome it

Frustratingly, its not consistent. Most of the time everything works. Just occasionally, it falls over and then continues to fail. I wish I knew what about the failure scenario triggers the failure and how to avoid it - the specific data, or some context of use (whatever else Excel might be doing in the background).

Anyway thanks for your engagement.

 

[Alex Blakenburg]

Inconsistent issues are certainly the hardest to find. Sorry we couldn't find a solution.

This is not going to solve the issue but assuming you are referring to these 2 they are in different columns (F & D) and are probably 2 separate outputs by 2 separate queries both using the same data source.
ExternalData_2 KPIs!$F$130:$F$134
ExternalData_3 KPIs!$D$130:$D$135
I haven't tried deleting one of the ExternalData_1 queries to see if it renumbers an already existing _2 back to _1. Assuming it doesn't that would muddy the water a bit more.