XSL applied with VBA

Hello, everybody, Derek, Brenda

Acctually I'm doing a test based in Instructional program evaluation faced with library resources.
The goal of these evaluationsis to assess the adequacy of library resources to the needs of courses offered within a program and to determine the ability of the library to support teaching and related research.

This methodology of collection evaluation based on the analysis of the content of individual courses and to assess its effectiveness for the needs of instructional program evaluation.

Then, the described method was collection-centered, comparative, quantitative. The evaluation included books pertaining to every course offered by the Colleges, as well as the ranking of
courses by the size of the supporting collectionas the major practical outcome of the overall
collection evaluation.

To turn this true, I'm using XSLT that takes on ISBN parameter to realize this comparation between Instructional_program.xml and the collection.xml

see the code from my XSL

< xml version="1.0" encoding="utf-8" >

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" encoding="ISO-8859-1" doctype-public="-//W3C//DTD HTML 4.0 Transitional//EN" />

<xsl:variable name="course" select="//course" />
<xsl:param name="compare-file-name" select="'library.xml'"/>
<xsl:variable name="second" select="document($compare-file-name)"/>

<xsl:key name="by-model" match="reference" use="isbn" />

<xsl:template match="/">

<html>

</html>

<xsl:for-each select="$course[@name='Library_Science']">

<table border="1" width="70%">

<tr>
<th>discipline</th>
<th>suggests</th>
<th>available</th>
<th>non available</th>

</tr>

<xsl:variable name="other" select="($second)/references/reference[format = current()/instructional-program/references/reference/format]"/>

<xsl:variable name="suggest_mid-nineties">

<xsl:value-of select="count(instructional-program/references/reference[publish_date > 1994 and publish_date < 2000 and generate-id(.) =
generate-id(key('by-model',isbn)[1]) ])"/>

</xsl:variable>

<xsl:variable name="suggests-two-thousand">

<xsl:value-of select="count(instructional-program/references/reference[publish_date > 1999 and publish_date < 2006 and generate-id(.) =
generate-id(key('by-model',isbn)[1]) ])"/>
</xsl:variable>

<xsl:variable name="total">
<xsl:value-of select="($suggest_mid-nineties + $suggests-two-thousand)"/>

</xsl:variable>

<tr>

<td><b>1995-1999</b></td>

<td align="center"><xsl:value-of select="($suggest_mid-nineties)"/> </td>

<xsl:variable name="otelo3" select="($second)/references/reference[publish_date > 1994 and publish_date < 2000 and isbn = current()/instructional-program/references/reference/isbn]"/>

<xsl:variable name="collection_mid-nineties">
<xsl:value-of select="count($otelo3/isbn)"/>
</xsl:variable>

<td align="center"><xsl:value-of select="($collection_mid-nineties)"/>

</td>

<td align="center"><xsl:value-of select="($suggest_mid-nineties - $collection_mid-nineties)"/> </td>

</tr>

<tr>

<td><b>2000-2005</b></td>

<td align="center"><xsl:value-of select="($suggests-two-thousand)"/>

</td>

<xsl:variable name="otelo4" select="($second)/references/reference[publish_date > 1999 and isbn = current()/instructional-program/references/reference/isbn]"/>

<xsl:variable name="non-available_two-thousand">
<xsl:value-of select="count($otelo4/isbn)"/>
</xsl:variable>

<td align="center"><xsl:value-of select="($non-available_two-thousand)"/>
</td>

<td align="center"><xsl:value-of select="($suggests-two-thousand - $non-available_two-thousand)"/> </td>

</tr>

</table>

<br/><br/>
</xsl:for-each>

My instructional program.xml, something like

< xml version="1.0" >
< xml-stylesheet type="text/xsl" href="compara.xsl" >
<UFES>

<instituition name="UNIVERSIDADE FEDERAL DOS ESPIRITO SANTO -UFES"/>
<center name="Centro de ciencias Juridicas e Economicas-CCJE">
<departaments>
<departament name="Information Science">
<course name="Library_Science">
<instructional-program id="Lib-03901">
<descricao>Automation</descricao>
<teacher>Elias de Oliveira</teacher>

