Linking Your SolidWorks PDM Enterprise Data Card Values to Microsoft Word and Excel Documents

One of the most common practices, yet least documented, capabilities of SolidWorks PDM Enterprise is the ability to link a wide variety of documents to variable values defined in your company’s customized data cards. Considering the lack of information on how to thoroughly do so with Microsoft Office documents such as Microsoft Word or Microsoft Excel, I have decided post this step-by-step process of automatically linking your properties inside of Microsoft Word and Microsoft Excel to the data cards that control them.

Microsoft Excel Document:

Here is a simple layout of how the linking process works with an Excel document. The process is almost identical when linking an EPDM variable to a Word document.

To get a good understanding on how this process works, I will be linking a variable to an Excel document from scratch.

Step 1)

The first step is to know or create the variable that is going to be used on the card. I’ll just create one. Open your administration tool. Log in to the appropriate vault. Right click on “Variables” and choose “New Variable…”.

Once you’ve chosen to create a new variable, you will have to;

• give it a name (Awesome Variable),
• an attribute so it can be recognized by other programs (click “New Attribute”),
• the attribute block name (CustomProperty) must be set to “CustomProperty” because that is what is used in Excel and Word,
• the attribute name (AVar Custom Property) will be the name of the custom property that you create/have in the Excel or Word document that you are linking it to,
• and finally the list of file extensions that this attribute will be compatible with. I usually click on the small downward arrow and choose the pre-existing Microsoft Office extension list to ensure premium compatibility.
• Then click OK!

Step 2)

Once your new variable is set up, it must be on the appropriate file card. If you are using office files for multiple reasons (sales information, ECO/ECN/ECR processes, vendor information, etc.) then you need to make sure the variable is inside the appropriate file card that gets assigned to those documents. So let’s go through and add this variable to a file card. I opened up a generic Office file card that’s attached to Excel and Word documents and just swapped out the “Subject” variable for my awesome variable to save a little time. Save the card and it’s good to go!

Step 3)

Create or open the Excel document from the PDM Enterprise vault. Since I’m doing this from scratch I’ll create a new one. Once the sheet is in the vault, open it up. Click on the cell that you would like the property to appear in and rename it. The cell name must be unique to the sheet and cannot contain any spaces. Select the cell, select the name box, type the new cell name and hit enter. What was cell A1 is now “AwesomeCell”.

We will now create the custom property and link it to a cell. In Office 2007 you need to go to your Office drop down menu, choose “Prepare” and “Properties”. Office 2010 will be File > Properties > Advanced Properties.

2007

2010

Once “Properties” is selected a tool ribbon will pop up. Click on the “Document Properties” drop down box and choose “Advanced Properties”.

When the sheet properties window pops up, click on the far right tab called “Custom”. This is where you control all of the sheets custom properties. In the first name box, type the name of your new custom property (This will be the variables attribute name, remember!). Next, click on the “Link to content” checkbox. This checkbox is only active if you have named cells in your sheet. You can access these named cells by clicking on the small dropdown arrow to the left of the checkbox. Choose the cell that we added a name to, then click the “Add” button at the top right. (Mine says “Modify” because I’ve already added it). Click the OK button, save and close your Excel sheet and we’re ready to test it out!

Step 4)

Here are screen shots of my Awesome Excel Sheet file card and cell information before I add any values.

Go to the file card preview in your vault, make sure the file is checked out and add a value to your new variable. Then save it and open your Excel sheet. The value that was added to the card is now in the cell!

This is a two way street so if you update the cell value in the Excel sheet, it will update the value in the file card as well. Change the value in the cell, save and close the sheet and check the file card again. If need be, make sure you set your read only permissions as needed either in the file card or in the cell.

And that’s how you can link your PDM Enterprise file card variables and values to an Excel sheet! Now let’s take a look at linking your variables to a Microsoft Word document.

Microsoft Word Document:

Step 1)

*See Step 1 for a Microsoft Excel Document*

Step 2)

*See Step 2 for a Microsoft Excel Document*

Step 3)

Create or open your Word document in the EPDM vault. Since I’m starting from scratch, I’ll create a new one. Once the document is in the vault, open it up. A word document does require just a few more steps for everything to work seamlessly together considering there are no cells. Instead, Word uses fields to bring in the properties. Also, the fields inside of a word document do not update automatically like they do in Excel but we’ll fix that a little later. For now, open up your Word document and let’s add a field. In Office 2007 you need to go to your Office drop down menu, choose “Prepare” and “Properties”.

Once “Properties” is selected a tool ribbon will pop up. Click on the “Document Properties” drop down box and choose “Advanced Properties”.

When the sheet properties window pops up, click on the far right tab called “Custom”. This is where you control all of the sheets custom properties. In the first name box, type the name of your new custom property (This will be the variables attribute name, remember!). Since the attribute on the variable that I created for the Excel document also had Word document extensions, I will continue to use that same variable and attribute. (Variable: Awesome Variable, Attribute: AVar Custom Property). See Step 1 if you need to create a new attribute for that variable so it can be used in Microsoft Word documents. Once the custom property is created, give it any value for now and click the “Add” button at the top right. (Mine says “Modify” because I’ve already added it). Click the OK button. Next we’ll add a field on the sheet to show the property value.

Make sure that your cursor is in the location where you would like this field to be added. Click on the “Insert” tab in the Word document. Then click on the “Quick Parts” drop down button and select “Field”.

