Record macro to export xml

I recorded a macro with 3 steps:
1. add an Xml Map to the curent workbook and choose root of the Map
2. map cells in excel file to the coresponding fields in the Map
3. export Xml data

The error I got is: �The map could not be exported, so the data was not
exported�
The problem is that step 2 is not recorded. There are no lines of code to
map the excel cells to the specified XmlMap so the map can not be exported.
How to write the code?

My xml schema has
a root � a list � 2 elements: T and O
and excel has 2 columns: T and O. The two elements should be mapped to the
excel cells.

Obs.: if I repeat these steps manually it works but if I register the macro
I get error.

please show us your code.

"IoM" wrote:

> I recorded a macro with 3 steps:
> 1. add an Xml Map to the curent workbook and choose root of the Map
> 2. map cells in excel file to the coresponding fields in the Map
> 3. export Xml data
>
> The error I got is: “The map could not be exported, so the data was not
> exported”
> The problem is that step 2 is not recorded. There are no lines of code to
> map the excel cells to the specified XmlMap so the map can not be exported.
> How to write the code?
>
> My xml schema has
> a root – a list – 2 elements: T and O
> and excel has 2 columns: T and O. The two elements should be mapped to the
> excel cells.
>
> Obs.: if I repeat these steps manually it works but if I register the macro
> I get error.
>

I looked for methods, properties to treat mapping xml - excel cells but did
not find them.
Code:
Sub Macro()

Dim strSchemaLocation As String
Dim obj1 As XmlMap

strSchemaLocation = "D:\2005.04-22\1.xsd"

'XmlMap Object Represents an XML map that has been added to a workbook.
' Use the Add method of the XmlMaps collection to add an XML map to a
workbook.
Set obj1 = ActiveWorkbook.XmlMaps.Add(strSchemaLocation, _
"dataroot")
obj1.Name = "dataroot_Map"

Application.DisplayXMLSourcePane obj1

'My xml schema has a root - a list with 2 elements: T and O
'that should be mapped to T and O excel columns
'>>>>>>>no code here – I guess here is the error>>>>>>>>>>

' Use the Export method to export data from cells mapped to the specified
XmlMap.
ActiveWorkbook.XmlMaps("dataroot_Map").Export URL:= _
"D:\2005.04-22\1.xml", _
OVERWRITE:=True

Application.CommandBars("Task Pane").Visible = True

End Sub


"Patrick Molloy" wrote:

> please show us your code.
>
> "IoM" wrote:
>
> > I recorded a macro with 3 steps:
> > 1. add an Xml Map to the curent workbook and choose root of the Map
> > 2. map cells in excel file to the coresponding fields in the Map
> > 3. export Xml data
> >
> > The error I got is: “The map could not be exported, so the data was not
> > exported”
> > The problem is that step 2 is not recorded. There are no lines of code to
> > map the excel cells to the specified XmlMap so the map can not be exported.
> > How to write the code?
> >
> > My xml schema has
> > a root – a list – 2 elements: T and O
> > and excel has 2 columns: T and O. The two elements should be mapped to the
> > excel cells.
> >
> > Obs.: if I repeat these steps manually it works but if I register the macro
> > I get error.
> >

Hi IoM,

> I looked for methods, properties to treat mapping xml - excel cells but did
> not find them.

You use the Range.Path.SetValue or ListColumn.XPath.SetValue to map a single
cell or a column of a List to an XML Map.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Thanks. I used ListColumn.XPath.SetValue and it worked.

Now i have another question:
Why Excel exports 65535 lines(maximum number) instead of only those with
useful data?
Because of that after export i have to open the xml file by hand and delete
all empty lines.
It will be nice to eliminate this manual operation.

"Stephen Bullen" wrote:

> Hi IoM,
>
> > I looked for methods, properties to treat mapping xml - excel cells but did
> > not find them.
>
> You use the Range.Path.SetValue or ListColumn.XPath.SetValue to map a single
> cell or a column of a List to an XML Map.
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
>
> Professional Excel Development
> The most advanced Excel VBA book available
> www.oaltd.co.uk/ProExcelDev
>
>
>

Hi IoM,

> Why Excel exports 65535 lines(maximum number) instead of only those with
> useful data?

It certainly shouldn't do! I guess it depends how you created and defined
your List object.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

I also think it should not do so. BUT, i also tried mapping by hand in Excel
(not with macro). It was the same result. The problem is inside Excel i
think. I need a way to trick it .

My conclusion:
If you try to map an element of the map to a cell, a range of cells of the
same column or to a whole column the result is the same. It mapps the column
to the element of the map, that means it exports the whole column, 65535 line
no matter useful data.

"Stephen Bullen" wrote:

> Hi IoM,
>
> > Why Excel exports 65535 lines(maximum number) instead of only those with
> > useful data?
>
> It certainly shouldn't do! I guess it depends how you created and defined
> your List object.
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
>
> Professional Excel Development
> The most advanced Excel VBA book available
> www.oaltd.co.uk/ProExcelDev
>
>
>

Hi IoM,

> If you try to map an element of the map to a cell, a range of cells of the
> same column or to a whole column the result is the same. It mapps the column
> to the element of the map, that means it exports the whole column, 65535 line
> no matter useful data.

That's not what I see here. If I map an element to a cell, I only get one item
exported. If I create a List on the sheet and map an element to a column in the
list, I get an item for each row of the list, but no more.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Hi all.

I'm really sorry to ressurrect an old thread but this is the closest to answer I've been able to find through using google.

I have the same issue in that I have created my XSD file and pulled it in to Excel. When I manually drag it on to the cell I want it applies the XSD across all columns and rows and I can then export the XML. The problem is a I have no idea how to use this in a macro. I 've looked at the thread above and used the solution and googled ListColumn.XPath.SetValue syntax which the thread opener used as solution but I just can't get it working.

Is someone able to very kindly just show me what the syntax should look like if I'm applying to sheet A Cell B1 asnd then I can adapt it to use on all sheets in the workbook.

thanks very much.