Writing to an Office file during a PDM Transition

Getting a value written into an Office document when passing through a transition is easier than you think.

Addressing the challenge of PDM and Office integration, particularly regarding the updating of document fields from data cards, can be a significant concern, especially in contexts where regulatory compliance and business processes are involved. The necessity of having the file checked out and opened in an Office application for updates to occur can be limiting, and relying on manual intervention, such as having an admin-level person check out the file, introduces risks of errors and inefficiencies.

Fortunately there are tools that can be employed that overcome this problem readily, but there is some planning needed. There are two primary elements to the solution. The first is the use of a Dispatch routine in PDM. The key is to create a Dispatch routine that is automatically executed when files are pushed to a particular state in a PDM workflow.

This is a sample Dispatch that checks out a file, launches the file, then checks it back in.

To ensure proper execution of the Dispatch during a state transition in PDM, there are specific settings and sequences to follow:

1. Activating Dispatch During State Transition:

   • When setting up the Dispatch action within the state transition, make sure to check the specific checkbox (highlighted in yellow). This checkbox is critical to trigger the Dispatch during a transition process made in the workflow.

2. Sequence of Execution:

   • It's important to note that the Dispatch is executed after the state change has occurred. This sequence ensures that any variables set in the transition (under the Actions tab) are correctly updated before the file is further processed by the Dispatch routine. This step is crucial for maintaining the integrity and relevance of the data being processed.

3. File Checkout:

   • The Dispatch routine should first check out the file. This action allows for the modifications and updates to be made on the file, which are necessary for the next steps.

4. Opening the File via Shell Command:

   • To open the file, use the Shell Command within the Dispatch. It is important to select the specific option for the Shell Command (as highlighted in turquoise below) to ensure the process runs correctly. This open verb configures the Shell Command to interact with the file in the desired manner, in this case, opening it into the application.

5. File Check In:

   • The Dispatch routine at the end of the Shell script execution checks the file back into PDM.

By following these steps, you can effectively integrate the Dispatch routine into the state transition process in PDM, ensuring that the file is checked out, updated, and processed correctly as part of the workflow.

Halting the execution of the Dispatch during the running of a Shell command

Now the second element of the process comes into play. VBA (Visual Basic for Applications) is a real workhorse in the MS Office environment. We can employ simple routines in VBA to force the update of the checked out document. When any MS office document opens there is a built-in command that forces execution when the file opens. The VBA command is Document_Open( ) for Word and Workbook_Open( ) for Excel, Powerpoint does not have this capability out of the box, but with an Add-in it can be achieved. Below is a sample routine that is well commented so each step can be understood. You can search the internet for specifics on where this code goes in Word or Excel or reach out to iNTEGRAL for further insights.

Private Sub Document_Open()

    If ActiveDocument.ReadOnly Then '----- 'CHECK FOR READ-ONLY DOCUMENT (NOT CHECKED OUT)
        Exit Sub   '----- 'DROP OUT OF ROUTINE IF THE DOC IS READ-ONLY SO USER CAN LOOK AT DOC
    End If
    '--------------CHECK A CUSTOM' PROPERTY VALUE IN DOC FOR SOMETHING OTHER THAN DEFAULT VALUE OF "-"
    If ActiveDocument.CustomDocumentProperties("DATE RELEASED").Value <> "-" Then
       UpdateDoc '-----------IF THERE IS A RELEASE DATE VALUE, RUN THE ROUTINE CALLED UpdateDoc
        '--------------------SAVING THE DOCUMENT AFTER UPDATES      
        ActiveDocument.Save
        '--------------------CLOSE THE DOCUMENT AND EXIT WORD
        Application.Quit
        '--------------------SAVING THE DOCUMENT AFTER UPDATES
    Else
        '----------------------IF THE RELEASE DATE CONDITION IS NOT MET, EXIT THE ROUTINE WITHOUT SAVING OR QUITTING
        '----------------------THIS IS RUN WHEN THE FILE IS BEING WORKED ON BEFORE IT IS RELEASED
        UpdateDoc
        Exit Sub
    End If
    
End Sub

Here is the VBA that will update the Word doc to reflect the values found in the data card.

Sub UpdateDoc()

    Dim rngStory As Word.Range
    Dim oShp As Shape

    ' ITERATING THROUGH EACH STORY RANGE IN THE DOCUMENT
    For Each rngStory In ActiveDocument.StoryRanges

        ' CONTINUOUSLY LOOP THROUGH ALL LINKED STORIES
        Do While Not rngStory Is Nothing
            ' UPDATE FIELDS IN THE STORY RANGE
            rngStory.Fields.Update

            ' GETTING THE NEXT LINKED STORY (IF ANY)
            Set rngStory = rngStory.NextStoryRange
        Loop
    Next rngStory
    
End Sub

When the UpdateDoc routine finishes and the Word doc is closed by VBA, the PDM Dispatch takes over again and checks in the file. The process is now complete with the signatures and dates now showing in the Word file.