For the field name choose “DocProperty” and for the property choose the one that you just created. Click “OK” in the bottom right corner.

Now you should have the value of your custom property in your Word document! You are able to edit the text inside of that field but it will not push that back to the custom property value or to the file data card in PDM Enterprise. To make sure that the data card, custom property value and text are always matching make sure that you make any changes only on the data card or to the property value in Word.

Step 4)

Save and close your Microsoft Word document. If you look at the file data card for this word document, you will see that the variable in the card has now updated to match the custom property value in the word document.

Let’s modify this inside the file data card and see how it updates inside of the document. Change the value inside of the file data card preview. Then click “Save” in the card preview.

Now open up the Word document. You’ll notice that the field did not actually update. Why the heck not!? Well, unfortunately Microsoft Word doesn’t work as well as Microsoft Excel does but we can fix that. If you check your custom property in the word document, you’ll see that the value actually did update, it just wasn’t pushed to the text in the document.

Click “OK” if the Word Document Properties is open. For the time being, manually update the text by right clicking on the text in the document and choosing “Update Field”.

Now the text will update to what its field value actually is!

Step 5)

Considering that most documents will have more than one field, it’s not very efficient to have to go through and manually update every single field. We can add some simple macro coding inside of Microsoft Word to make sure that all of the fields update whenever you open the document. Click on the “Developer” tab and select “Macros”.

When the macro window opens type “AutoOpen” as the new macro name, then click on the “Create” button. By giving this macro the name “AutoOpen” it will automatically run whenever a file is opened.

When the Visual Basic window opens, there will be a little bit of starter code already in there. Delete all of it and copy and paste this code in it’s place.

Sub AutoOpen()

‘

‘ AutoOpen Macro

‘

‘

Dim aStory As Range

Dim aField As Field

For Each aStory In ActiveDocument.StoryRanges

For Each aField In aStory.Fields

aField.Update

Next aField

Next aStory

End Sub

Click “Save” and close out the Visual Basic window. Now save and close your Word document and we’ll see if the code works. Modify the variable value in your file data card again. Then click the save button at the top right.

Now open up the Word document and your fields in the text should update themselves automatically!

This works if you only have fields in the text body. If you have fields in headers and footers, or if you have fields inside text boxes inside those headers and footers, use the following code.

Sub AutoOpen()

‘

‘ AutoOpen Macro

‘

‘

Dim aStory As Range

Dim aField As Field

Dim aSection As Section

Dim aHeaderFooter As HeaderFooter

Dim shp As Shape

For Each aStory In ActiveDocument.StoryRanges

For Each aField In aStory.Fields

aField.Update

Next aField

Next aStory

For Each aSection In ActiveDocument.Sections

For Each aHeaderFooter In aSection.
Headers

Set aStory = aHeaderFooter.Range

For Each aField In aStory.Fields

aField.Update

Next aField

For Each shp In aStory.ShapeRange

With shp.TextFrame

If .HasText Then

.TextRange.Fields.Update

End If

End With

Next

Next aHeaderFooter

For Each aHeaderFooter In aSection.Footers

Set aStory = aHeaderFooter.Range

For Each aField In aStory.Fields

aField.Update

Next aField

For Each shp In aStory.ShapeRange

With shp.TextFrame

If .HasText Then

.TextRange.Fields.Update

End If

End With

Next

Next aHeaderFooter

Next aSection

End Sub

Using Linked Values with Project Folders or Folder Templates:

If you have an Excel or Word file outside of your vault that was created and setup to link to PDM Enterprise variables like we just did, you may see some strange behavior when you add that file to the EPDM vault. By default whenever a document with properties is added to the vault, the file data card will populate it’s values based on what is already in the document. For instance, if you create an Excel or Word document outside of the vault that has a property called “Customer Name” and give it a value of “Unknown”, when you add that file to the vault the value in it’s data card will be “Unknown” if that variable is in the card.

This result is probably what you are looking for but it can cause some problems if you have a folder data card or template set up to populate a file data card. For example, this file was imported into a “Force Attribute Testing” folder. The folder itself has a data card that contains the Customer, Project Name and Project Number and I would rather have the folder data card decide what the Customer Name is rather than what is already in the imported document.

Method 1)

The first method is to manually update the folder card values to the file card values. Select the folder that the problematic files are located in then select the “Modify” drop down list. Now select “Update” and “Values in files…”.

This will take you through a tool that will let you choose what variables you would like to update and which files will be updated.

Once the tool has finished running you will see that the variable value inside of the file data card has now been changed to match the variable value inside of the folder data card.

If you open up this Word document, you will see that the field has been updated from “Unknown” to “Awesome Customer” just like it shows in the file data card.

Method 2)

The second method is to automatically have it rewrite any variable values inside of the data card when you import a document. To do this, go into your PDM Enterprise Administration tool by clicking the “Tools” drop down list and selecting “EPDM Administration”.

Then browse to the appropriate card for your Office documents. I only have one for all of my Microsoft Office documents in this vault. Open up the file data card and select the variable that you would like to re-write when a file is imported. Then select “Default Overwrites”.

Save and close the card editor. Now whenever a file is imported into the vault, the value that was in the document is replaced. The folder data card variable value is pushed to the file card variable value which then pushes to the document’s custom property value!

If you would like to know how to push a file data card variable up to a folder data card variable, please see here; InFlow Technology: Automatically updating folder properties in SolidWorks Enterprise PDM