How to Link Excel and Word Documents to EPDM Datacards
There are a few methods you can use to link Word and Excel to datacards in EPDM. In this blog post, I’m going to discuss the best methods I have come across that do not require you to save a document with a macro embedded in it.Setting up the EPDM Variables
In order for EPDM datacards to link into file properties, you must add attributes to the variables in the EPDM Administration Tool. Below is an example for the variable Document Number. Word and Excel utilize the Block names Summary and CustomProperty. Use Summary for built in properties such as Author, Title, etc. and use CustomProperty to make up your own.
Linking to the Datacard from Word
To create custom properties in Word, it is slightly different in Office 2007 vs 2010. In 2007, go to Windows button > Prepare > Properties.
In 2010, go to File > Info > Properties > Advanced Properties.
In the Custom tab, add the variables you would like to link to. You must at least enter a space for the value for the Add button to become available. I recommend you put a default value so that you can see it when the field is inserted into the document.
Finally, you can add fields to your document that will link to these variables. Go to Insert > Quick Parts > Field…
To choose one of the Summary or Custom properties, you must choose DocProperty for the Field Name and choose your property from the Property list to the right. Say OK to the dialog and it will insert a Field into your document.
Now, you have a two way connection to the datacard. Unfortunately, when you update your datacard, the changes will not automatically appear on your fields. You must right click on the individual fields and choose Update Field. There is a way to avoid this, but it must be done on all of your clients.
- Go to Developer > Macros or just press Alt + F8.
- For the Macro name field, type AutoOpen and click Create.
- Insert the following macro code between Sub AutoOpen() and End Sub:Dim aStory As Range
Dim aField As Field
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
This macro will essentially run every time you open any document and update any fields inside of it.
Linking to the Datacard from Excel
Linking to the Datacard from Excel is quite a bit easier and does not involve any need for macros. First, Give the cell an explicit name that you want to insert the property into. Below I named it DocNum.
Next, add a property exactly how you would in Word except you’re going to check the box to Link to content and choose the name of the cell.
That’s it! Simple as that.
- If you are going to use these with datacards that have fields with default values, make sure to check Default Overwrites so that the defaults for the properties you entered do not override your datacard.
- It is a good idea to protect the linked parts of your documents from unintentional editing. You can do this by using Protect Document (Word) or Protect Sheet (Excel) in the Review tab. It is possible to block some or all of the sections of the document from editing with this.