Step 2: Create the report in Microsoft Word

Next we create a mail merge report in Word. First we look at creating a report where only one .csv file is generated.

In this example, we use the main .csv file (einvoice.csv). In the majority of cases in which only a single .csv file is generated when a report is run out of Vitalware, the steps described here are all you will require to create a mail merge report. At this stage the report will include details about the transaction and the purchaser (but not the items purchased).

Note: The steps described here for connecting to the DSN are required whether you have one or more than one .csv file to access.

We will then adapt out report to access data included in the second .csv file (Invorder.csv) in order to include details about the objects loaned.

Microsoft Word opened when we ran the new report at the end of Step 1: Create the report in Vitalware.

In order to run the macros we will create we need to ensure that the Security level in Word is appropriate:

  1. Select Tools>Macro>Security from the Word Menu bar to display the Security box.
  2. Select the Medium radio button (if not already selected):

    Security

  3. Click OK to close the Security box.
  4. Select Tools>Macro>Visual Basic Editor (ALT+F11) from the Menu bar.

    The Microsoft Visual Basic editor opens.

  5. Select Tools>References from the Microsoft Visual Basic Editor Menu bar.

    The References - Project dialogue box displays.

  6. Place a tick in the Microsoft Activex Data Objects 2.7 Library check box and click OK to close the dialogue box.
  7. Under the Project (Document 1) folder in the left Project pane, double-click ThisDocument :

    Visual Basic

  8. First we set up the data source:

    Select Document from the first drop list and Open from the second drop list.

  9. Enter the following code between Private Sub Document_Open() and End Sub :

    '

    ' Setup the data source and run the merge

    '

    Set MainDocument = ActiveDocument

    With ActiveDocument.MailMerge

    .MainDocumentType = wdFormLetters

    .OpenDataSource _

    Name:="", _

    Connection:="DSN=KE Vitalware Invoices;", _

    SQLStatement:="SELECT * FROM einvoice.csv", _

    Subtype:=wdMergeSubTypeWord2000

    .Destination = wdSendToNewDocument

    .Execute Pause:=False

    End With

    '

    ' Force all fields to be updated in case we have images

    '

    ActiveDocument.Fields.Update

    MainDocument.Close SaveChanges:=False

    Note: See DSN table for details about Vitalware DSN names and the name of the .csv files generated.

  10. Select Close from the second drop list.
  11. Enter the following code between Private Sub Document_Close() and End Sub :

    ' When the document is closed we need to disconnect the

    ' data source otherwise Word will try and use it

    ' when opening the document

    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

    ActiveDocument.Save

    The Visual Basic Editor should look similar to the following screen:

    Note: While we're building our report we want to comment out the following line:
    MainDocument.Close SaveChanges:=False
    We do this by placing an apostrophe at the start of the line of code (as in the previous screenshot).
    If this line is not commented out, the master report document we are creating will not display when the report is run.
    We will need to remove this apostrophe after we have finished creating the report.

    Note: It is beyond the scope of this Help to explain the Visual Basic code (or how to program with Visual basic) in detail, and the following is provided for general information only.

    • The function Document_Open() is executed every time the document is opened in Word.
    • The line With ActiveDocument.MailMerge

      specifies that all lines that start with a dot (.) down to the closing End With statement actually have ActiveDocument.MailMerge prepended to them.

    • The merge document type is set with:

      .MainDocumentType = wdFormLetters

      There are many document types available which define how the merged document will appear. The value wdFormLetters indicates that we are producing a separate letter (page) for each merge record found. For a complete list of values available consult the Help system within the Visual Basic Editor (search using the keyword MainDocumentType).

    • The following lines set the DSN without specifying the location of the database files:

      .OpenDataSource _

      Name:="", _

      Connection:="DSN=KE Vitalware Invoices;", _

      SQLStatement:="SELECT * FROM einvoices.csv", _

      Subtype:=wdMergeSubTypeWord2000

      Lines ending with an underscore (_) indicate that the code continues on to the next line. The above lines are actually a single code statement. As you can see the name of the DSN is set in the Connection property (the value is KE Vitalware Invoices). If we were using data from the Parties module rather than the Invoices module, we would need to change the DSN from KE Vitalware Invoices to KE Vitalware Parties.

      Note: The DSN table can be used to determine the name of the DSN to use for each module.

      The SQLStatement defines a query that is used to retrieve the records required from the data source. The query SELECT * FROM einvoices.csv fetches all the records from the Invoices data source.

      The Subtype property is required for Word 2002 onwards.

      Note: If your institution uses an earlier version of Word, remove this line and the comma and underscore at the end of the previous line.
      If you are using Word 2000 or earlier, it may be necessary to add at least one field to the Word document before the macro will work (otherwise an error 4605 may be generated).

    • The next line of code indicates the destination of the resulting merge document:

      .Destination = wdSendToNewDocument

      The value wdSendToNewDocument sends the output to a new Word document. Consult the Help system within the Visual Basic Editor (search using the keyword Destination) for a complete list of possible destinations.

    • Now that the merge document type, data source and output destination are defined we can run the mail merge to produce the required document. The line:

      .Execute Pause:=False

      runs the mail merge, placing any error messages in the new document. When the merge is executing you will see two Word documents on your screen. The first document will be the master merge document (the one we are creating) and the second will be the new output document.

    • Once the merge is complete we force an update of all fields in the merged documents with the code:

      ActiveDocument.Fields.Update

      This line is only required if you have exported images from Vitalware via the report interface for inclusion in a report. If this is not the case, you can remove this line.

    • Finally we close the master mail merge document:

      MainDocument.Close SaveChanges:=False

      which will leave the new mail merge document as the only open document. While building a report it is useful to disable this statement so that you can test your mail merge document. If it is not disabled, a mail merge will be run every time we open the document (which is good), but we will not be able to get to the master merge document as it will be closed (which is bad). To disable the statement, place an apostrophe at the beginning of the line. We will remove the apostrophe after we have completed the report (see also Step 3: Edit the VB code to close the main report document).

    • The second function, Document_Close(), is executed when the document is closed. The code is used to save the document without its mail merge status. If a document is deemed to be a mail merge document, Word will automatically store the file location of the data source that is currently being used. As storing this location will make our report malfunction on different machines (since the file location may vary from machine to machine) we need to save the document as a "normal" Word document. The following lines of code reset the document type and save the document again:

      ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

      ActiveDocument.Save

  12. Select File>Close and Return to Microsoft Word from the Visual Basic editor Menu bar.

    The Visual Basic editor closes.

  13. Save the Word document with a descriptive name, e.g. Invoice.doc.
  14. Close the Word document.

