Workbooks

The TIC Systems Inspection Management System offers a seamless integration solution for incorporating Excel Workbooks into your data management processes using this Workbook Integration Guide. By seamlessly populating your data from an existing workbook that your team may already be utilising, the system enables efficient extraction of data input. This feature proves particularly advantageous in scenarios where internet connectivity is limited or non-existent, presenting diverse use cases across various operational environments.

The integration is bi-directional – there is a pre-population of data when downloading a workbook from the software to provide to the inspector. Then there is a set of required fields needed by the system in order to re-upload the completed file back into the software.

Pre-population

Workbooks are pre-populated when downloading a workbook ready to provide to an inspector.
Named cells in the Workbook Template are populated as follows:

Fill in each of the following cells with data from the Job Order.

Field Name	Value
FOF_File_No	Reference number
FOF_Office	Office name
FOF_Type_Of_Operation	Job order type name
FOF_Voyage_No	Voyage number
FOF_Port	Port
FOF_Terminal_1	Terminal
FOF_Coordinator_Name	Coordinator display name
FOF_Date	Date Format: m-d-Y

If there are inspection subjects (vessels, barges, tanks etc), populate the following named fields as described:

Field Name	Value
FOF_Object_Type	Inspection subject type code
FOF_Object_Name	Display name of the inspection subject
FOF_Object_Name_Orig	Display name of the inspection subject

If the inspection subjects contain a barge or vessel loop through them all, populating the below named cells with the display name(s):

Field Name	Value	Notes
FOF_Barge_(suffixed with an index starting at 1)	Display Name	Field names increment to represent number of cargoes. Begin counting from 1. Example field names: FOF_Barge_1 FOF_Barge_2
FOF_Barge_(infixed with an index starting at 1)_Orig	Display Name	Field names increment to represent number of cargoes. Begin counting from 1. Example field names: FOF_Barge_1_Orig FOF_Barge_2_Orig

Field Name

Value

Notes

FOF_Barge_(suffixed with an index starting at 1)

Display Name

Field names increment to represent number of cargoes. Begin counting from 1.

Example field names:
FOF_Barge_1
FOF_Barge_2

FOF_Barge_(infixed with an index starting at 1)_Orig

Display Name

Field names increment to represent number of cargoes. Begin counting from 1.

Example field names:
FOF_Barge_1_Orig
FOF_Barge_2_Orig

Otherwise, if the inspection subject is a shore tank populate the following named fields with the display name:

Field Name	Value
FOF_Object_Name	Display Name
FOF_Object_Name_Orig	Display Name

Populate the destination (or load) port from the set of ports. If there are destination or load ports, set the following named cell:

Field Name	Value
FOF_Destination_Port	Display name

Populate the products table. For each cargo, set a named field suffixed with the index of the cargo started from 0 with the cargo name:

Field Name	Value	Notes
FOF_Product_(suffixed with an index starting at 1)	Cargo name	Field names increment to represent index number of cargo. Begin counting from 1. Example field names: FOF_Product_1 FOF_Product_2
FOF_Product_(infixed with an index starting at 1)_Orig	Cargo name	Field names increment to represent index number of cargo. Begin counting from 1. Example field names: FOF_Product_1_Orig FOF_Product_2_Orig
FOF_VCF_Table_(suffixed with an index starting at 1)	Contains the VCF table name	Example field name: FOF_VCF_TABLE_1

Field Name

Value

Notes

FOF_Product_(suffixed with an index starting at 1)

Cargo name

Field names increment to represent index number of cargo. Begin counting from 1.

Example field names:
FOF_Product_1
FOF_Product_2

FOF_Product_(infixed with an index starting at 1)_Orig

Cargo name

Field names increment to represent index number of cargo. Begin counting from 1.

Example field names: FOF_Product_1_Orig
FOF_Product_2_Orig

FOF_VCF_Table_(suffixed with an index starting at 1)

Contains the VCF table name

Example field name: FOF_VCF_TABLE_1

Populate the customers table. The contact for each customer is the primary contact – if one exists – or the first contact listed. For every client, list the contact in the following named cells:

Field Name	Value	Notes
FOF_Client_Company_(suffixed with an index starting at 1)	Legal entity name	Example field name: FOF_Client_Company_1
FOF_Client_Contact_(suffixed with an index starting at 1)	Primary contact display name	Example field name: FOF_Client_Contact_1
FOF_Client_Ref_(suffixed with an index starting at 1)	Client reference number	Example field name: FOF_Client_Ref_1

