You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Purpose

This page purpose's is to introduce an Excel macro called "Quik2Code" with instructions to install and to provide use cases with examples.

Introduction

A research concept is a collection of data elements with controlled vocabularies applied. At CDISC, we use the CDISC Controlled Terminology for identifying controlled vocabularies. To perform this task, a metadata developer often needed to cross-reference multiple spreadsheets; and, manually transcribe codelist contents by either re-typing or copy-and-pasting data. This mode of operation is not only tedious, but error-prone to typographical errors.

Quik2Code is designed to significantly reduce the manual intervention needed to create a research concept. It is an Excel macro with a user interface using data from the CDISC Controlled Terminology spreadsheet published quarterly. Users will check off codelist values (controlled vocabularies) from a codelist. With click of a button, the macro will populate the selection in a predetermined format. Users will also be able to reverse lookup an existing list of controlled vocabularies. The tool will select codelist values found, as well as identify non-matching values that are either new vocabularies or typographical errors.

Installation

The following describes the steps to install Quik2Code on a Microsoft Windows PC:

StepsVisual Aid

A. Create folder and place Quik2Code Excel macro file

  1. Using File Explorer, on the C: drive, create a CDISC folder, i.e., C:\CDISC
  2. Place the Excel macro file <<ct_picker.xlsm>> in it
 

B. Add CDISC folder as a trusted folder for launching Excel macro

  1. Open Excel
  2. Select File | Options. The Excel Options dialog box appears
  3. Select Trust Center on the left pane
  4. Click Trust Center Settings... The Trust Center dialog box appears
  5. Select Trusted Locations on the left pane
  6. Click Add new location... The Microsoft Office Trusted Location dialog box appears
  7. Type or browser to C:\CDISC as Path
  8. Click OK:
    • The Microsoft Office Trusted Location dialog box closes
    • C:\CDISC appears on the list of paths under Trusted Locations
  9. Click OK to close the Trust Center dialog box
 

C. Create a customize Excel ribbon for Quik2Code (assuming Excel 2010 and 2013 versions)

  1. Download this <<Excel UI customization file>> and place it into C:\CDISC folder
  2. Repeat Steps B1, B2 to bring back the Excel Options dialog box, if it is not already showing
  3.  Select Customize Ribbon
  4. Click Import/Export at the lower right corner of the Excel Options dialog box
  5. Select the Import customization file option. The File Open dialog box appears
  6. Browse to the Excel UI customization file saved in Step C1
  7. Click Yes when prompted to replace ribbon
    1. An entry labeled CDISC (Custom) appears on the right side of the Excel Options dialog box
    2. Expanding the node will reveal Function (Custom)
    3. Expanding again will reveal Quik2Code
  8. Click OK to close the Excel Options dialog box
 

D.Verify Quik2Code Install

  1. Select the ribbon labelled "CDISC" from the top menu
  2. Click the Quik2Code icon
    • The CDISC Controlled Codelist spreadsheet appears and may obstruct Quik2Code. If so, minimize the CDISC Controlled Codelist spreadsheet
  3. The Quik2Code's user interface appears
 
  • No labels