Next we test that the data source code has been configured correctly.

The code added in 1. Write the VB code ensures that when the Word document is opened the data source is configured correctly without embedding the location of the DSN. We need to test that the data source code has been configured correctly:

  1. Open the main report document, e.g. Invoice.doc in this example.

    The Security box displays:

  2. Click Enable Macros.
  3. Two documents open:
    • The main report document, e.g. Invoice.doc.
    • A mail merge document (e.g. FormLetters1.doc) generated by the macros in the main report document. This document is blank because no text has yet been added to the main report document.

      Note: If this doesn't happen and a dialogue box opens looking to establish a connection to a data source, ensure that you commented out MainDocument.Close SaveChanges:=False at Step 9.

  4. Close FormLetters1.doc without saving it.
  5. Select View>Tool bars>Mail Merge to display the mail merge Tool bar:

    Mail merge toolbar

  6. Add some test text to the Invoice.doc document, e.g. This is a test:

  7. Save and close the main report document.
  8. Reopen the main report document, enabling macros when asked.

    A new mail merge document (e.g. FormLetters1.doc) should be generated displaying the test text, indicating that the data source code is working correctly.

  9. Delete the test text from the main report document, e.g. Invoice.doc, save and close the document.
  10. Close the mail merge document (e.g. FormLetters1.doc) without saving it.