<references>
<reference>
<title>XML Developer's Guide</title>
<author>Gambardella, Matthew</author>
<isbn>059644-3</isbn>
<publish_date>1996</publish_date>
</reference>
<reference>
<title>Midnight Rain</title>
<author>Ralls, Kim</author>
<isbn>0481135-2</isbn>
<publish_date>1999</publish_date>

and the collection from the library.xml

< xml version="1.0" encoding="UTF-8" >

<references>
<reference>
<title>XML Developer's Guide</title>
<author>Gambardella, Matthew</author>
<isbn>059644-3</isbn>
<publish_date>1996</publish_date>
</reference>
<reference>
<title>Maeve Ascendant</title>
<author>Corets, Eva</author>
<isbn>0600511-4</isbn>
<publish_date>2000</publish_date>

It works Well, but my problem now it's... I need to work with the viewed datas.
Thinking about this I long to make this in a tool like Excel.
Well, would N1 tell me if MSXML and VBA resolve this

Any sample will be appreciate.

ThanX

Answer this question

XSL applied with VBA

vishal868
Oh, I'm sorry,

I forgot to say, this is a bigger code then I cutted it off a few. This is the cause for bugs founded.

Marcos Hercules

Bryant Likes

Hi Marcos,

Read up a bit on XSLT and MSXML and it's not that tricky to do. Right now I don't have the time to create a sample but here's the basics. Very simple...

You need to create two DOMDocuments objects in your code, one for your first xml file and one for your stylesheet.

Then you load the xml and xslt files into their DOMDocuments. Call the transformNode() method of the DOMDocument containing the xml passing the xslt DOM as an argument. This will return a string containing the results which you can save to file.

There is another method transformNodeToObject that lets you pass another DOMDocument object for holding the results. Thats a better approach for yourself because you can then use the result tree.

Hopefully importing the second xml file in the stylesheet using the document() statement won't cause a problem during transformation. If it does then the error message might give you a clue on how to fix the problem.

Here is an example taking from the book...

Dim Source As New Msxml2.DOMDocument40
Dim stylesheet As New Msxml2.DOMDocument40
Dim stylesheet2 As New Msxml2.DOMDocument40
Dim result As New Msxml2.DOMDocument40
Dim result2 As New Msxml2.DOMDocument40

' Load data.
Source.async = False
Source.Load "sample.xml"
If (xmlDoc.parseError.errorCode <> 0) Then
   Dim myErr
   Set myErr = xmlDoc.parseError
   MsgBox("You have error " & myErr.reason)
Else
   ' Load style sheet.
   stylesheet.async = False
   stylesheet.Load "stylesheet1.xsl"
   If (xmlDoc.parseError.errorCode <> 0) Then
      Dim myErr
      Set myErr = xmlDoc.parseError
      MsgBox("You have error " & myErr.reason)
   Else
      ' Set up the resulting document.
      result.async = False
      result.validateOnParse = True
      result2.async = False
      result2.validateOnParse = True

      ' Parse results into a result DOM Document.
      Source.transformNodeToObject stylesheet, result

      stylesheet2.async = False
      stylesheet2.Load "stylesheet2.xsl"
      If (xmlDoc.parseError.errorCode <> 0) Then
         Dim myErr
         Set myErr = xmlDoc.parseError
         MsgBox("You have error " & myErr.reason)
      Else
         result.transformNodeToObject stylesheet2, result2
         MsgBox result2.xml
      End If
   End If
End If

If you haven't downloaded it yet get the MSXML4.0 SDK as it has an excellent help file.

omarf

Hey Marcos,

Looks like your wanting to store the result tree of the transformation so you can use the transformed data.

Think I see a small bug in your XSLT but it could just of been typed in wrong

<xsl:key name="by-model" match="reference" use="isbn" />

<xsl:template match="/">

<html>

</html>

.... should be

<xsl:key name="by-model" match="reference" use="isbn" />

<html>
<xsl:template match="/">

</html>

Is that what your looking for A way to use MSXML to call your stylesheet and write the results back to memory (DOM) (or to file)

Because I know it can be done.
Sinan Do?anl?
Hi, Derk
I think it's better use MSXML to call the stylesheet and writing the results to file.

Marcos Hercules
XSL applied with VBA

Answer this question