Nice! Anything you can share? Removing the company specific bits, of course. Sounds like a useful document to have.
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 😉
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 🙂 )
I checked with Creo4 and it works fine, also with all the $$$$ signs.
Good job, Thank you very much!!
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!