cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Using excel to build simplified reps

SOLVED
Highlighted
Regular Member

Using excel to build simplified reps

I have assemblies with many versions of an assembly in one large file. Is it possible to use a list of part numbers in an excel sheet to activate/select the parts needed to create a simplified rep. All of the parts exist in the assembly, it is just time consuming searching through the model tree trying to find the 50 to 100 parts I need out of the thousand or so parts in the top assembly. I have used excel in the past to calculate values to build shafts from so I can do the interface part, I just need it as an advanced "selection" tool. The existing method of picking parts to create a rep are limited. Thanks for any help, Gary-

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Using excel to build simplified reps

I am attaching my solution, I replaced my personal info with $ signs, and it actually still works...so, to use it on sheet 1 the yellow columns are where your part numbers go that you wish to use to define the simp rep...it will generate the strings needed. I made it for up to 250 parts. Sheet 2 gives the output, copy paste that as a whole into notepad. In order for creo to read the file use search replace to replace all the double-double quotes with a single double quote hope that makes sense….replace "" with ". Then in the first line remove the very first and last quotes that are on each end of the line. Save the file as your query file (qry extension). Then in creo build your simp rep and use the "retrieve query file" option to read in your file...pick yes where says no model found...query is built and pick ok. (this is for the solid model name search option of course)… good luck hope it works for you...use at your own risk of course...(feel like I should add a disclaimer 🙂  )

View solution in original post

16 REPLIES 16
Highlighted

Re: Using excel to build simplified reps

Hmm.. Assuming you have some meta data in the models that would be useful for your goal...

 

Would constructing a rule work?

 

https://learningconnector.ptc.com/content/tut-3268/rule-based-simplified-representations

 

Dave

Highlighted

Re: Using excel to build simplified reps

Rules...yes, to explain further. My model (just for reference) is like a Silverado 1500 truck with all the possible versions in it. There is a manufacturing bom that contains all the parts needed to build one particular truck. I want to use that excel sheet to make a simplified rep of that one particular truck.  I got to the point of using rules, then picking "options", under that is retrieve query file, but the file it reads is code that is created during the "build query" stage and is not just a text list of parts. IF it were a list of parts without all the code...it would be perfect and all my parts would be selected. That would be great and I would have a rep that match what was being built. Only thing I tried at that point was take a query file and edit where the part numbers are and copy and paste...but that is way too much to build a search of a hundred parts.  Thanks, Gary-

Highlighted

Re: Using excel to build simplified reps

Hmmm... 

 

Well - I think rules would still be useful - IF you had a well structured set of Parameters and Values in your Models that would be used for some simple rules to pick up on... But the more I think about it, that could be a difficult situation to manage or develop rules for (e.g. part A and B have the same parameter value -- but you really don't want both of them to show up in some situations).   Hence, I see why you are wanting that to be done a certain way from a list of parts - completely logical.

 

What about the Options Modeler? (if you have it)

OR - something in Windchill to manage the BOM list/configuration?

 

Another thought - (Creo specific) you must have all of these options pre-assembled and are just toggling their display (e.g. vehicle position alone or vehicle position + local constraints for sub-assemblies).   Not an uncommon thing, but a bit of a headache to manage when you have a lot of options.

 

IF you have a pure vehicle position, or a skeleton based model referencing key mountings via CSYSs, you might consider just "building the configuration" from scratch as an option.  But that would require a bit of scripting to make happen - could be done easily via CREOSON ... programming the simplified rep function to add these options is also an option... but is expensive to do yourself or buy (technical debt).

 

I feel your pain on this one...  I will keep thinking a bit more.

 

Dave

Highlighted

Re: Using excel to build simplified reps

using the advanced search and then build a query allows you to enter more than one component at a time to build a simplified rep, then save the query.  now if there was a way to cut and paste into this query, this might get you close to what you need. not sure how easy this would be.

Re: Using excel to build simplified reps

Yeah, I think building a query file is going to be your best bet. It shouldn't be too hard to write an Excel macro that writes a file Creo can read. It's basically going to do this:

 

  1. Write all the stuff in the beginning of the file that will be the same each time.
  2. For each row: if the "Include" column is set to "yes", write a code segment that includes the file name.
  3. Write all the stuff at the end of the file that will be the same each time.

 

That should be possible in Excel and you could probably do it just by adapting some tutorials. Search for "Excel VBA write to text file" or something similar.

 

Then you just launch the Excel macro to write the query file, and you could do a Creo mapkey that sets everything to exclude, opens the Search tool, reads the query file, selects all the hits and sets them to include.

 

If you do end up writing the Excel macro, do post it! It's pretty interesting, and I'd love to have a demo of this. If you go all in, you could even build a PowerQuery table that imports a Model Tree export file, so you can easily update your Excel file when new models are added!

Highlighted

Re: Using excel to build simplified reps

Love that you mentioned PowerQuery - THAT is some killer underused capability right there... basically the best data slicing-and-dicing tool in Excel (period)...  We use it extensively when it makes sense - and it often does - very handy for configuration management and integration.

Highlighted

Re: Using excel to build simplified reps

Yeah, PowerQuery is great. I'm no Excel guru, but I recently had some great use for it. A fun little project: I built a Repeat Region that makes a custom cabling BOM, which eliminates spaces from the article numbers and truncates them (lots of spools with suffixes in their article numbers). The user then exports this to a CSV, then opens an Excel spreadsheet that uses PowerQuery to import the data, combines all the rows with identical article numbers and sums their lengths (couldn't get Creo to sum up a non-quantity parameter in the Repeat Region). Finally, three VBA macros: One for browsing for the the CSV, one for refreshing the PowerQuery table (so you don't need to browse for it again if the CSV has been updated), and one for copying the information to the clipboard so it can be pasted into the PLM system (which apparently couldn't import data from a CSV itself …).

 

A fun little project, and one where frustratingly limited functionality in Creo and the PLM system could be worked around by the amazingly extensive capabilities of Excel.

 

I think there's a lot of integration possibilities between Creo and Excel, by using things like PowerQuery and VBA macros to import and export files like model tree configurations, points tables, family tables, pattern tables, repeat regions, parameters …

Highlighted

Re: Using excel to build simplified reps

Yep - zero doubts on that...

 

We often use PowerQuery to take external data (bom or metadata from whereever) to build execute instructions for Nitro-CELL within Excel Worksheets - (e.g. assemble components by reference, mass parameter updates, compute costs, update data from external sources, etc. etc. etc. )

 

PowerQuery works like a charm and rarely requires even a single excel formula to get to the desired output/result... (let that sink in ... for all you VLOOKUP, INDEX, MATCH fans out there...) 😎

 

Dave 

Highlighted

Re: Using excel to build simplified reps

I solved it from the excel side. I created a spreadsheet that concatinates the header info from a typical query, and a vertical column of cells where I drop in the partnumbers I want to show as part of the simplified rep. In my case I left open 250 cells. and beside those cells it concatinates the string needed for each of those partnumbers. Also the footer string is concatinated. All this gives the printout I need in the order I need on a separate worksheet, and I copy paste that excel sheet into notepad. You have to do a quick search and replace all the pairs of double quotes with single that show up in the top header line, and remove the " at the beginning and end of the header line save it as a .qry (query) file and creo will read it in under the Read Query option. It works 🙂   thanks for the responses, Gary Miller

Announcements
Message from Brian Thompson (PTC General Manager and DVP) regarding Creo Business Continuity plans.