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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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,136,329
Messages
5,675,142
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top