locked
Excel VBA Coding for xls transformation along with Paramters RRS feed

  • Question

  • Hi ,

    I need to parse xsl along with its parameters using VBA code.

    I can use VBA code from the below link(for your reference) but only thing is I need to pass XSLT Parameters through VBA code.

    //social.msdn.microsoft.com/Forums/en-US/ba46717f-66ee-46fd-a7eb-df4de17d9371/excel-vba-coding-for-xls-transformation?forum=isvvba

    My (ds_test.xsl) file

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
       <xsl:output method="xml" indent="yes" />
       <xsl:param name="job" />
       <xsl:param name="src" />
       <xsl:param name="spath" />
       <xsl:template match="/">
          <DSExport>
             <Job>
                <xsl:attribute name="Identifier">
                   <xsl:value-of select="$job" />
                </xsl:attribute>
                <Record Identifier="V25S0P1" Type="CustomOutput" Readonly="0">
                   <Collection Name="Columns" Type="OutputColumn">
                      <xsl:copy-of select="document($src)//Record[@Identifier=$spath]//SubRecord" />
                   </Collection>
                </Record>
             </Job>
          </DSExport>
       </xsl:template>
    </xsl:stylesheet>


    My input (Metadata.xml) file

    <?xml version="1.0" encoding="UTF-8"?>
    <DSExport>
       <Header CharacterSet="CP1252" ExportingTool="IBM InfoSphere DataStage Export" ToolVersion="8" ServerName="HCL-BOEING-DS" ToolInstanceID="EFBI_BAL_OPT" Date="2014-01-21" Time="19.09.04" ServerVersion="9.1" />
       <TableDefinitions>
          <Record Identifier="TEST1" DateModified="2013-12-23" TimeModified="11.01.03" Type="MetaTable" Readonly="0">
             <Collection Name="Columns" Type="MetaColumn">
                <SubRecord>
                   <Property Name="Name">BEMSID</Property>
                   <Property Name="Description">BEMSID: string[max=10]</Property>
                   <Property Name="SqlType">12</Property>
                   <Property Name="Precision">10</Property>
                </SubRecord>
                <SubRecord>
                   <Property Name="Name">EMPL_NM</Property>
                   <Property Name="Description">EMPL_NM: string[max=18]</Property>
                   <Property Name="SqlType">12</Property>
                   <Property Name="Precision">18</Property>
                </SubRecord>
             </Collection>
          </Record>
       </TableDefinitions>
    </DSExport>

    My Expected Output in format of XML (output.xml)

    Note : Here is my question - In runtime I should pass XSL parameter's value through Excel cell or VBA variables

    Let us assume that I am giving the below parameter value

    $job = "PXJ_TEST1"

    $src = "Metadata.xml"     

    $spath = "TEST1"

    output.xml should be in below format

    <?xml version="1.0" encoding="UTF-8"?>
    <DSExport>
       <Job Identifier="PXJ_TEST1">
          <Record Identifier="V25S0P1" Type="CustomOutput" Readonly="0">
             <Collection Name="Columns" Type="OutputColumn">
                <SubRecord>
                   <Property Name="Name">BEMSID</Property>
                   <Property Name="Description">BEMSID: string[max=10]</Property>
                   <Property Name="SqlType">12</Property>
                   <Property Name="Precision">10</Property>
                </SubRecord>
                <SubRecord>
                   <Property Name="Name">EMPL_NM</Property>
                   <Property Name="Description">EMPL_NM: string[max=18]</Property>
                   <Property Name="SqlType">12</Property>
                   <Property Name="Precision">18</Property>
                </SubRecord>
             </Collection>
          </Record>
       </Job>
    </DSExport>

    Thanks,

    Elavarasan



    • Edited by Elavarasan S Wednesday, January 29, 2014 12:05 PM Hperlink added
    Wednesday, January 29, 2014 8:14 AM