Who is this article for?

Users looking to pull information from system fields into documents or emails.

No special access or permissions are required.

To dynamically update documents, emails, and workflows when an item is opened or crated, you can use system-specific fields, also known as environment variables. These fields may include revision information, publication dates, expiration details, and many other types of data.

This article walks you through how you can use these in your documentation.

1. Understanding variables

For documents, environment variables update through the document automation process. Updates are triggered upon publication, check-in, release for approval/review, or manually through the Run Document Automation command. Automation may also be configured as a Scheduled Task.

For Office files, these fields may also be referred to as embedded variables or field codes, and can reside in the header/footer or document body of the file itself.

For HTML files, environment variables can only reside in the header/footer defined in the folder properties, not the body. The same fields are available for all file types, however.

2. Setting up files

Before you can use variables in Excel files, you need to make your file macro-enabled.

To macro-enable your file:

1. Check the document out for editing.
2. Open the file.
3. Switch to the View tab.
4. Click Macros.
5. Select View Macros.

6. Enter AutoOpen as a Macro name.
7. Change the Macros in dropdown to the name of the file you're adding the macro to.

8. Click Create.
9. Copy the following and paste it into the Visual Basic page.
   If there's already content in the page, overwrite it.

Public Function DocProperty(property As String) As String
  Dim WB As Workbook
  On Error Resume Next
  If TypeOf Application.Caller Is Range Then
      Set WB = Application.Caller.Parent.Parent
  Else
      Set WB = ActiveWorkbook
  End If
  DocProperty = WB.CustomDocumentProperties(property)
  WB.Saved = True
End Function
Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
  Dim ws As Worksheet
  Dim cell As Range

  For Each ws In ThisWorkbook.Worksheets
     HeaderFooter ws
      For Each cell In ws.UsedRange.Cells
          If InStr(cell.Formula, "DocProperty")  0 Then
              cell.Formula = cell.Formula
          End If
      Next cell
  Next ws
End Sub

Private Sub HeaderFooter(wks As Worksheet)
Dim sValue As String
Dim sFooter As String

wks.PageSetup.LeftHeader = "&L&B&I" & "Qualtrax"

wks.PageSetup.RightHeader = "&R" & DocProperty("##ID##") & Space(2) & DocProperty("##TITLE##") & Chr(13) _

& "&R" & "Original SOP Number - " & DocProperty("##ORIGINAL_SOP_NUMBER##")

wks.PageSetup.LeftFooter = "&L" & "Status: " & DocProperty("##STATUS##") & Chr(13) _

& "Issue Date: " & Format(DocProperty("##DATE_PUBLISHED##"), "MM/dd/yyyy") & Space(2) & "Revision: " & DocProperty("##REVISION##")

End Sub

10. Save the Visual Basic page.

The macro is now saved to the document and will be present for any user who views or edits it in the future, as long as their macro security permits.

Setting up macro security

Environment variables in your Excel files should be configured to update automatically each time the document is opened. To do this, you'll need to install the AutoOpen macro.

Because macros from untrusted sources can be used to deliver malicious code, Microsoft provides security settings that prevent macros from running automatically.

To set up macro security:

1. Open Excel.
2. Click File.
3. Select Options.
4. Navigate to the Trust Center.

5. Choose Macro Settings from the list on the left.
6. Select one of the settings depending on the security you want to maintain.

7. Click OK.

If you chose Disable all macros without notifications, you will need to add your working folder as a trusted location.

To add a trusted location:

1. In the Trust Centre, go to Trusted Locations.
2. Tick Allow Trusted locations on my network.
3. Click Add new location...

4. Select your working folder location.
5. Click OK.

3. Using variables

3.1. Excel files

Environment variables from the variables list are available to add to your Excel documents once you set up your file to be macro-enabled (i.e. .xls or .xlsm).

You can add them to the Header/Footer or the body of your spreadsheet without any additional set up.

Our PDF viewer does not support the execution of macros during the conversion of documents to PDF format. Consequently, any environment variables applied to Excel files will only be accurate when viewed in their original format.

3.2. Word files

Environment variables from the variables list are available to add to your Word documents as soon as they're created or checked into the system. Once the document has hit the system, all available variables can be added directly from Word.

To add a variable to Word files:

1. Check the document out for editing.
2. Open the file.
3. Switch to the Insert tab.
4. Click Quick Parts.
5. Select Field....

6. Select DocProperty from the Field names.
7. Choose the environment variable from the Property list.

8. Click OK.

You will see the field is now included in your document. Repeat the steps for each field you want to add.

Need to modify the date format?
You can customise the date formatting by manipulating the advanced properties of the field.

3.3. HTML files

Environment variables only work for HTML files when added to the Header/Footer tab of the Folder Properties the document resides in.

To add a variable to HTML files:

1. Determine which fields you want to add from the list of variables.
2. Add them to the Header/Footer tab as they appear in the list.
3. Save your file.

4. List of variables

4.1. Document variables

Environment variables for document-related data can be used in any document.

You can toggle between the normal reading view and one showing the locations of inserted variables by pressing ALT + F9 on your keyboard. This can clarify where variables are, if they're present at all, when it's not readily clear.

All fields must be added with the "##" symbols, in all uppercase letters, and with any "_" characters. When updated, system data overwrites these variables with system information.

Approve

Publish

Review

Edit

Retire

Properties

Dependencies

4.2. Workflow variables

Environment variables for workflow-related data can only be added to email actions and reminders.

They cannot be inserted directly into workflow instances for users to view when processing since most of this information is already displayed in the processing details at the top of the instance or in the body itself.

All fields must be added with the "##" symbols, in all uppercase letters, and with any "_" characters. When updated, system data overwrites these variables with system information.