Hi Steve,
I'm trying to use the method described in Knowledge Base
article 295005 to covert an Excel XML spreadsheet file
into a generic xml file using an xsl file I have supplied.
I can get the macro to run, and it produces a file, but
the file is empty. Any ideas why?
Probably because your xsl file is missing the namespace prefixes for the XML-SS elements, or you're getting them wrong, or your using a different
case in your definitions.
For example, the following is the xsl I used to convert an Excel XML spreadsheet file to a 'timesheet' XML, where most things in the file have
named ranges, hence all the NamedCell calls. Note that the schemas I'll be using are defined at the top and all my element references have the
appropriate 'xl:' or 'xsl:' prefixes:
(watch out for word-wrap!)
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="
http://www.w3.org/1999/XSL/Transform"
xmlns:xl="urn:schemas-microsoft-com
ffice:spreadsheet"
exclude-result-prefixes="xl">
<xsl:template match="/xl:Workbook/xl:Worksheet/xl:Table">
<TS:Timesheet xmlns:TS="urn:schemas_wrox_com:ExcelProgRef:Timesheet">
<TS:Employee>
<TS:FirstName><xsl:value-of select="xl:Row/xl:Cell[xl:NamedCell[@xl:Name='ForeName']]/xl
ata"/></TS:FirstName>
<TS:LastName><xsl:value-of select="xl:Row/xl:Cell[xl:NamedCell[@xl:Name='Surname']]/xl
ata"/></TS:LastName>
<TS:EmployeeNumber><xsl:value-of select="xl:Row/xl:Cell[xl:NamedCell[@xl:Name='EmpNo']]/xl
ata"/></TS:EmployeeNumber>
</TS:Employee>
<TS:ReportingMonth>
<TS:Year><xsl:value-of select="substring(xl:Row/xl:Cell[xl:NamedCell[@xl:Name='Month']]/xl
ata,1,4)"/></TS:Year>
<TS:Month><xsl:value-of select="substring(xl:Row/xl:Cell[xl:NamedCell[@xl:Name='Month']]/xl
ata,6,2)"/></TS:Month>
<TS:TotalStdHours><xsl:value-of select="xl:Row/xl:Cell[xl:NamedCell[@xl:Name='StdHours']]/xl
ata"/></TS:TotalStdHours>
</TS:ReportingMonth>
<xsl:for-each select="xl:Row/xl:Cell[xl:NamedCell[@xl:Name='Days']]">
<xsl:if test="string(number(../xl:Cell[xl:NamedCell[@xl:Name='Hours']]/xl
ata))!='NaN'">
<TS:HoursWorked>
<TS
ay><xsl:value-of select="../xl:Cell[xl:NamedCell[@xl:Name='Days']]/xl
ata"/></TS
ay>
<TS:StdHours><xsl:value-of select="../xl:Cell[xl:NamedCell[@xl:Name='Hours']]/xl
ata"/></TS:StdHours>
<xsl:choose>
<xsl:when test="string(number(../xl:Cell[xl:NamedCell[@xl:Name='Overtime']]/xl
ata))!='NaN'">
<TS:Overtime><xsl:value-of select="../xl:Cell[xl:NamedCell[@xl:Name='Overtime']]/xl
ata"/></TS:Overtime>
</xsl:when>
<xsl
therwise><TS:Overtime/></xsl
therwise>
</xsl:choose>
</TS:HoursWorked>
</xsl:if>
</xsl:for-each>
</TS:Timesheet>
</xsl:template>
</xsl:stylesheet>
Regards
Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk