用于xls转换的Excel VBA编码以及参数 [英] Excel VBA Coding for xls transformation along with Paramters
问题描述
我需要使用VBA代码解析xsl及其参数. 我可以从下面的链接中使用VBA代码(供您参考),但唯一的事情是我需要通过VBA代码传递XSLT参数.
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.
<?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>
我的输入(Metadata.xml)文件
<?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>
我的XML(output.xml)格式的预期输出
注意:这是我的问题-正如我前面提到的,在运行时我应该通过Excel VBA代码传递XSL参数的值.让我们假设我给出以下参数值
Note : Here is my question - In runtime I should pass XSL parameter's value through Excel VBA code as I mentioned earlier. Let us assume that I am giving the below parameter value
output.xml 应该采用以下格式
如果需要设置参数,则使用transformNode的简单方法不起作用,您需要使用 http://msdn .microsoft.com/en-us/library/ms762312%28v = vs.85%29.aspx If you need to set parameters then the simple approach with transformNode does not work, you need to use the API presented in http://msdn.microsoft.com/en-us/library/ms762799%28v=vs.85%29.aspx (the page only has JScript and C++ unfortunately but of course you can use the same API and objects with VBScript or VBA). Then you can use the method http://msdn.microsoft.com/en-us/library/ms762312%28v=vs.85%29.aspx 这篇关于用于xls转换的Excel VBA编码以及参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
<?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>
推荐答案
addParameter
设置参数.addParameter
to set a parameter.