Using excel to build simplified reps

[glmiller73]

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-

[DavidBigelow]

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

[glmiller73]

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-

[DavidBigelow]

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

[Pettersson]

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!

[DavidBigelow]

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.

[Pettersson]

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 …

[DavidBigelow]

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 

[HamsterNL]

---

@DavidBigelow wrote:

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...) 😎

---

But...but...that's boring 😉

[jbob]

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.

[glmiller73]

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

[DavidBigelow]

Well.. that is pretty clever!

Nice job!

Dave

[Pettersson]

Nice! Anything you can share? Removing the company specific bits, of course. Sounds like a useful document to have.

[glmiller73 · Accepted solution]

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

[Trebla]

Hi Glmiller,

I checked with Creo4 and it works fine, also with all the $$$$ signs.

Good job, Thank you very much!!

[Pettersson]

This is a great start! Good research. I couldn't help improving on it a little bit by adding a VBA macro that writes the code to a file. With this version, you shouldn't have to do any copying and replacing. Just enter your parts, change the query file location if you want, then click the button to write the query file to disk.

If you make a mapkey that always reads the query file from that location, you should be able to automate this to a high degree.

Next step would be to move a lot of that code into the VBA macro to tidy up a bit (I moved it out of the way to a different sheet), then maybe make the parts list into a table, so that it can be filtered. Finally, one could make it so that it's easy to import a BOM from CREO. I imagine this can be useful for all sorts of things.

Note that this is a Macro-enabled Excel file, and the macro needs to be enabled for it to work. I had to ZIP it for the forum to allow it to be uploaded. You can check out the simple macro before you activate it, if you want to make sure it doesn't do anything sinister. You should always be careful with these types of files!

[Trebla]

Hi Pettersson,

Thank you for your VBA contribution, I checked in Creo4 and it works fantastic!!

[jmonk1]

Does anyone have a solution for when you have different instances of the same part (for example fasteners) that need to appear in different simplified reps? Using this Excel method will bring all instances of a given part name into the new rep.

[Pettersson]

Long time since I was logged in here. You'd have to use something like Component parameters or feature number/feature ID. You could have a column in the Excel file for the feature ID of the part, then use that to build the Query file. I'm not sure how user friendly that would be, though, as you can't really tell the different Components apart without looking up their IDs in Creo (most easily done through a tree column).