Using excel to build simplified reps

Forum|Forum|6 years ago
December 19, 2019
4 replies
11774 views

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-

Assembly Design

Best answer by glmiller73

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 🙂 )

D

DavidBigelow

17-Peridot

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

G

glmiller73

Author

1-Visitor

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-

D

DavidBigelow

17-Peridot

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

J

jbob

12-Amethyst

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.

G

glmiller73

Author

1-Visitor

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

D

DavidBigelow

17-Peridot

Well.. that is pretty clever!

Nice job!

Dave

G

glmiller73

AuthorAnswer

1-Visitor

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 🙂 )

1_querry-a099-4-short-blanked.xlsx