Revisions in BOM's 1

[gross01]

Hello SW people:

I am trying to create a BOM to do 2 things:

1. To have the revisions of the parts not to display the trailing numbers after the decimal point. 1A.01 to be displayed a 1A

2. Our parts have a propriety call "class number" (a 3 digit number)I would like to for example if a part has a class number 099 then the revision would not be displayed.
Item qty pt no. rev description class
1 4 123456 1A.00 Gromet 099
2 1 789123 3C.01 Plate 302

this is what I would like to achive

1 4 123456 Gromet 099
2 1 789123 3C Plate 302

I'm not sure how to go about this I though somebody out there might be able to help.

thank you for your time.
-Caper-

 

[RawheadRex]

Caper,

What I think you need to do is set up a custom BOM template which contains a macro consisting of an "if...then" or "select case" evaluation statement that reads the contents of your "class" cell and selectively clears the "rev" cell as required by your criteria.

That's a broad brush view of what it seems to me that needs to happen. While I know that Excel supports decision structures such as "if...then" I have to admit that I've never employed them in the Excel environment (I'm mainly a VB guy). It seems to me that's the best place to validate BOM field values though. Hopefully, someone can be more specific as to how to pull it off if you need help with that. If not please let me know and I'll try to find some time to look into it.

Chris Gervais
Mechanical Designer
American Supercondcutor

 

[macPT]

gross01

I think you should try to create your BOM using EXCEL. With the VBA from EXCEL (which is a lot more than decision structures) you can create a macro (a VB program) to extract the assembly and components data into cells and control the contents of these cells for an adequate output.

Regards

 

[gross01]

Sounds like I need to brush up on my EXCEL skills.

Thanks for the comments.

-Caper-

 

[RawheadRex]

Caper,

While macPT may have a point I think that he's suggesting a more complicated approach than necessary. The algorithm in my mindseye is as simple as the following:

1. Generate the BOM using SolidWorks (this routine already exists within SolidWorks and works fine without having to re-write it if I'm interpreting macPT's suggestion correctly, if not my apologies)

2. Once the data is in the BOM cells all that needs to be done is to evaluate the contents of the cells you want to manipulate with an excel macro (e.g. IF F2 = 099 THEN D2 = "&quot.

It's a little more complicated than that to pull off I'm sure but that's generally how it would break down in my opinion. VBA in this case might be overkill for what you're really looking to do. I have a former co-worker buddy of mine who I know did something similar replacing the values in a BOMs quantity field with "AR" using the native Excel "If...then" functionality and this is pretty much the same thing in my view. In any case, yes you're probably going to need to brush up on Excel.

Chris Gervais
Mechanical Designer
American Superconductor

 

[macPT]

RawheadRex,

I don't re-write the BOM. What I do is create the BOM directly in Excel. With the assembly as the active doc., I open an Excel file with a VBA macro that reads the information from the assemby (whitch are custom properties like Part N., Part Name, Weight, cost,... and file information like filename, assembly level, quantity, ...) and put it on cells. With an Excel VBA macro there is virtually no limitations about how to control the information you want (and I think that some information I need would be very difficult to have on the native BOM of SW).
And belive me in 2 points:
1 - VBA is not "if..then". VBA is Visual Basic with some aditional code specific for the application (in this case the Excel and SW). Mabe there are some limitations comparing with the Visual Basic programing tool from M$, but I think not in the code.
2 - I started to program in VBA (making macros for SW, Excel and AutoCad) less then 2 years ago. I'm not a guru, but I can manage to do some usefull programs (specially for some time consuming tasks or to turn arround some limitations of SW). I think that any engineer with some programing skills can program in VBA in a very short time. Read the API help from SW. Analyse the API examples from SW. Download an analyse all the free stuff you can find on the internet (you can start with the FAQ folder). It pays the effort.

 

[RawheadRex]

macPT,

Yes, I agree that VB/VBA is much, much more than "if..then" constructs. In case I wasn't clear previously, I already have a pretty decent amount of experience with programming for CAD automation and client/server applications. I know what VBA is (and isn't for that matter) and you're correct that there are limitations when compared with full-blown VB (which is what I code in mostly, although I have some C++ background along with a good measure of SQL programming, as well as AutoLISP from my days on AutoCAD).

I'm sure that you write good programs/utilities which do what you want and work exactly the way you want them to. However, I believe the initial question deals with simple text data manipulation quite similar to Find/Replace functionality in Word and that VBA isn't necessary.

Assuming that the initial question deals with manipulating BOM data created in the SolidWorks drawing environment two things need to happen:

1. Read the BOM data into the format of choice within the SolidWorks drawing environment.

2. Validate the contents of the class entries and update the corresponding revision entries for each row.

How to do this:

1. Create an on-sheet BOM based on a drawing view associated with an assembly model in the SolidWorks drawing environment.

2. Apply a similar formula to following the SolidWorks BOM template: IF(F2=099, D2="", D2=D2) Here the first argument (F2=099) is the value to check for, the second argument adjusts the contents of a cell (D2), the third argument sets the contents of a cell (D2) equal to itself if the result of the first argument returns "False."

That's it. In this instance, the desired end result is achieved more simply than if one used VBA.

If the BOM needs to be generated from the assembly model without benefit of the BOM functionality available in the SolidWorks drawing environment then, yes, I agree with you that one is much better served by VB/VBA.

Chris Gervais
Mechanical Designer
American Superconductor

 

[macPT]

RawheadRex,

Now I understand the meaning of your first post. Of course you are right, the problem of gross01 does not have to be solved using VBA. The use of Excel functions can help to control the BOM output.

Don't misunderstand me: I was not giving you a lesson on VB/VBA. I was just trying to say to readers of this thread that starting to use VBA is not very difficult (if this was difficult mabe I should not be able to do it) and the investment can be easely paid in time saved in automated tasks.

About writing good programs, you are just being nice. I'm shure that any experienced programmer would find my programs funny. Nevertheless they work and help me a lot.

Regards

 

[RawheadRex]

macPT,

As I look back at it now, I used the term "macro" when referring to what is really a formula function in Excel. Bad choice of words on my part, the initial interpretation you had was on the mark when I think about it now.

VBA is a neat tool for doing automation and macros. Pretty much anyone with a rudimentary understanding of programming logic ought to be able to pick up on it and create useful tools with a bit of practice. When I was first learning it a couple of years back it hit me all of a sudden that anyone familiar with the "old" BASIC language could pick-up on VB/VBA easily. That's because VB/VBA is the BASIC language updated for the Windows environment. As my instructor said, "Visual Basic programming is Windows programming."

Don't sell yourself short. If you've got utilities that accomplish the results you desire then what's to laugh at. Programming is kind of like CAD there's not really any single road to a particular end result. If it works then hey, don't knock it. Anyway, believe me I'm not an ultra-geek C/C++ programmer (although I know a couple) constantly looking for the smallest amount of code to most efficiently accomplish a task. So I doubt that I'd find your programs to be funny.

Chris Gervais
Mechanical Designer
American Superconductor