Export properties XML
You will typically want to customize the output properties when exporting tables. For export, the customized properties are configured and stored in an export property XML file.
The following is an outline of the export properties XML file:
<properties>
<property>
<name>ChartCategoryElements</name>
<type>8</type>
<value><![CDATA[Per Variable]]></value>
</property>
...
<property>
<name>ChartRowsAsSeries</name>
<type>11</type>
<value><![CDATA[-1]]></value>
</property>
...
</properties>
The properties are organized as a collection. Each property consists of three attributes: <name>, <type>, and <value>.
When writing script, you will need to create a properties XML file, modify the appropriate attributes, and then pass the files to the export interface. Export parses the XML file prior to exporting.
Manually creating and editing the properties can prove problematic in that is possible to make mistakes (typos, syntax, and so on). For this reason, it is strongly recommended that you set the properties through the TOM interface. The following functions can be used to manipulate the properties XML file:
Function GetPropertiesFromTableDoc(TableDoc, ExportType)
Select Case ExportType
Case "Excel"
Set GetPropertiesFromTableDoc = TableDoc.Exports["mrExcelExport"].Properties.GetInterface("{F359CAFF-9765-4ecd-8F32-EA83649D2A0C}")
Case "HTML"
Set GetPropertiesFromTableDoc = TableDoc.Exports["mrHtmlExport"].Properties.GetInterface("{F359CAFF-9765-4ecd-8F32-EA83649D2A0C}")
Case "PPT"
Set GetPropertiesFromTableDoc = TableDoc.Exports["mrPowerpointExport"].Properties.GetInterface("{F359CAFF-9765-4ecd-8F32-EA83649D2A0C}")
Case "Text"
Set GetPropertiesFromTableDoc = TableDoc.Exports["mrTextExport"].Properties.GetInterface("{F359CAFF-9765-4ecd-8F32-EA83649D2A0C}")
Case "Word"
Set GetPropertiesFromTableDoc = TableDoc.Exports["mrWordExport"].Properties.GetInterface("{F359CAFF-9765-4ecd-8F32-EA83649D2A0C}")
End Select
End Function
Sub SetProperty(PropXML, Name, Value)
Dim oxProp, oxPropValue
oxProp = PropXML.selectSingleNode("properties/property [name = '"+Name+"']")
oxPropValue = oxProp.selectSingleNode("value")
oxPropValue.Text = Value
End Sub
You can call GetPropertiesFromTableDoc to obtain the properties object, pass the associated XML file to a DOM object, and then call SetProperty to configure the appropriate properties.
For example:
Dim ExportProperties
Set ExportProperties = GetPropertiesFromTableDoc(TableDoc,
ExportType)
Dim xmlDoc
Set xmlDoc = CreateObject("MSXML2.FreeThreadedDOMDocument.6.0")
If xmlDoc = Nothing Then
Set xmlDoc = CreateObject("MSXML2.FreeThreadedDOMDocument.4.0")
End If
xmlDoc.async = False
xmlDoc.loadXML(ExportProperties.XML["properties"])
SetProperty(xmlDoc, "Destination", EXPORT_FILE)
SetProperty(xmlDoc, "DisplayCharts", True)
Considering that the format of the document is controlled by TOM, the benefits of using this method become apparent when upgrading or performing maintenance.
See also