By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,947 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,947 IT Pros & Developers. It's quick & easy.

Exporting Data from Excel as XML using XmlMap

P: n/a
Hi,

Using the Excel object in Visual Studio 2005 I'm trying to export an
Excel ObjectList to a string as XML. I'm trying to read out the three
columns referenced my an Excel Range, but when I run the code I hit the
line where it tries to set map the first column and get an error
message saying "The XPath is not valid because either the XPath syntax
is incorrect or not supported by Excel." As far as I can see the XPath
syntax is correct, does anyone know why I'm seeing this?

My code is:

'myExcelWS is defined as my Excel Worksheet
'myExcelRangeOut is defined as an Excel range of three columns
'sXml is defined as a String containing the content of my .xsd file

Dim xmp As XmlMap
xmp = myExcelWorkbook.XmlMaps.Add(sXml)
xmp.Name = "Output"

Dim xmlListOut As Excel.ListObject
xmlListOut = myExcelWS.ListObjects.Add
xmlListOut.Resize(myExcelRangeOut)

xmlListOut.ListColumns(1).XPath.SetValue(xmp,
"/account/result/week")
xmlListOut.ListColumns(2).XPath.SetValue(xmp,
"/account/result/weeksearch")
xmlListOut.ListColumns(3).XPath.SetValue(xmp,
"/account/result/answersearch")
Dim sXmlOutput As String = ""

xmp.ExportXml(sXmlOutput)

And my Schema (stored in the string sXml) looks like this:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="InputData"
targetNamespace="http://tempuri.org/InputData.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/InputData.xsd"
xmlns:mstns="http://tempuri.org/InputData.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="account">
<xs:complexType>
<xs:sequence>
<xs:element name="result" maxOccurs="unbounded" minOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="week" type="xs:string" />
<xs:element name="weeksearch" type="xs:string" />
<xs:element name="answersearch" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Thanks in advance,

Cassidy.

Feb 28 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.