Populate the hidden list of offices. For each office, populate the following named cells:

Field Name	Value	Notes
FOF_OfficeName_(suffixed with an index starting at 1)	Office name	Example field names: FOF_OfficeName_1 FOF_OfficeName_2
FOF_OfficeAddress_(suffixed with an index starting at 1)	Office contact	Example field names: FOF_OfficeAddress_1 FOF_OfficeAddress_2

For the remaining offices fields, set the named cells to null

To ensure pre-populated data is carried through the workbook, ensure “ForceFullCalculation” is set to true for the workbook in the VisualBasic editor window.

Steps to Access the Screen:

1. Open the Visual Basic for Applications (VBA) Editor

Press ALT + F11 in Excel.

2. Open the Project Explorer

If it’s not already visible:

Press CTRL + R
or go to View > Project Explorer.

3. Open the Properties Window

If it’s not already visible:

Press F4
or go to View > Properties Window.

4. Select `ThisWorkbook`

In the Project Explorer (usually on the left), find the VBAProject that corresponds to your workbook.
Expand it, and double-click on ThisWorkbook.

When selected, its properties will show in the Properties Window, just like in your image.

Mapping your Data

In order to ensure that our application can read the data, this is required to be in a format that is readable by our application.

We obtain the spreadsheet data from a Data tab. Note there can only be a single Data tab in your spreadsheet. This data must be structured in a specific format as outlined below.

In each tab you want to capture in your workbook, you will want to map

A VBA script will be required to parse through each sheet and extract the data into the data sheet.

 Sub ListSheets()

    Dim ws As Worksheet
    Dim x As Integer
    
    x = 3
    
    ' Sheets("Data").Range("A:A").Clear
    
    For Each ws In Worksheets
        If ws.Name = "Data" Then
        Else
             Sheets("Data").Cells(x, 1) = ws.Name
             Sheets("Data").Cells(x, 2) = "Section"
             Sheets("Data").Cells(x, 3) = "Variable"
             Sheets("Data").Cells(x, 4) = "Type"
             Sheets("Data").Cells(x, 5) = 0
             Sheets("Data").Cells(x, 6) = "Input|Calculated"
             Sheets("Data").Cells(x, 7) = "Value"
             Sheets("Data").Cells(x, 8) = "1" ' instance number
             Sheets("Data").Cells(x, 9) = ws.Name
             x = x + 1
        End If
    Next ws

 End Sub

 Public Sub Button1_Click()

    Dim copyRange As Range
    Dim pasteRange As Range

    Dim copyRange2 As Range
    Dim pasteRange2 As Range

    Set copyRange = Worksheets("CP").Range("C20")
    Set pasteRange = Worksheets("Data").Range("G1")

    Set copyRange2 = Worksheets("CP").Range("E18")
    Set pasteRange2 = Worksheets("Data").Range("G2")
    ' MsgBox copyRange
    pasteRange.Value = copyRange.Value
    pasteRange2.Value = copyRange2.Value
    
    ListSheets
    
    ActiveWorkbook.Save
 End Sub

Uploading

In order to be able to upload a completed workbook back into the software, the following set of minimum requirements must be met:

Field Workbook Templates can only be uploaded with the following mime-types:
1. application/vnd.ms-excel.sheet.macroEnabled.12
2. application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Field Workbook Templates can only be uploaded with the following file suffixes:
1. .xlsm
2. .xlsx
Maximum file size allowed for upload is 30MB
To ensure that the Field Workbook Template is parseable, the minimum requirements are:
1. A sheet is present in uploaded completed Workbooks named “Data” (the Data Sheet). This recommended to be a hidden sheet.
2. The first row (Row 1) of the data sheet contains the following:
  - Column A contains the text “Fill out First”
  - Column B contains the text “Job”
  - Column C contains the text “FileNo”
  - Column D contains the Data Type date | number | numeric | varchar
  - Column E contains the Data Field Size
  - Column F contains the Data Field Type Calc | Input
  - Column G contains a valid (existent) Job Order Number generated by TIC Systems Inspection Management System
  - Column I contains the instance number e.g. 1, 2, 3 this is used if you have multiple versions of the sheet
  - Column J contains the sheet where the data is extracted from

Additional integration is possible to allow the system to parse quantity data. These details can be provided upon request.

Talk to sales