Hello,

I have a problem to solve, I tried different strategies but with no satisfactory results until now.

I have few online html tables (feeds), lets say "header" , "message" and "history". First two tables have single entries for each record but "history" table has multiple entries for a record.
Theese tables are imported in an excel sheet (first) through web queries.
I have a relational xml schema for exporting the values to a xml file.
If I try to apply the schema to the tables heading this is no possible because of excel limitations.
So I tried to import all the values to another sheet (second) where I have another three tables "header2" , "message2" and "history2" with the same headings. For the first to ables I linked the corresponding cells to second sheet using formulas of type =first!A2 ; =first!B2, etc.
For "history" tables, because it has multiple entries (maximum 20), I linked all the cells into the second sheet using another formula =IF(first!C31="","",first!C31) . I use this type o formula to avoid empty cells to be imported as "zero".
The problem is I dont't know the number of entries for the "history" table so I defined a number of 20 rows of linked cells between "first" sheet and "second" sheet.
I applied the schema and then when I export the tables I have a problem with "history2" table entries in xml because for the rows where there are entries everything is ok, but for rows where there are no entries in the table but formula exists, the rows ar exported as tags wit no values, like here:

-<history2>
<patientepisodenamemeddraversion>13</patientepisodenamemeddraversion>
<patientepisodename>2</patientepisodename>
<patientmedicalcontinue>1</patientmedicalcontinue>
</history2>
<history2> </history2>
<history2> </history2>
<history2> </history2>
<history2> </history2>
<history2> </history2>
<history2> </history2>
<history2> </history2>

I tried another strategy, to use a macro and paste the value cells to the second sheet but this is not posible with multiple entries table because another limitation where a schema with multiple entries is applied.
Another solution maybe is to use different sheet for each web query import data, than export to separate sheet but keeping the header.
The macro will be like
Worksheets("second").Range("A2:M8").Value = Worksheets("first").Range("A2:M8").Value
and the end o range maybe should not be B8 and should be at the end of the active rows, but I don' know how to define the range.
Or maybe there is another solultion.
Any help will be appreciated.
Thank you!