Next we add text to the main report document.

  1. Open the main report document report, e.g. Invoice.doc in this example.

    The Security box displays:

  2. Click Enable Macros.
  3. Two documents open:
    1. The main report document, e.g. Invoice.doc.
    2. A mail merge document, e.g. FormLetters1.doc.
  4. Close the mail merge document without saving it.

    In the main report document we'll add an automatic date to the top of the document.

  5. Select Insert>Date and Time from the Microsoft Word Menu bar to display the Date and Time box.
  6. Select the required date format and ensure that the Update Automatically checkbox is ticked:

    Date and Time

    The current date will display on the page (and will be updated daily).

    Note: If you select Tools>Options from the Menu bar and then place a tick in the Field codes check box on the View tab, the code for this date field will display. Alternatively, use the keyboard shortcut, ALT+F9 to hide / display field codes.

  7. Below the date, enter text into the document. For example:

  8. Save and close the main report document.
  9. Reopen the main report document, enabling macros when asked.

    Again two documents are opened, the main report document (e.g. Invoice.doc) and the mail merge document (e.g. FormLetters1).

    The mail merge document now includes the text and date that were added to the main report document.

  10. Close the mail merge document (e.g. FormLetters1) without saving it.

Next we add fields to the main report document.

In the main report document (Invoice.doc in this example):

  1. Press ALT+F9.

    The document is now in Show Field Codes mode and the date field entered in the previous step should now display as code rather than a date.

  2. Place the cursor in the document where you want to insert a field, e.g. one space after Transaction Date in this example:

  3. Click Insert Merge Fields Insert Merge Fields button from the Mail Merge Tool bar.

    Note: If the Mail Merge Tool bar does not display, select Tools>Letters and Mailings>Show Mail Merge Tool bar from the Microsoft Word Menu bar.

    The Insert Merge Field box displays:

    Note: If a field you added to the report in Vitalware does not display in the Insert Merge Field box, it is probable that the field is a table of values and has been output as a separate .csv. If you wish to include it in the report, it is necessary to convert the field in Vitalware from Table to Text (see 1: Create the report in Vitalware for details) or to add additional Visual Basic code to access data in the second (third, etc.) .csv file.

  4. Select the field to insert, e.g. InvPaymentDate0 in this example, and click Insert

    -OR-

    Double click the field to insert.

  5. Close the Insert Merge Field box.

    In this example the following field code displays where the cursor had been placed:

    Loan Number: { MERGEFIELD "InvPaymentDate0"}

  6. Add the remaining fields to the main report document.

    Note: To find the correct field name to select, open the module in which the report was created, move to the field and press F1. The Vitalware Field Level help opens, displaying the field name.

  7. The main report document now displays a number of inserted field codes:

    Tip: Each field can be assigned a number of display and functional properties, e.g. to display in UPPERCASE; to add text either before or after the field value, etc.

    1. In the main report document, right-click a field (e.g. InvPaymentType_tab) and select Edit Field from the menu that displays:

      The Field box displays.

    2. Select the required format for the field in the Format list, e.g. Title case:

    3. Click OK.

      In this example, we'll also format the currency value (InvTotalOrdered):

      1. Type \# "$,0.00" after "InvTotalOrdered", giving:

      This will format currency values with a $ sign, a comma for thousands and two decimal places, e.g. $1,500.10.

    4. Save and close the main report document.
    5. Reopen the report document, enabling macros when asked:

    6. Close both documents without saving them.
  8. Save and close the main report document.
  9. Reopen the main report document, enabling macros when asked.

    Again two documents are opened, the main report document and the mail merge document.

    Note: You may need to select ALT+F9 (hide / display Field Codes) in order to display the date correctly.

    The mail merge document includes the text and data that was added to the main report document:

  10. Close both documents without saving them.

Next we add the Parties fields to the main report document. The Parties fields are used to:

  • Insert a suitable salutation after the greeting.
  • Add a mailing address.

The logic used for the salutation is:

  • Enter the salutation if it exists in the Parties record.
  • If the salutation does not exist, use the first name.
  • If the first name does not exist, use the text Sir/Madam.

This is achieved using the If...Then...Else field code.

