PDA

View Full Version : Eventdriven XML Export



theracer
06-04-2010, 08:15 AM
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 I constructed an XML Schema looking like this:

<?XML version=”1. 0” ?>
<XSD: schema xmlns: XSD=” Link”>
<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("Location.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:="Location", _
Overwrite:=True

End Sub

Does anybody have better code/way to do this?

Thanks in advance & Best

theracer
06-06-2010, 09:47 AM
Bump

slurpee55
06-16-2010, 08:45 AM
I truly don't know if this is along the lines of what you want - I am far from any kind of VBA whiz, but Raymond Pang made this for me when I was converting a lot of Excel files to XML. Obviously it will have to be converted quite a bit to meet your needs, but....