Excel Documents as SOLIDWORKS PDM Forms

Written by: Bryce Hooper, Application Engineer at DASI Solutions

Filling out forms is always a drag. Automating those forms with data cards and transition updates is a great way to alleviate that burden. There are many forms we see around the industry, so we’ll start with an example in Microsoft Excel.

Here we have an example of an Engineering Change Notice form.

Now, to map these properties to our file and our data card, we will need to start with the variable setup. For Microsoft Office documents, the setup is pretty simple and should be familiar. For each variable we are mapping, we will want to use the attribute block CustomProperty. This should be familiar as it is the same that we would use for SOLIDWORKS documents.

Set this to use the proper extensions that we are intending to use and the rest of the options can be set at your discretion.

We then turn to our spreadsheet to set up the variables inside for display. The first step to this is to create a named range. It’s generally a good recommendation to name this range something close to your variable/attribute name for ease of setup later.

Note: No spaces can be used in the name for a range. See that in the image below I have simply pushed all of the text together without spaces.

After these ranges have been named, it is time to map the custom properties to those ranges. This is done by going to the file info inside of Excel, then properties and Advanced properties.

In the dialog that shows we go to the Custom tab. Here we find a list of properties that are already defined. If your variables haven’t mapped to the file yet, that is fine as we will create the link here. If they are mapped that is fine as well, we will link their pre-existing values to ranges. To do this, we can select the name in the list or type a new name.

Check the box for “Link to content” and select the named range from the Source drop-down. Click add to add it to the list. From here on out, those cells will be filled in with our values from the data card.

From here, we can make things even easier by creating a template to help us fill in values or serialize a naming convention. We can also create actions in our workflow transitions to automatically fill in names and dates for approvals.

There are of course some pros and cons to this technique. I’ll break them down a bit here. By no means is this an all-encompassing list.

Pros:

Your company’s forms are probably already in Excel (or some other Office format), so the translation isn’t difficult.
If they aren’t already in Excel, this is an easy program for anyone else to learn and create forms.
Setup for this is fairly simple and intuitive. The process works similarly for other office formats.

Cons:

The PDM Preview window does not update until the file has been checked out, opened, saved, and checked back in.
Requires Microsoft Office on any machine that would need to view/print it.
Does not handle changes down the line to the form/format with ease. EX: As your company uses the process and changes the formatting or logo, old forms will not update to the new format without manual updates.
As of this article, some variable types either don’t work (Yes/No) or don’t display correctly (Date)

If you’re looking for other ways to create forms inside of PDM, you may also want to check out our upcoming blogs in the coming months on Microsoft Word and XML documents as forms in PDM.

Bio
Twitter
Facebook
Latest Posts

DASI Solutions

DASI Solutions is dedicated to service and support. As one of a handful of original, charter value-added resellers (VAR) in the SolidWorks Community, DASI Solutions has built partnerships and success stories with many of our customers. We are very pleased to bring you SolidWorks 3D CAD design engineering software and 3D printing services.