Excel Macro Template for Issues and Risks

Synchronise and publish project issues or risks from Excel directly to 3D Repo and vice versa.

F
Written by Faisal Ramady
Updated over a week ago

Unlike Excel for Issues and Risks Project Reporting, the Macro Template allows you to connect to 3D Repo and track your project’s issues and risks in real time in a bidirectional way. This means that you can publish any changes in Excel back to/from 3D Repo.

We provide this template to our users as an example of 3D Repo API interaction and to assist you in understanding how our APIs work. Note that this is only intended for use when building custom workflows for a wide range of use cases or as a starting point for custom development.

Here I'll cover the following:

Get Started

Before we begin, head over to our downloads page and download the Macro Template. Extract the .xlsx file from a ZIP folder and open it. You will see a sample table, which we'll use to list your project issues and risks.

Initial Setup

You will find 4 tabs: User, Risks, Issues & Lists. Each tab has information to display or for a user to fill out. You can also change the table layout to suit your needs.

  • User tab requires user information for authentication and linking your project

  • Risks tab displays risks downloaded from 3D Repo or a list of risks you have created to upload to 3D Repo

  • Issues tab displays issues downloaded from 3D Repo or a list of issues you have created to upload to 3D Repo

  • Lists tab contains all the relevant information of custom/default fields to fill out

  1. When you open the Excel file, click on Enable Editing and then on Enable Content

  2. In the User tab, provide the following information where relevant:
    - Teamspace Name of the Teamspace where your issues and risks reside
    - Project name Name of the Project where your issues and risks reside (optional)
    - Model ID Accessible through the model settings menu
    - API Key For authentication


    ​Example Connection setup
    ​

Update the Lists Tab

3. Open the lists tab and update the information to match your project in 3D Repo

4. Any new and custom field must be included in Excel:
- Include any custom Issue types or Risks categories that you have created in your Teamspace settings
- Update the Assigned to column to match the Jobs list you have in User Management in 3D Repo

Note the following:

Predefined Columns
(cannot be edited in Excel)

Customisable Columns
(can be changed in 3D Repo)

X Priority

✓ Type

X Status

✓ Assigned to

X Risk Rating

✓ Risk Types

X Risk Lookup

X Risk Status

Push and Pull Issues & Risks from 3D Repo

This is where the magic happens! When the Users & Lists tab are both set up you can start downloading & uploading issues and risks.

Pull Issues from 3D Repo

To download the issues list from 3D Repo you will need to do the following:

  1. Open the Issues tab

  2. Click on the button 'Pull Issues from 3D Repo'

  3. Existing issues will populate your spreadsheet. Click OK when you see the message Complete pop up

  4. You can amend the following cells for any issue field with a dropdown: Priority, Status, Assign, Type, Due Date or Description

  5. To submit the changes click on the button 'Push Issues to 3D Repo'

  6. Click OK when you see the message Complete pop up

    Example

Pull Risks from 3D Repo

To download the risks list from 3D Repo, follow the same 6 steps illustrated above.

This time, you can amend the following cells for any column with a dropdown option: Risk Likelihood, Risk Consequence, Level of Risk, Risk Owner, Category, Treatment Status, Treated risk Likelihood, Treated Risk Consequence or Treated Level of Risk.
​

Upload a new Issue & Risk to 3D Repo

When you want to create a new issue in Excel and upload it to 3D Repo, follow these steps:

  1. Provide a title to the new Issue or Risk

  2. Provide further information such as:
    - For issues: Priority, Status, Assign, Type, Due Date & Description
    - For risks: Risk Likelihood, Risk Consequence, Level of Risk, Risk Owner, Category, Treatment Status, Treated risk Likelihood, Treated Risk Consequence or Treated Level of Risk

  3. Optional if you wish, you can:
    - Add a due date if you wish
    - Add a description if you wish
    - Add an X, Y and Z information for the pin location

  4. For images and screenshots (also optional)*
    - Paste the screenshot filename and file type. I.e. Beam Connection.png
    - Paste the resource file name (comma separated for multiple files)
    ​ I.e. beam.png,wall.jpg,windows.png

  5. To create this new issue in 3D Repo, click on the button 'Push Issues to 3D Repo' and hit OK when the message Complete appears as a pop up.
    For risks, do the same and click on 'Push Risks to 3D Repo'.
    Once the issues/risks are pushed, 3D Repo will generate a unique ID to those issues/risks

*Note
- You can attach a screenshot or resource files in Excel to new issues and risks only
- You cannot update the screenshots or attach resource files to existing issues
- The only file types supported in Excel are .PNG and .JPG

Voilà! Now you have established a live connection with 3D Repo. Save the excel file and use it again in the future to retrieve the latest data from 3D Repo or to add new issues and risks.

Did this answer your question?