Record macro to export xml

[IoM]

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.

 

[Patrick Molloy]

please show us your code.

 

[IoM]

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

 

[Stephen Bullen]

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

 

[IoM]

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]

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

 

[IoM]

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]

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