To insert a salutation in the main report document:

  1. Open the main report document (Invoice.doc in this example), enabling macros when asked.
  2. In the main report document, select ALT+F9 to display field codes.
  3. Place the cursor just before the comma after the Dear text (leaving a space between the text and the comma):

  4. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays:

    Insert Word Field menu

    Note: If the Mail Merge Tool bar does not display, select Tools>Letters and Mailings>Show Mail Merge Tool bar from the MS Word Menu bar.

    The Insert Word Field: IF box displays:

  5. Select NamSalutation from the Field name drop list.
  6. Select Not equal to from the Comparison drop list.
  7. Leave the Compare to field empty.
  8. Enter true in the Insert this text box and false in the Otherwise insert this text box:

    Insert Word Field

  9. Click OK to close the Insert Word Field box.

    The following code displays in the main report document:

    Salutation text

  10. Select true, making sure not to include the double quotes:

    Code snippet

  11. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar and select NamSalutation from the Insert Merge Field box:

  12. Click Insert and then close the Insert Merge Field box.

    The following code now displays in the main report document:

    Salutation text

  13. Select false, making sure not to include the double quotes.
  14. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays.

    The Insert Word Field: IF box displays.

  15. Select NamFirst from the Field name drop list.
  16. Select Not Equal To from the Comparison drop list.
  17. Leave the Compare to field empty.
  18. Enter true in the Insert this text box and Sir/Madam in the Otherwise insert this text box:

    Insert Word Field

  19. Click OK to close the Insert Word Field box.
  20. Select true, making sure not to include the double quotes:

    Code snippet

  21. Click Insert Merge Fields Insert Merge Fields button from the Mail Merge Tool bar, select NamFirst from the Insert Merge Field box.
  22. Select Insert and Close the Insert Merge Field box.

    The following code now displays in the main report document:

    Code Snippet

  23. Make sure that the font of this code matches the font of the other text in your document.
  24. Save and close the main report document.
  25. Reopen the main report document, enabling macros when asked.

    A new document is generated showing the salutation:

Next we insert name, position, department and organisation details in the main report document.

The logic used here is similar to that used for the salutation. For example:

  • Enter the position name if it exists.
  • If the position name does not exist, leave blank.

Again this is achieved using the If...Then...Else field code.

To insert position, department and organisation details in the main report document:

  1. Place the cursor in an area near the top of the document (e.g. after the date field) where the address details are to display:

    First we'll insert the name.

  2. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays:

    Insert Word Field menu

    Note: If the Mail Merge Tool bar does not display, select Tools>Letters and Mailings>Show Mail Merge Tool bar from the Microsoft Word Menu bar.

    The Insert Word Field: IF box displays.

  3. Select NamFirst from the Field name drop list.
  4. Select Not equal to from the Comparison drop list.
  5. Leave the Compare to field empty.
  6. Enter true in the Insert this text box and false in the Otherwise insert this text box:

    Insert Word Field

  7. Click OK to close the Insert Word Field box.

    The following code displays in the main report document:

    Code snippet

  8. Place the cursor immediately after {MERGEFIELD NamFirst} and enter a space.
  9. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar and select NamLast from the Insert Merge Field box:

  10. Click Insert and then close the Insert Merge Field box.
  11. Place double quotes around {MERGEFIELD NamFirst} {MERGEFIELD NamLast}

    The following code now displays in the main report document:

    Code snippet

  12. Select true, making sure not to include the double quotes:

    Code snippet

  13. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar, select NamFirst from the Insert Merge Field box and close the Insert Merge Field box.

    The cursor should be between the } and " immediately after "NamFirst":

    Code snippet

  14. Hit the SPACE bar to enter a space between } and ".
  15. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar, select NamLast from the Insert Merge Field box and and close the Insert Merge Field box.

    The following code now displays in the main report document:

    Code snippet

  16. In order to avoid a blank line displaying if the value following the name does not exist, a soft return is placed within the code:

    Place the cursor between } and " "false"}:

    Code snippet

  17. Press SHIFT+ENTER to create a new line within the code.

    The following code now displays in the main report document:

    Code snippet

    If both the first name and last name do not exist, nothing should be printed, therefore:

  18. Select false, making sure not to include the double quotes and delete it.

    The following code now displays in the main report document:

    Code snippet

    This code looks to see if both the first and last fields together are empty. If either the first name or last name or both exist they will be printed, otherwise nothing will be printed.

  19. Next we insert the addressee's position.

    To ensure that there is no blank line before the next line of the address, place the cursor on the same line and directly after the previous code.

  20. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays.

    The Insert Word Field: IF box displays.

  21. Select NamPosition from the Field name drop list.
  22. Select Not equal to from the Comparison drop list.
  23. Leave the Compare to field empty.
  24. Enter true in the Insert this text box and false in the Otherwise insert this text box:

    Insert Word Field

  25. Click OK to close the Insert Word Field box.
  26. Select true, making sure not to include the double quotes:

    Code snippet

  27. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar, select NamPosition from the Insert Merge Field box and close the Insert Merge Field box.
  28. The cursor should be between the } and " "false"}. Press SHIFT+ENTER to create a soft return.
  29. Select false, making sure not to include the double quotes and delete it.

    The following code now displays in the main report document:

    Code snippet

  30. Repeat Steps 19 onwards to insert the department (NamDepartment) and organisation (NamOrganisation) fields.

    The following code now displays in the main report document:

    Code snippet

  31. Make sure that the font of this code matches the font of the other text in your document.
  32. Save and close the main report document.
  33. Reopen the main report document, enabling macros when asked.

    A new document is generated showing addressee details:

