Eventdriven XML Export

theracer

New Member
Joined
May 3, 2010
Messages
9
Hi,

I have a range on a single column consisting of 0s and 1s that I want to package and export automatically to an XML file on my PC.

Unfortunately Excel seems to have some problems parsing XML so I was wondering if anybody can help me out with this.

I am a pretty new to both VBA and XML so I tried to construct taking bits and pieces from books and forums
The XML Schema looking like this:

<?XML version=”1. 0” ?>
<XSD: schema xmlns: XSD=” http: //www. w3. org/2 001/XMLSchema”>
<XSD: element name=”Activation” type=” XSD:Boolean”
minOccurs=”0” maxOccurs=”500” />
</XSD: schema>

Then in the VBA Workbook:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim xmp As XmlMap
Set xmp = _
Application.Workbooks(1).XmlMaps.Add("C:\Documents and Settings\lvonprel\Desktop\x- Files\XML Uploads\XMLSchemeControl3.xsd")
xmp.Name = "c_dr_pre_control"

Dim cll As Range
Dim xp As XPath

Set xp = Worksheets("c_dr_pre").Range("B4:B202").XPath
xp.SetValue ActiveWorkbook.XmlMaps(1), "/Activation"

MsgBox cll.XPath.Value

xp.SetValue xmp, "/ns1:Activation"

ActiveWorkbook.XmlMaps("c_dr_pre_control").Export URL:="C:\Documents and Settings\lvonprel\Desktop\x- Files\XML Uploads", _
Overwrite:=True

End Sub

Does anybody have a better code to run this kind of eventdriven function?

Thanks in advance,
Best
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
I've seen very few xml mapping questions satisfactorily resolved. Your probably on your own :( Though for my part I can see no reason why you'd be exporting data to XML on every calculation of the worksheet - its seems an odd thing to do in a calculate event.
 

theracer

New Member
Joined
May 3, 2010
Messages
9
Hi Xenou,

thanks for the reply. I want to export the XML in order to synch the Excel file with an Xcelsius Dashboard I'm preparing which does not support the inherent complications of the sheet... Guess I'llhave to find some-way around it... Thanks,
L
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
It shouldn't be too hard really. It just seems no one knows how to do these things. My efforts haven't been very successful either. However, if you have the xml schema or DTD and your data isn't complicated, you may be able to create the xml yourself with a vba procedure. I don't know anything about xcelsius. WIll it read a csv file or text file?
 

Forum statistics

Threads
1,082,478
Messages
5,365,789
Members
400,851
Latest member
FrankNStein

Some videos you may like

This Week's Hot Topics

Top