Readme for Micro Focus Connect Excel Connector 24.2

This readme describes how to configure Micro Focus Excel Connector. Refer to the Micro Focus Community and Micro Focus Connect Help Center for further configuration instructions.

The Micro Focus Excel Connector for Micro Focus Connect (Micro Focus Excel Connector) lets you synchronize assets in OpenText Products into tables in worksheets, for example assets found in the Micro Focus Octane Connector.

Copyright

Copyright 2018 - 2024 Open Text.

The only warranties for products and services of Open Text and its affiliates and licensors ("Open Text") are as may be set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. Open Text shall not be liable for technical or editorial errors or omissions contained herein. The information contained herein is subject to change without notice.

Except as specifically indicated otherwise, this document contains confidential information and a valid license is required for possession, use or copying. If this work is provided to the U.S. Government, consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.

Contents
  Installation and Upgrade Information Technical Notes
Prerequisites Known Issues Need assistance with the product?

Features

Supported Versions

This version of the connector is certified with Excel for Microsoft 365 (.XLSM files)

Unless otherwise specified, for any given MF Connect connector from OpenText, all versions of the connector product that are officially released and supported by it's vendor both four months before the time of the release of the connector as well as at the time of using the connector, are supported by OpenText. Not all product versions will be tested and validated by OpenText for each connector release. Instead, OpenText chooses a subset of versions that we deem representative or most important to run validation and certification tests against. These selected versions are mentioned in the respective connector read-me, clearly identifying them as "validated" or "certified". As stated above, this does not mean that Micro Focus Connect does not support other product versions – it only means that no validation test cycle was executed for the other versions.

Supported Assets

This version of the connector maps any asset (Stories, Tasks, etc) to tables in worksheets.

 

Top

Prerequisites

This connector requires at least build 24.2 of Micro Focus Connect and can be configured using the Micro Focus Connect UI.

The thesaurus.properties file should be copied to the Micro Focus Connect's AppData\data folder.

For each project you wish to synchronize via Micro Focus Connect, you will create an Excel file in the "projects" directory noted below, and it should be a copy of the template file provided. For example if you have a project 'MyNextRelease', you can copy the template file to the projects directory and rename it to MyNextRelease.xlsm. Then when you configure the synchronization in Micro Focus Connect you will see this listed as a project from Excel.

The Excel template file provided demonstrates the layout used for synchronization. Each file is treated as a project, with each worksheet being treated as a type, and each column (starting from column C until an empty column is found) is then treated as a field. For example if I copy the template file and name it 'MyNextRelease.xlsm', then looking at the file I have a MyNextRelease project, with a worksheet for stories (called Stories), and columns defining the available fields (like 'Name'). The Modified column on each worksheet is used via Excel macros to determine if the row has been changed, to aid in synchronization, it should be left unchanged.

The following are required to run the Connector. For further configuration instructions, see the Micro Focus Connect Release Notes.

  1. Create a Connector directory. For example: C:\Program Files\Micro Focus\Connect\AppData\connectors\mfcConnectorExcel
    • NOTE: If this is being installed into an existing connector directory, you must remove the existing mfc*.jars
  2. Create a directory for the Excel project files (for demonstration purposes we'll call it "projects").
  3. Create a directory for the provided template file (for demonstration purposes we'll call it "projects\template").
  4. Copy the template file (found in the src\main\resources directory) into the template directory.
  5. Copy the following libraries (and the provided Connector jar) into the connector directory you created.

 

Top

Installation and Upgrade Information

The following information contains notes regarding installation, but does not replace the entire process.

Required Connection Information

In order to use this connector, you will need to collect and enter this information in the Micro Focus Connect user interface:

 

Top

Known Issues

Fallback users are not supported

When selecting a fallback user a message will state there are no users.

 

Top

Technical Notes

Excel Template File

The structure of the Excel template must match the one shipped with the connector. Each sheet in the template file defines a type. The first row, starting at column B, contains the field names you can use. You can include other fields that are available in your schema. To do this, use the field mapping in the GUI and add them to your Excel template file. Use the the actual field name from the GUI in the Excel file.

The template file contains a worksheet named Internal Lists. This worksheet contains fields that are included in the template drop-down options. You can modify the lists but can't change the existing names. In addition Internal Lists will show in the list of types when configuring types in the Micro Focus connect UI. Ignore it as a type. The following fields use the lists (although you can also type):

Note: The Modified Time field is a calculated field. When you have the file opened, be sure to enable macros in Excel and not modify this column.

Important: If you are making changes in project files, when you have completed your changes you need to close the file so that Micro Focus Connect can use it. As part of the sync operations Micro Focus Connect needs to read/modify the template file and any syncing project files during the sync process. Because of this, during the sync process it will temporarily move the file to determine if it is in use. If Micro Focus Connect cannot move it, Micro Focus Connect waits 5 seconds and then continues to try to move it. Once you close the file, Micro Focus Connect will then be able to continue the sync process. Similarly, if you try to open the file during the move, the file may not be available. Wait a few moments and then try again, (or optionally stop the sync process before editing files).

Excel Built in Lists

To make it easier to enter data in Excel from the template, we have configured certain columns to allow the user to pick from a list. The lists are on the worksheet named 'Internal Lists', with each existing list showing it's name above it and the current values. If you want to modify a list, either add or remove an item, you change the range for the Excel named item by highlighting the appropriate range and choosing define name. These named lists are then set (for example Story Status column) by selecting that column, choosing data validation and then choosing the appropriate name and desired conditions. Refer to your Excel documentation for more details.

Directory Structure

The best way to use this connector is to copy the Excel template from the installation directory and paste it into the location where you want to create the Excel projects (the project directory). Then, when you create a new Excel file from the template and save it into the Excel Sheet Path directory, it will display as a project to map to in the Micro Focus Connect user interface.

Excel file layout

Each Worksheet represents an entity type, and the name of sheet is the name of the entity type that will appear in the Micro Focus Connect UI.

Columns after the third column represent any fields. The first row, starting in column B and moving to the right, represent the name of the field that is in an entity. For example in the ExcelTemplate file, on the stories worksheet column C indicates 'StoryID' as the name of the id field, while the column containing 'name' represents the name field of a story.

Working with your own file

If you are starting with your own existing file, you will need to modify it to follow the format provided in template file. On each worksheet that you are using as a type, the first column should be left blank and the second column should be titled Modified (and to have row change detection work, you'd want to re-use the macro that is active in the template file). The third column is considered the ID column for the type (typically type ID as the name). Any columns after that become field names.

Truncation of fields

Excel fields cannot contain more than 32K characters, and if encountered the Excel connector will truncate the value. If you are performing bidirectional sync of this content and a user makes changes to the value in a truncated field in Excel, then the truncated value will get synced back to the other product.

 

Top

Need assistance with the product?

Get support enables you to open a support incident or to submit a bug. You can also browse many helpful support resources.

 

Top