One of SOLIDWORKS PDM’s most underused but powerful capabilities is the ability to map PDM variables into non-CAD document types. In this tutorial, we explore how to use the CustomProperty function in Microsoft Excel to map PDM variables into an Excel document. To illustrate this, we will be creating an ECR (Engineering Change Request) template to be used to create new ECR’s through PDM.
In this article, we'll cover:
Required items to use this tutorial:
This step is very important because without the template file none of this would work!
As you can see, I already have several cells named what I will ultimately be tied back to their master PDM variables:
Once in the Name Manager, you can assign a name to a specific cell. In the example above, to the right of my title ‘ECR Number’ where I want the ECR Number to be displayed is where I will assign the name ECR_Number to that cell (cell C3 in this case).
Note: You must use names that do not include spaces. I use the underscore ( _ ) where a space is needed.
To create a new cell name definition, simply click ‘New…’ and create it where it is needed. You need to provide a name and a cell location in the workbook.
Once your cells are properly named, when you highlight the cell you should see the following in the cell definition block up near the ribbon:
*We will still need to update the Excel file to ensure the mapping works correctly.
From the data card, we can select any control and open the variable editor easily.
Once you are inside the variable manager you can select the variable you want to map. In this case, our variable ‘ECR Number’ is already selected.
If the are no assigned Attributes for the variable you are mapping, simply click the ‘New Attribute’ button near the bottom right of the variable manager.
Three primary components need to be entered accurately in the variable manager for each variable you intend to map:
Now that we’ve correctly named and assigned our variables in the Name Manager of the Excel template file and mapped the applicable variables from PDM to the Excel CustomProperty block, we can link them in the File Properties of the Template.
Once the Advanced Properties dialog window is open, click on the Custom tab:
If your template is new, unlike mine, you should see no entries in the Properties section of the Custom tab. Here’s where the rubber meets the road with getting your data from the data card and the file inside of PDM into the Excel file!
When you create a new link in the Custom Property section of Excel the name of the Property (green box in this case) MUST match the Attribute name in PDM (also green here). Once you’ve typed the name correctly, you must next check the ‘Link to content’ box which will bring up a list of items to choose from in the Source (Red box above) dropdown. If you’ve named your cells correctly in the Name Manager back in step 1 then your list should be populated with all the relevant variables you want to link. In this example, we will select ‘ECR_Number’ from the list and then click ‘Add’ (the Add box will be where the grayed out Modify box currently is in the above screenshot). Manually add all your PDM variable attributes and link them accordingly using the same process.
Now that you’ve properly built your Excel template file, assigned the attributes in PDM, and mapped them to your Excel template file, it’s time to test it out! I will use my ECR Data card for this example and show what the process looks like from start to finish.
ECR Data Card: Fill our your template data card with the appropriate information.
New ECR File Properties inside of Excel: All the mapped data should be present.
PDF of automatically generated ECR File.
A few things to note:
Any variables that you mapped that are tied to your workflow, like Approved By or Approved Date, for example, will also be mapped when the action in the workflow occurs. In my example, I have an ECR approval process in which once the ECR has been approved the Approver and Approval date gets stamped to the data card. Once the data has been written to the data card it will automatically update and map into the Excel file accordingly.
In conclusion, this guide covers how to correctly configure your Excel file template and map variables from a SOLIDWORKS PDM data card into that template file for new file creation. The result will be time savings and consistency with your documents, like ECOs and other commonly used engineering documents.
It’s also important to know that this is just one example of how to map variables and data from PDM into other file types. While it is widely used for Microsoft Office documents like Excel and Word it is also highly useful for other files like PDFs, Autodesk/AutoCAD files, and many more.
About Steven Humphrey
Steven Humphrey is a PLM/PDM Solutions Architect based out of San Diego, California. He earned his Bachelor’s degree in Mechanical Engineering from the University of South Florida then earned his Master’s degree from the University of California, Los Angeles in Engineering Management. Steven has an extensive engineering background with over 10 years experience in a variety of industries. He joined the GoEngineer family in July 2019.
Get the latest articles delivered daily to your inbox, unsubscribe at any time.