14 Replies Latest reply on Feb 1, 2018 12:59 PM by Mike Childers

Manage custom properties of all parts of an assembly in one excel file

Mike Childers Jan 25, 2018 11:30 AM

I'm not sure which section is the best place to post this question, as I'm not sure how to do it.

For a given project (assembly) we have a "master" spreadsheet, that will have all the filenames of all the parts, and all the important information about that part, including information that will end up being in the drawing bom, but also many more columns of information associated with that part (like outsourcing vendor information), important dates, etc.

My question is I would like my master spreadsheet to be the master for all the custom properties in a part. If a part has a custom property, I want that property to be a column in my master spreadsheet. Then if I update a column in my master spreadsheet, for then to have some way of updating the custom property in the part.

Ideally, I would like it to be two way, that is if I update the spreadsheet, it will update the custom property of the part. Or if I update the custom property of the part, it will update the master spreadsheet.

We do not currently use PDM, so I don't know if that is a feature of that. Does anyone know how I can implement this?

Thanks. We are currently using version 2017.

Re: Manage custom properties of all parts of an assembly in one excel file

Paul Wyndham Jan 25, 2018 6:34 PM (in response to Mike Childers)

I am not sure if you can have an assembly design table that can drive the custom properties of the parts in the assembly, but you can save the design table outside of the assembly model and then link them together.

Add a design table to the assembly.

Open the design table in a new window. This will open it in Excel.
Save the design table to a folder.

Edit the properties of the table to say from File and link it to the file.

Edit the design table with the desired custom properties like this $PRP@Description

This assembly had two configurations so that is why there are two rows. But I can either edit the design table from within the configuration editor or from the excel spreadsheet.

If you update the spreadsheet you will have to open the assembly before the changes take affect.

If I have time tomorrow I will see if you can add parts to a spreadsheet.
Like Show 0 Likes(0)

Actions
- Re: Manage custom properties of all parts of an assembly in one excel file
  
  Mike Childers Jan 26, 2018 10:02 AM (in response to Paul Wyndham)
  
  Yes, thank you, but that will only control the custom properties of the assembly. I was looking for a way to link the properties of multiple parts in one spreadsheet. Thanks.
  
  Like Show 0 Likes(0)
  
  Actions
Re: Manage custom properties of all parts of an assembly in one excel file

Ulf Stockburger Jan 26, 2018 7:37 AM (in response to Mike Childers)

Hi Mike

Maybe this will help you: https://forum.solidworks.com/thread/38414#819437
Search for SWie_1.2.zip

BiI SAP-PDM
Like Show 0 Likes(0)

Actions
- Re: Manage custom properties of all parts of an assembly in one excel file
  
  Mike Childers Jan 26, 2018 10:22 AM (in response to Ulf Stockburger)
  
  I have experimented with the SWie spreadsheet/macro before, but it never works completely for me. Some properties will get populated but not all. But that's definitely the idea
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: Manage custom properties of all parts of an assembly in one excel file
    
    Ulf Stockburger Jan 26, 2018 11:56 AM (in response to Mike Childers)
    
    I tested it with one part and two configurations. The import and export went as expected. Do you have a small example that shows what works and what doesn't?
    
    BiI SAP-PDM
    
    Like Show 0 Likes(0)
    
    Actions
    - Re: Manage custom properties of all parts of an assembly in one excel file
      
      Mike Childers Jan 26, 2018 3:30 PM (in response to Ulf Stockburger)
      
      I will try to put together a small assembly and show results. Thanks.
      
      Like Show 0 Likes(0)
      
      Actions
      - Re: Manage custom properties of all parts of an assembly in one excel file
        
        Mike Childers Jan 29, 2018 2:28 PM (in response to Mike Childers)
        
        This is an error I got today while exporting part data.
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Manage custom properties of all parts of an assembly in one excel file
        
        Ulf Stockburger Jan 29, 2018 3:05 PM (in response to Mike Childers)
        
        Hi Mike
        
        Can I get the files to understand why the error is being thrown? Otherwise it's not possible for me to identify the error.
        
        BiI SAP-PDM
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Manage custom properties of all parts of an assembly in one excel file
        
        Mike Childers Jan 30, 2018 11:20 AM (in response to Ulf Stockburger)
        
        Unfortunately, I won't be allowed to do that. I'll try to test it on an assy that I can send and see if I can make it generate the error.
        Thanks for the help.
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Manage custom properties of all parts of an assembly in one excel file
        
        Ulf Stockburger Jan 30, 2018 11:45 AM (in response to Mike Childers)
        
        No problem, I wish you success.
        
        BiI SAP-PDM
        
        Like Show 0 Likes(0)
        
        Actions
Re: Manage custom properties of all parts of an assembly in one excel file

Adrian Velazquez Jan 30, 2018 3:11 PM (in response to Mike Childers)

Mike the closest(simplest) thing you'll get to this Out-of-the-Box would be to use Treehouse to open your top level then use the "Open in Excel" option, this will list all components with their correspondinf CP values.

Unfortunately this is not Bi-Directional. Seems like it's a good time to consider inverting in PDM
Like Show 0 Likes(0)

Actions
- Re: Manage custom properties of all parts of an assembly in one excel file
  
  Mike Childers Jan 30, 2018 3:48 PM (in response to Adrian Velazquez)
  
  What capabilities would PDM give?
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: Manage custom properties of all parts of an assembly in one excel file
    
    Adrian Velazquez Feb 1, 2018 12:49 PM (in response to Mike Childers)
    
    Well, you could setup a Search that would generate the Table you desire instantly. From there you could update Properties via DataCard, you would be able to track who updated your properties. An you could do a Batch update of properties per folder or other property
    
    Like Show 0 Likes(0)
    
    Actions
    - Re: Manage custom properties of all parts of an assembly in one excel file
      
      Mike Childers Feb 1, 2018 12:59 PM (in response to Adrian Velazquez)
      
      I think we are getting PDM (ePDM?) this year, so I will definitely check it out. Thanks.
      
      Like Show 0 Likes(0)
      
      Actions

Go to original post