XML-mapping not compliant with Excel RefreshAll

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
60
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
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

Board Regular
Joined
Feb 28, 2016
Messages
60
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...
 
Last edited:

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top