XML Map autoexpansion issue

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
I have 3 tables in Excel 2016 worksheet. Tables 1 and 3 are connected to SQL database and Table 2 has a XML Map to import and display XML-files. Created a simple mockup to better illustrate this.

When loading data, each autoexpand fine and push header of the table beneath downwards to make room for extra rows (while maintaining the same gap between tables).

However, I also refresh data when tables are already filled and there's a difference between Tables 1 & 3 (SQL connections), compared to Table 2 (XML Map). In case data refresh decreases the row count in a table, SQL tables also correctly "pull" the table beneath it upwards and always maintain the same distance between the last row and header of the next table.

This is where Table 2 differs for some reason. While it correctly "pushes" the next table to maintain the same distance, it will not "pull" the next table upwards in case XML import reduces the row count in Table 2, leaving blank areas between the end of Table 2 and header of Table 3.

The gaps between tables would remain the same in case the sequence would be Table 1 -> Table 3 -> Table 2, however I'm preferring to present data in the other order (I have a separate named range consisting of all 3 tables and I'm emailing the result over VBA - this order is better for data presentation). Frankly, it's not ideal to leave XML Map table as the last one either, as this just pushes the named range also down on to empty rows, creating a blank area at the end of the email (which is obviously a better option than a big blank area between tables).

Could it be that I'm missing some setup or is it just a feature that the XML Mapped table has?


x6GKkKw
mockup.jpg
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top