Populating cells in Microsoft Excel with EPDM variables

Article by Chris Briand, CSWE updated April 21, 2013


In prior posts we have shown you how to link EDPM variables to the custom properties of a Microsoft office document.

What if you need to push that data into the cells of a Microsoft Excel Spreadsheet?

To be able to write any properties back to specific cells in an Excel document, you will need to write an additional VB Macro that will update the files upon opening with any changes made with Enterprise PDM Variable mapping.

In the case of updating a cell in the spreadsheet that would carry a value for a Project Name

The steps to follow in order to associate the cell in the spreadsheet to the EPDM variable would be:

  1. Define a name for specific cell that would be updated (“projectname” for example in this example) This could be done by naming the cell (Option in the upper left-hand corner of the sheet)
  2. Define appropriate data type (test, number, Date etc) to the cell based on type of value it will be displaying.
  3. Define variable mapping to Excel custom properties for Enterprise PDM variables.
  4. Add the following VB code in the sheet that will trigger on a Document open action (Press Alt+F11 in your spreadsheet to open the VB editor and place the code)

Private Sub Workbook_Open()
Sheet1.Range(“Projname”).Value = ThisWorkbook.CustomDocumentProperties(“Project name”)
Sheet1.Range(“Projnum”).Value = ThisWorkbook.CustomDocumentProperties(“ProjectNumber”)
Sheet1.Range(“Title”).Value = ThisWorkbook.BuiltinDocumentProperties(“Title”)
MsgBox (“Properties updated – save the document before exiting”)
End Sub

In our example, the sample properties are mapped to the values on the Excel data card that gets installed with the default EPDM vault.

These values are:

a. Project Name

b. Project Number

c. Title

Additional properties can be mapped/updated using the same steps mentioned earlier.
Please use the above as a sample only.

NOTE: Starting with Office 2007, macro enabled Excel files are to be saved as .xlsm files

Related Links

Want to do more with SOLIDWORKS PDM?

Our Certified SOLIDWORKS PDM Experts can help you to:

Find Related Content by TAG:

Chris Briand, CSWE

Chris has been educating and supporting Engineers, Designers and IT Personnel within the 3D CAD industry since 2002, and was adopted into the fantastic team of applications experts here at Javelin Technologies in early 2006 and migrated along with his team members to the TriMech Solutions team in 2021.  Chris enjoys the continuous learning driven by the ingenuity and challenges Designers bring forward. Innovation using 3D Printing, 3D CAD and other technologies, combined with a diverse background as a technologist, allows Chris to find solutions that accelerate Designers, and take Design Teams to new heights. Chris is currently being held at an undisclosed location, near Halifax, Nova Scotia, Canada.