Excel

New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Alternativlly, existing table definitions can be amended by selecting an existing table from the My Data using he mouse pointer, or selecting the properties icon ( ) against the table. Once the table properties screen is displayed, new tables can be defined or selected parts of existing tables can be amended.

Tables can be defined to allow data to be entered directly within the Metamation database, or can be set to import (pull) information in from a number of different data sources including ODBC connection, Excel spread sheets or Microsoft Project Plans.

When an Excel connection is made for a table, the columns returned from the specified Excel spread sheet can be linked to the defined table columns. The excel spread sheet can be held either within the Metamation document repository (as a checked in document), or held somewhere on the network.

As records are returned (pulled) from the Excel spreadsheet, records are created or updated in the linked tables, with the map between the imported fields and the defined named areas of the spreadsheet. The definition of the Excel connection details exactly how the import will work.

To define an Excel connection for a table, once the table properties are on screen, use the selection list on the External Data Connection to set the connection type to Excel connection, and then use the Define Connection button to define the links to the named area in an Excel spread sheet.

Table External Link Definition - Excel - Connections

An Excel connection for a table is set through the table properties window. Using the selection list on the External Data Connection to set the connection type to Excel connection, and then use the Excel Admin button, the Excel connection properties window is then displayed.

On the Excel connections screen, connections are set in three parts:

Specify the data source
You have the option to use two different sources for the data source. Once is the imported documents - select the imported excel spread sheet from the list of imported documents. Alternativly, you can specify an external spreadsheet somewhere on the network. Once this selection has been made, either type in the name directly, or use the browse button to find the file.

Note: It is highly recommended that any connected excel sheet is located on a network path which is ALWAYS available (i.e, not a client PC). Also, the path should ALWAYS be a now mapped path (i.e, it should be a URL in the format \\myserver\dir\mysheet.xls.

Range Name
The range name is used to indicate the range of cells in the spreadsheet to use for the import. Each row within the specified range will be imported as a new record into the table, with each named column being available for linking into a defined table column. Select the ranges from the drop down list, and use the refresh icon ( ) to connect the spreadsheet and renew the named ranges.

Refresh Type
The refresh type drop down indicates what should happen with the imported data. The options available are:

Refresh Entire Table - Before the Excel connection is made, the current contents of the table will be cleared and the Excel import will re-create the contents.

Add new Items only - The Excel import will look for new records and where the records do not exist, they will be created. Existing records will not be amended, and records missing from the Excel source will not be deleted within Metamation

Add new Items and Amend Changed - The Excel import will look for new records and where the records do not exist, they will be created. Existing records will also be updated to reflect the Excel data source. However, any records missing from the Excel source will not be deleted within Metamation

Add new Items, amend changed and mark deleted - The Excel import will look for new records and where the records do not exist, they will be created. Existing records will also be updated to reflect the Excel data source. Where records are missing from the Excel source, these will be marked as deleted (making them unusable, but not actually deleting them) within Metamation

Refresh Time
The refresh time is how long in minutes, between every data refresh. This entry will only accept a numeric. 60 will be a refresh once an hour, 1440 will be a refresh once a day, 10080 will be once a week, and 40320 will be once a month. Any numeric is valid.

Below the selected refresh time, the last date and time of refresh will be displayed.