XML-mapping not compliant with Excel RefreshAll

[dotsent]

Hi!

Running an Excel 2016 workbook with a few data connections to SQL server and they refresh fine with "Refresh all" command. I added a XML-mapped table just for exporting (i.e. no real refreshing needed - data in XML-mapped table is filled manually and via formulae & exported on-demand). Did it by regular XML-mapping functionality under the Developer tab and a XSD-file.

Once a XML-mapped table is created, "Refresh all" button will not run anymore properly and I'm prompted by a warning message:

"The following data range failed to refresh: Data_map
Continue to refresh all?

Other than that I do get proper data connection refresh and the XML export itself works fine. The problem I have is I want to schedule a VBScript to launch RefreshAll command and this error message gets in a way of running this process smoothly.

While I might be able to possibly bypass this by .DisplayAlerts = False property in VBS, I'm reluctant to do it, as I don't want VBScript to complete if there are other unexpected warnings or errors. How could I remove the XML mapped table from the RefreshAll command or how could I make the XML mapping compliant with RefreshAll (I'm not entirely sure what it tries to refresh anyway)? There is not even a possibiliy to remove "Refresh this connection on Refresh All" checkmark like on regular Data connections in Excel.

Thanks a bunch!

 

[SpillerBD]

In my mind, Refresh All looks at the Connections. Is your XML table listed in the Connections? You should be able to change the connection's properties and exclude it from the Refresh All.

 

[dotsent]

Indeed. This part is also not entirely clear. So - I have a workbook I was developing (and where I first noticed the warning). Adding this XML-mapping at start did create a new connection which I seemed to have named "schema". Now when opening the properties of this connection - it's all dim, i.e. its not possible to adjust settings, however the "include on Refresh All" is checked. I don't know how it got there, but the reason for not being able to manipulate its settings might be because on the last tab where it shows the Excel table ranges where the connection is used, it's just all blank, i.e. not used anywhere.

So, I should just delete the connection, right? Well, no dice. While I can do it, it will not change the outcome. The warning on RefreshAll disappears only when I remove the XML mapping from under the Developer tab and re-appears when I add it again. When mapping the XML table again (and starting to see the warning again on refresh all), it did not produce a new connection (which I had deleted), so I'm not entirely sure what I might have done differently at first.

I even tried a seemingly foolproof option - added the XML-mapping to a brand new Workbook. While creating a XML-mapped table in a blank workbook did not produce a connection, launching RefreshAll gives the same warning. Not sure which stone to turn now...