Finally we insert address details in the main report document.

The logic used for the address fields is:

  • Enter the postal street name if it exists.
  • If the postal street name does not exist, enter the physical street name.
  • If the physical street name name does not exist, leave blank.

Again this is achieved using the Word If...Then...Else field code.

To insert an address field in the main report document:

  1. Place the cursor just after the last bracket:

    Code snippet

  2. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays:

    Insert Word Field menu

    Note: If the Mail Merge Tool bar does not display, select Tools>Letters and Mailings>Show Mail Merge Tool bar from the Microsoft Word Menu bar.

    The Insert Word Field: IF box displays.

  3. Select AddPostStreet from the Field name drop list.
  4. Select Not equal to from the Comparison drop list.
  5. Leave the Compare to field empty.
  6. Enter true in the Insert this text box and false in the Otherwise insert this text box:

    Insert Word Field

  7. Click OK to close the Insert Word Field box.
  8. Select true, making sure not to include the double quotes:

    Code snippet

  9. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar, select AddPostStreet from the Insert Merge Field box and close the Insert Merge Field box.

    The cursor should be between the } and " "false":

    Code snippet

  10. Press SHIFT+ENTER to create a new line within the code.
  11. Select false, making sure not to include the double quotes:

    Code snippet

  12. Select Insert Word Field from the Mail Merge Tool bar and select If...Then...Else... from the drop list that displays.

    The Insert Word Field: IF box displays.

  13. Select AddPhysStreet from the Field name drop list.
  14. Select Not equal to from the Comparison drop list.
  15. Leave the Compare to field empty.
  16. Enter true in the Insert this text box and false in the Otherwise insert this text box.
  17. Click OK to close the Insert Word Field box.
  18. Select true, making sure not to include the double quotes:

    Code snippet

  19. Click Insert Merge Fields Insert Merge Fields button in the Mail Merge Tool bar, select AddPhysStreet from the Insert Merge Field box and close the Insert Merge Field box.

    The cursor should be between the } and " "false"}.

  20. Press SHIFT+ENTER to create a soft return.
  21. Select false, making sure not to include the double quotes and delete it.

    The following code now displays in the main report document:

    Code snippet

  22. Repeat Steps 1 onwards to add the:
    • Postal/physical state (AddPostCity/AddPhysCity)

      Instead of using a soft return (SHIFT+ENTER) leave three blank spaces after the AddPostCity and Add PhysCity field codes so that the postcode will display three spaces after the city name.

    • Postal/physical postcodes (AddPostPost/AddPhysPost)

    The following code now displays in the main report document:

    Code snippet

  23. Make sure that the font of this code matches the font of the other text in your document.
  24. Save and close the main report document.
  25. Open the main report document.

    A new mail merge document is generated showing the address details:

    Note: If, when you run the report, you find that the city and postcode are repeated (e.g. AdelaideAdelaide), check that at Step 12 you added another If...Then...Else statement and did not simply delete "false".

  26. At Step 1. Write the VB code we modified the VB code for testing purposes by commenting out MainDocument.Close SaveChanges:=False. Before we finalise the report we need to edit the VB code to remove the apostrophe.