Wednesday, April 10, 2013

How to Link Word and Excel 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.
  1. Go to Developer > Macros or just press Alt + F8.
  2. For the Macro name field, type AutoOpen and click Create.
  3. 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
          Next aField
        Next aStory

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.

Final Tips

  1. 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.
  2. 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.

For more training and tutorials on the many 3D CAD Modeling solutions in the SolidWorks family of products and add-ons, please feel free to look through our Webcast Archive, register for an upcoming webcast or event, or look into our 3DU SolidWorks Training and Certification courses.


Anonymous said...

How do I link the word document custom properties back to the data card? For example, I would like to be able to edit a field on the word document and it also updates the variable value on the card.

Lindsay Early said...

The method I described above will enable 2 way communication. If you change the datacard it will change the document as well as if you change the value of the field in word it will populate the datacard.

Avi said...

Unless one of the fields is read only in ePDM. For example if one of the fields you are populating in your word document is the revision level of the document and that can only be set by an action in your workflow then ePDM will be the master of that field and you won't be able to drive it from word.

Silvia Navarro said...

how do you link checkboxes from a data card to MS Office docs and back?

Lindsay Early said...

Silvia, I have yet to find a good way to do that. I don't think it is possible without a macro. What I do is just put a dropdown with Yes/No option on the datacard and have the document just display the word Yes/No.