Structured references and XML refresh

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,793
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
I have an external XML feed, which varies in the number of columns and rows, the information I need is in different columns positions which I cannot control
my first take was to import the data to an excel table, scan the headers for the columns I need and create a lookup table to use INDIRECT however this bogs it down due to the number of rows and INDIRECT is a volatile nasty function
my second take was to use structured references to pull in and play with the data TABLE1[@name]="Jim" etc this works brilliantly for the one set of data, as soon as I go to my XML table sheet, control A delete the data, and load a different XML file, excel in its infinite or in most cases lack of wisdom decides upon a different table name for the next set of data and of course the formula references collapse with #ref errors.

anyone got any tried and tested methods to keep the XML data as always say TABLE1, or ways to keep the formulae from bombing out
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,002
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
I'm not sure to entirely get this. But would this work? Have you tried it via Power Query?
1624118700107.png
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,793
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
okay I have come up with a hokey pokey way of getting around the problem
If I have say 4 xml files, file1, file2, file3, file4, the initial load is with file1 and I get my collated summary information referencing file1, known internally as Table3
if I then rename file1 in windows explorer to file1_done.xml, and then rename file2.xml to file1.xml and within the table worksheet if I refresh the data from the connections section it drags in the new data and does not blow away the references and all is good
I can then repeat the above steps for files 3 and 4
so the new file must be the same name as the initial load file to stop excel auto incrementing the tableN number internally and blowing away the references
 
Solution

Forum statistics

Threads
1,144,515
Messages
5,724,814
Members
422,579
Latest member
parsnipsnatcher

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
Top