Microsoft Word and Excel Reports

The following steps should only need to be performed once.

If the Developer tab does not display in Excel:

  1. In Excel, click File>Options>Customize Ribbon.
  2. With Main Tabs selected from the Customize the Ribbon drop list (1), select the Developer check box (2):

    ex_select_developer_tab.png

In order to run the macros that we will create with our reports we need to ensure that the Security level in Excel is appropriate:

  1. On the Developer tab, click Macro Security.
  2. If it is not already selected, click the radio button for Disable all macros with notification:

    db_macro_settings_2013.png

    With this setting, macros are disabled, but security alerts appear if there are macros present. You enable macros on a case-by-case basis.

    Note: You could select Enable all macros in order to avoid the need to enable macros on a case-by-case basis however be aware that there is a security risk in doing so.

  3. Click OK to close the Trust Center.

When we run a report in EMu, one or more files are generated with the report data. These files hold the data that the third party reporting tools (Crystal Reports, Microsoft Word and Excel for instance) use to generate the report.

What kind of files are generated will depend on the Type of report you are creating:

  • EMu 4.3 and earlier: report generation typically makes use of ODBC (Open Database Connectivity) and most report Types will generate one or more .csv files.
  • EMu 5.0 onwards: it is also possible to create ADO reports which report directly to an ActiveX Data Objects (ADO) RecordSet object (creating a .xml file) rather than reporting to an ODBC data source (which creates one or more .csv files).

Note: More detail about what type of files are created and where they are located is available here.

Read on for details about:

When mail merge facilities were introduced to Microsoft Word, the source of the data for merging in the document was a simple text file. Users were required to edit a text file and add data, one entry per line for each mail merge record required. To run the mail merge, Word needed to know the exact location of the text file that contained the mail merge records. It stored this location in the master merge document so that it could regenerate the merged document when required.

With the introduction of ODBC by Microsoft, the data source is hidden behind a name (DSN) that programs such as Word and Excel can use without needing to know the location of the data source. When creating a report in MS Word or Excel in versions prior to Office 2013 it is important that the DSN is specified using Visual Basic code.

In versions of MS Word and Excel from Office 2013 onwards it is not necessary to specify the DSN using Visual Basic code and the DSN can be specified using a Wizard.

This table provides details of EMu DSN names and the name of the .csv files generated when a report is run out of EMu:

Module Name

DSN

.csv File

Link Column
Accession Lots EMu Accession Lots eaccessi.csv eaccessionlots_key
Admin Tasks EMu Admin Tasks eadmin.csv eadmin_key
Audit Trails EMu Audit Trails eaudit.csv eaudit_key
Bibliography EMu Bibliography ebibliog.csv ebibliography_key
Catalogue EMu Catalogue ecatalog.csv ecatalogue_key
Collection Events EMu Collection Events ecollect.csv ecollectionevents_key
Condition Checks EMu Condition Checks econditi.csv econdition_key
Conservation EMu Conservation econserv.csv econservation_key
Events EMu Events eevents.csv eevents_key
Gazetteer EMu Gazetteer egazette.csv egazetteer_key
Groups EMu Groups egroups.csv egroups_key
Insurance EMu Insurance einsuran.csv einsurance_key
Internal Movements EMu Internal Movements einterna.csv einternal_key
Loans EMu Loans eloans.csv eloans_key
Locations EMu Locations elocatio.csv elocations_key
Lookup Lists EMu Lookup Lists eluts.csv eluts_key
Movements EMu Movements emovemen.csv emovements_key
Multimedia EMu Multimedia emultime.csv emultimedia_key
Narratives EMu Narratives enarrati.csv enarratives_key
Parties EMu Parties eparties.csv eparties_key
Propagation EMu Propagation epropaga.csv epropagation_key
Registry EMu Registry eregistr.csv eregistry_key
Rights EMu Rights erights.csv erights_key
Sites EMu Sites esites.csv esites_key
Statistics EMu Statistics estatist.csv estatistics_key
Task Templates EMu Task Templates etemplat.csv etemplate_key
Taxonomy EMu Taxonomy etaxonom.csv etaxonomy_key
Thesaurus EMu Thesaurus ethesaur.csv ethesaurus_key
Transactions EMu Transactions etransac.csv etransactions_key
Valuations EMu Valuations evaluati.csv evaluations_key
Voucher Payments EMu Voucher Payments evoucher.csv evoucher_key

DSNs are stored (and can be modified) in the ODBC Data Source Administrator, which can be accessed by selecting:

  • Windows 7: Start>All Programs>Administrative Tools>Data Sources (ODBC)
  • Windows 8: Control Panel>System and Security>Administrative Tools>ODBC Data Sources

  • Windows 8/10: Control Panel>System and Security>Administrative Tools>ODBC Data Sources (32-bit)

  • Windows 8.1: Control Panel >Administrative Tools>ODBC Data Sources (32-bit)

To connect to an ADO RecordSet when building a Microsoft ADO Report it is necessary to ensure that Word and Excel are configured to use the Microsoft ActiveX Data Objects Library, and to specify the link to the ADO RecordSet:

  1. On the Developer tab in Excel, click Visual Basic.

    Microsoft Visual Basic for Applications will open.

    Tip: If the Developer tab does not display, ensure that Excel has been set up correctly.

  2. Select Tools>References from the Menu bar.

    The References - VBAProject box displays.

  3. If the checkbox for Microsoft ActiveX Data Objects 6.1 Library is not ticked, click the checkbox:

  4. Click OK.

When writing the Visual Basic code for your report it is necessary to specify the location of the xmldata file generated by EMu.

The location of this file can vary, but typically it can be found in:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

For example, a report run in the Parties module, will save the xmldata file to:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

Note: More details here.

Things to note:

  • The xmldata file is always called xmldata.xml.
  • xmldata.xml is located in a folder named after the module in which the report was run. In the following example, the report was run in the Parties module and xmldata.xml is located in a folder called eparties:

The following needs to be added to your VB code (be sure to specify the correct module folder):

'Get the persisted record set
source = Environ("LocalAppData") & "\KESoftware\Reports\eparties\xmldata.xml"
Set RecordSet = New ADODB.RecordSet
RecordSet.Open source, "Provider=MSPersist"

Tip: Example code for an Excel ADO Report is provided here.