Configuring Interactivity with Microsoft Excel

The following configuration is optional, enabling you to access a Save button directly from within a Microsoft Excel spreadsheet (utilized in the 'Edit with Excel' feature).

Prerequisites

To use the Edit with Excel feature, you must have the following set up:

  • Microsoft Excel must be installed on your system (laptop/desktop).
  • A Web services license must be assigned to your user id by the PointForce iTopia system administrator.
  • The TECSYS Excel Add-In must be installed.

One-Time Setup Procedure

If you PC was equipped with an older version of the TECSYS Excel Add-In (file extension .xla), it must be removed before installing the new version. The following steps outline how to remove the older add-in:

  1. Launch Windows Explorer.

  2. Go to the Add-In folder and delete the XLA file (i.e. C:\Users\{username}\Documents\Tecsys\iTopia\Add-In\tecsys_iTopia.xla).

  3. Launch Microsoft Excel.

  4. A prompt will advise you that the add-in no longer exists. Click OK.

  5. Click File.

  6. Click Options.

  7. Click Add-Ins.

  8. From the Manage drop-down menu, select the Excel Add-ins option, and then click Go....

  9. Select the Tecsys_Itopia plugin.

  10. A prompt will advise you that it cannot find the add-in and to delete it from the list. Click Yes.

  11. Click OK.

  12. Close Excel.

  13. Install the latest version of the add-in using the instructions in the following procedure.

The following steps outline how to install the TECSYS Excel Add-in:

  1. Launch the Export to Excel (meta_export_excel) resource from PointForce iTopia.

  2. Click the Download hyperlink to download the indicated Tecsys.zip file onto your local system.

  3. Open the ZIP file and extract its contents into your Windows Documents folder. This will create a folder called Tecsys/iTopia (i.e. C:\Users\{username}\Documents\Tecsys\iTopia), as well as three sub folders titled Add-in, Output, and Template.

  4. Launch Microsoft Excel.

  5. Click File.

  6. Click Options.

  7. Click Add-Ins....

  8. From the Manage drop-down menu, select the Excel Add-ins option and then click Go...

  9. Click the Browse... button and go to the C:\Users\{username}\Documents\Tecsys\iTopia\Add-In folder.

  10. Select the Tecsys_iTopia.xlam add-in file and click OK. This will add the Tecsys_Itopia option to the list of add-ins.

  11. Ensure that the Tecsys_iTopia check box is selected, and then click OK.

  12. When you return to the spread sheet, you should see the TECSYS iTopia folder on the menu.

  13. If you do NOT see the TECSYS iTopia folder on the menu, go to the C:\Users\{username}\Documents\Tecsys\iTopia\Add-In folder and,

    • Right click on the Tecsys_iTopia.xlam file name.
    • Select Properties.
    • Click the Unblock button.
    • Click the Apply button to close the Properties window.

  14. When you return to the spread sheet, you should now see the TECSYS iTopia folder on the menu.

Using the Edit with Excel Feature

Here's an example from the Customers resource using Edit with Excel. In this example, we have selected 1 record to edit. Here is what the spreadsheet looks like in Excel:

  • To modify a record, change the value of any modifiable column.

  • If you wish to create an additional record, add a new row with a unique business key and complete the required columns.

  • When all of the data has been modified or created, click the Save action from the TECSYS iTopia tab in the Excel ribbon to write the data to the database.

  • Once the Save action is performed, the status of the record(s) is displayed in the Status column. Any record that has been modified successfully will have a status of Updated. Every record that has been created will have a status of Created. Any record with an Updated or Created status has been written to the database. Every record that was unsuccessfully created or updated will have a status of Invalid. This record will also have cell notes in each column in which the error occurred. Correct the errors and save the record again.

Note: If you are working with a view that includes links to other views on the details page, you will experience issues when you export the table to excel and attempt to save your changes. This section explains the issue and provides a solution.

Here is an example of the Products (ibis_ici1.im13) view's detail page:

  • You can export the data to excel without any issues. When you save your change(s), the following message displays: "The Web service cannot be executed. OK".

  • In order to save the changes, you must personalize the view (i.e. you must start over, so all of the changes you just made in excel will be lost).

  • Return to the view in iTopia and click on Personalize found in the bottom right-hand corner of the search criteria/results page or on the details page.

  • On the Details folder, remove the section (and all of the contents) under 'Link To ...' in column on the right (Show These Fields in This Order).

  • We recommend that you use the Save As option in Personalization in order to save a new view rather than saving the master view. In this way, you have a view that is always available for editing with Excel.

  • With the Link To section removed from the view, you can successfully edit records in Excel and save the changes in iTopia.

 
Back