Changing export components
With the assistance of resource editors, you can change the export component resources, such as the icons, strings, and so on. You can also change the scripts that are stored in .dll files to further customize export component behavior.
The following example illustrates the process of customizing the ExportExcel.dll by changing and debugging the file’s internal scripts.
The export components are only offered as 32-bit, regardless of whether you install the x86 32-bit or x64 64-bit version of UNICOM Intelligence Reporter - Survey Tabulation.
Open ExportExcel.dll in Microsoft Visual Studio
In Visual Studio, select File > Open > File, and then go to the ExportExcel.dll file location.
By default, Visual Studio opens the DLL file in the resource editor.
Extracting the VBA scripts
1 Expand the script section to view the resource files that contain scripts. In this example, the files 106, 107, and 110 contain scripts.
2 Double-click a file to display the script contents.
The contents are not usable in the presented format. Copying the content to a text editor will display the information as plain text.
3 Use CTRL+A to copy the contents and then CTRL+C to paste the contents into a text editor.
Adding content by using the Excel VBA editor
The next step entails setting up the debugging environment in Excel.
1 Launch Excel and then press Alt+F11 to open the Visual Basic Editor.
2 In the left top pane, right-click the folder button and add a module to the project.
3 Following the instructions in section
Extracting the VBA scripts, paste the contents of file
106 into the new module and rename the module
Main.
4 Right-click the folder button and add a class to the project.
5 Paste the contents of file 107 into the new class and rename the class CTableRender.
6 Right-click the folder button and add a class to the project.
7 Paste the contents of file 110 into the new class and rename the class CChartBlock.
After adding the above content, the VBA project will resemble the following:
Considering that the project uses COM components, you will need to define the appropriate references.
8 Select References from the Tools menu to open the References dialog.
9 Select UNICOM Intelligence Reporter Excel Export 1.0 Type Library, UNICOM Intelligence Reporter TOM 1.0 Type Library, and Microsoft XML from the list of available references, and then click OK.
Creating the main test function
At this point, all of the scripts have been transferred to the current VBA project. You can change the scripts to test the final output. The main purpose of the export is to establish the Export method in the Main module. Looking at the function, we can see that the function requires an Export instance handle, a string containing the tables XML, and the export property XML file.
In order to drive the export process, you need to create an entry function and pass the necessary parameters. The content of propertiesExcel.xml can be obtained from the associated DLL file. Using the Visual Studio resource editor, you can obtain the content of propertiesExcel.xml from the PROPERTIES folder.
Take the following sample code as an example:
Sub TestExport()
'Define constants for file locations used in the script
Const MDD_FILE = "C:\Program Files\IBM\SPSS\DataCollection\6\DDL\Data\Xml\Museum.mdd"
Const XML_FILE = "C:\Program Files\IBM\SPSS\DataCollection\6\DDL\Data\Xml\Museum.xml"
Const PROPERTY_XML_FILE = " C:\PropertiesExcel.xml "
Dim TableDoc As TOMLib.Document
Dim DDL_HOME As String
DDL_HOME = "C:\Program Files\IBM\SPSS\DataCollection\6\DDL\Data\XML"
Set TableDoc = New TOMLib.Document
' Load the Museum sample XML data set
Call TableDoc.DataSet.Load(MDD_FILE, , XML_FILE, "mrXmlDsc")
' ---- Create the tables and Populate the Tables----
With TableDoc.Tables
Call .AddNew("Table1", "age * gender", "Age by Gender")
Call .AddNew("Table2", "Distance", "Distance from home to museum")
Call .AddNew("Table3", "before * distance", "Before by Distance")
End With
TableDoc.Populate
' ---- Get TableXML and PropertiesXML ----
Dim TableXml As String
Dim PropertyXml As String
Dim Property As TOMLib.IProperties
Dim MyXml As MSXML2.DOMDocument
TableXml = TableDoc.GetTablesXml
Set MyXml = New MSXML2.DOMDocument
MyXml.Load PROPERTY_XML_FILE
PropertyXml = MyXml.xml
' ---- Export the Tables ----
Dim MYEXPORT As ExportExcelLib.Export
Set MYEXPORT = New ExportExcelLib.Export
Export MYEXPORT, TableXml, PropertyXml
End Sub
Press F5, in the Visual Studio resource editor, to run this function. The export results display in the current Excel worksheet. You can set break points to debug the entire script.
Saving the changed scripts back to ExportExcel.dll
After making the appropriate changes, you can use Visual Studio to save the updated scripts to ExportExcel.dll.
1 Select the content for a file in VBA (Ctrl+A), and then copy the file contents (Ctrl+C).
2 Select the appropriate file in the Visual Studio Resource Editor.
3 Select the file contents (Ctrl+A), and then paste the contents (Ctrl+V). This replaces the old scripts.
4 Repeat these steps for each script.
5 Click File > Save.
The updates take effect next time that the component is used.
See also
