I have a macro that works great in Excel 2010. There is a button that refreshes a "From Web" data connection which hits an external api and returns back an xml object that gets parsed into 100s of rows of data. The issue is that one (possible more) of those xml elements may have special characters in them (I am assuming the culprit in my example is the carriage return, but not 100% sure). Now in Excel 2003 and in Office for Mac 2010 when I refresh the data via the same button...the data does not parse nicely into columns. What appears to be happening is those carriage returns are causing excel to put the data from that element into multiple rows. Which then causes all sorts of issues with the rest of my macros.

So, if my xml that is getting imported looks like this
<data>
<cola>abc</cola>
<colb>def<colb>
<colc>some text with a carriage return {CRLF} and then some more text after carriage return </colc>
<cold>xyz</cold>
<cola>abc2</cola>
<colb>def2<colb>
<colc>some text with a carriage return {CRLF} and then some more text after carriage return 2</colc>
<cold>xyz2</cold>
</data>

In excel2010, it refreshes correctly...like so
..........COLA......COLB...COLC..............................................................................................................COLD
Row1 abc def some text with a carriage return and then some more text after carriage return xyz
Row2 abc2 def2 some text with a carriage return and then some more text after carriage return2 xyz2


In excel 2003, it refreshes incorrectly...like so
..........COLA...................................................................COLB...COLC..................................................COLD
Row1 abc def some text with a carriage return
Row2 and then some more text after carriage return xyz
Row3 abc2 def2 some text with a carriage return
Row4 and then some more text after carriage return2 xyz2


If there were just one carriage return, i probably can reformat it...but there can me any number of carriage returns...so I am hoping there is a way to adjust the refresh command in VBA...or update the connection.

Any help would be appreciated

Thanks,
Dan