11 Replies Latest reply on Jan 13, 2016 5:19 PM by Josh Brady

    Programmatically "Unhiding" Columns in a BOM

    Erich Tisa

      In brief, I am trying to programmatically "unhide" columns in a BOM (that is, a SldWorks.TableAnnotation object) in VBA. And then I am iterating over the columns and rows in the BOM and pushing the data to an Excel spreadsheet. I'm using SolidWorks 2015. The BOM types in question are indented BOMs (although hiding and unhiding columns should work on top-level BOMs and indented BOMs).

       

      For background, I am unhiding hidden columns because I'm exporting the contents of the BOM table either to a text file or to Excel. If you use the API or the interface (i.e., right-click, Save As...) to export a BOM table, any content in hidden columns will not be exported. So, in order to export a complete BOM table, hidden columns and all, you have to unhide any hidden columns.

       

      Let me just say that there is an apparently reliable set of operations that can be carried out in the UI that is more or less what I am trying to do programmatically. But my suspicion is that this operation cannot actually be implemented through the API, which would be a remarkable oversight. I'm hoping to be proven wrong.

       

      The set of operations to unhide BOM columns in the UI comprises the following steps:

       

      1. Right-click anywhere on a BOM table in a drawing document.

      2. On the context menu that appears, click the "Show Row/Column" command. The mouse pointer will change to indicate, evidently, that you are in "show/hide" mode:

      ShowHideCursor.jpg

      3. Click the column header of any blue-shaded (i.e., hidden) columns.

      4. Click anywhere else in the SolidWorks window to exit the Show/Hide mode.

      5. Note that the BOM "refreshes" and, if there was data in a given cell, the data becomes visible. Also note that when you are unhiding columns in this manner, a column may appear to be empty, but after clicking away, the data (if there is any) will populate the column.

       

      This is the UI command on the context menu I'm referring to:

       

      ShowRow.jpg

       

      Now, I have done everything I can think of to carry out an analogue of these steps in the API. I imagine that anyone familiar with the API will of course tell me to iterate over the columns (using the TotalColumnCount) to set the ColumnHidden property of each column to false. Here is such a routine, which iterates over BOM features and then iterates over the table annotations associated with those features to unhide any hidden columns. (Note: This routine is pared-down to the basics (that is, it excludes exception handling, checks for nulls, etc. and it assumes swApp is global and a drawing doc is open, blah, blah.)

       

       

      Private Sub UnhideBomColumns()

        Dim swDoc As SldWorks.ModelDoc2

        Dim swFeature As SldWorks.Feature

        Dim swTable As SldWorks.TableAnnotation

        Dim ColCount As Integer

        Dim c As Integer

        Dim TableArray As Variant

        Dim TableItem As Variant

       

        Set swApp = Application.SldWorks

        Set swDoc = swApp.ActiveDoc

       

        ' Get the annotation table from a feature or however else you prefer, say, by getting

        ' a BOM from a Feature using GetSpecificFeature2 and then getting annotations from

        ' the BOM using GetAnnotations or by using the GetTableAnnotations method of the View object.

        ' Using BOM features here.

       

        Set swFeature = swDoc.FirstFeature

        While Not swFeature Is Nothing

            If "BomFeat" = swFeature.GetTypeName2 Then

                Set swBOM = swFeature.GetSpecificFeature2

                TableArray = swBOM.GetTableAnnotations

                For Each TableItem In TableArray

                    Set swTable = TableItem

                    ColCount = swTable.TotalColumnCount

                    For c = 0 To ColCount - 1

                        swTable.ColumnHidden(c) = False

                    Next c

                Next TableItem

            End If

            Set swFeature = swFeature.GetNextFeature

        Wend

      End Sub

       

       

      Now, when I see something that toggles visibility in an API, I would expect that visibility is actually toggled---that you can interact with the newly visible item. But, if you run this routine, the BOM table columns will APPEAR to be visible, but the previously hidden columns will not be populated with content. If you right-click a BOM and click Save As to export to Excel (or if you do this programmatically), the previously hidden columns will be posted to Excel but without any data. That is, a column in Excel will show up for the previously hidden column, but each cell will be empty.

       

      HOWEVER: If, after running this routine, you right-click on a BOM and click the Show Row/Column command and then simply click AWAY from the BOM, you will see the data populate. It seems that this ColumnHidden property must be acting on some sort of phantom representation of the BOM.

       

      So, it seems that something is being executed when you click that "Show Row/Column" command that is not executed when you programmatically unhide columns. The "refresh" that happens is essential. As I say, I've tried everything I can think of to generate this refresh behavior. I've toggled visibility on everything from dimensions to views to entire tables and features. I've iterated over configurations and layers. I've tried to invoke that "Show Row/Column" command using the RunCommand method (of both the ISldWorks and the IModelDocExtension interfaces) but there does not seem to be a command ID corresponding to the "Show Row/Column" command in the swCommands_e enumeration. (I've tried to invoke any command with "hide" or "show" or "visible" in its enumeration member name.)

       

      But using RunCommand here, even if it were possible, would be a hack. Surely there is an API call that performs the functionality of the "Show Row/Column" command on a BOM. I've tried ForceRebuild3, GraphicsRedraw2, saving and reopening the drawing doc after unhiding columns---nothing invokes that refresh operation that seems to happen when you use the "Show Row/Column" command on the context menu.

       

      The reason this baffles me is that normally an API is a SUPERSET of the functionality available in the UI. I simply can't believe that there is something so simple (and necessary) that can be done in the UI but that the same functionality is not available in the API. I must be missing something. I can't say I'm especially familiar with SolidWorks (although I've spent more time than I want to with its API now) but I've worked with many APIs, even interfaces more convoluted than that associated with SolidWorks. But maybe I'm going about this all wrong. Maybe I'm not operating within the proper SolidWorks paradigm. I'm not even sure what that "refresh" operation that the "Show Row/Column" command invokes is---is it a regeneration of the BOM from the model? Is it a visual redraw? If I knew what it was or could trace it, maybe I could find something in the API.

       

      But surely someone has encountered this circumstance before. This seems like very remedial functionality and I expected to write the necessary code to get this working in an afternoon. Can anyone offer any suggestions?

       

      One further note: I know I can insert a NEW BOM for a given drawing view using a template derived from an existing BOM but with all the columns displayed. This works to some degree, but it doesn't capture everything in the current BOM (for example, if someone has made in-place edits in the current BOM). There MUST be a programmatic way to unhide columns of a given BOM, refresh (or rebuild the BOM table) and then export the BOM.

       

      I know this was long, but I wanted to be clear about what it is I'm after. And I wanted to forestall responses like "just right-click the BOM in the UI", etc., etc.

       

      Any help or alternatives or workarounds or even some sort of definitive declaration that what I'm after is not possible would be greatly appreciated.

       

      Thanks all. Be well.

        • Re: Programmatically "Unhiding" Columns in a BOM
          Amen Allah Jlili

          With a little modification, your code works on my end.
          This will show hidden columns with their data populated.

           

          Public Sub UnhideBomColumns()
            Dim swDoc As SldWorks.ModelDoc2
            Dim swFeature As SldWorks.Feature
            Dim swTable As SldWorks.TableAnnotation
            Dim swBOM As SldWorks.BomFeature
            Dim ColCount As Integer
            Dim c As Integer
            Dim TableArray As Variant
            Dim TableItem As Variant
          
            Set swApp = Application.SldWorks
            Set swDoc = swApp.ActiveDoc
          
            Set swFeature = swDoc.FirstFeature
            While Not swFeature Is Nothing
                If "BomFeat" = swFeature.GetTypeName2 Then
                    Set swBOM = swFeature.GetSpecificFeature2
                        Set swTable = swBOM.GetTableAnnotations(0)
                        ColCount = swTable.TotalColumnCount
                        For c = 0 To ColCount - 1
                            swTable.ColumnHidden(c) = False
                        Next c
                End If
                Set swFeature = swFeature.GetNextFeature
            Wend
          End Sub
          

           

          One more thing: Take a chill pill mate . I know frustration can be a mood killer (Tell me about it ).

            • Re: Programmatically "Unhiding" Columns in a BOM
              Erich Tisa

              Hey, Amen.

               

              Thanks for looking at this and replying. I can't believe you continued reading my question after the first couple of paragraphs!

               

              I note that I did fail to include a declaration for the swBOM variable in my original post. (I had passed that in as a parameter to the routine in my actual code and didn't take that into account in recreating this ad-hoc UnhideBomColumns routine when I posted my question.) But, with the inclusion of that declaration, your change pretty much amounts to the same routine as mine, except that you use just the first table in the array of table annotations rather than store the return value of GetTableAnnotations in an array and iterate over that. I suppose that it makes sense to use only the first item in the table annotation array if we're dealing with indented BOMs only (which, apparently, will have only one TableAnnotation object) instead of so-called "top-level" BOMs (which, it seems, can be associated with multiple TableAnnotation objects).

               

              In any case, I'd hoped that maybe there was something peculiar in how pointers were assigned if transmission of assignment passed through a variant array so I tried it as you have it here, but, as I suspected, whether using only the first item of the array as you have here or iterating over the array as I had been, the object you end up with (and the object that the swBOM pointer variable is set to) is still a SldWorks.TableAnnotation object. And it's the setting of the ColumnHidden property on that object that doesn't seem to be working properly. I'm not sure how this works on your end (or what the difference is between the BOM tables we're using), but it still doesn't work as I would expect it to work for me.

               

              Of course, that's not your problem, so forgive me for going on here, but, as an example, a BOM in a drawing doc might start out like the BOM in the image below, with certain columns hidden. (Note that the following images are deliberately small because I don't own the intellectual property here of my client. But you will get the idea of "visible" columns and their populated data from these images.)

               

              BOM1.jpg

               

              Then, I execute the UnhideBomColumns routine against that BOM (either using your tweaked version or my original version), and the formerly hidden columns are thereby displayed:

               

              BOM2.jpg

               

              But note that most of the cells are empty (though not the cells that were visible before running the routine).

               

              Then, if I simply click on the BOM table in the interface and then click the "Hide/Show" button on the popup toolbar:

               

              toolbar.jpg

               

              And THEN, without clicking on any of the column headers to hide or show any columns (because they are already all visible), if I just click anywhere else in the drawing doc outside the BOM (to, I suppose, deselect it and dismiss this popup toolbar), an operation happens (that I have been calling a "refresh" operation) which populates the data, as below:

               

              BOM3.jpg

               

              That third BOM image is of the same BOM table as the previous image (and the first image), the only difference being that I clicked the "Hide/Show" button on that toolbar after having run this UnhideBomColumns routine. (Instead of using the popup toolbar, you can right-click the BOM table and click "Show Row/Column" in the consequent context menu.) Now, most of the formerly hidden columns are "user defined" (i.e., swTableColumnType_UserDefined or zero in the swTableColumnTypes_e enumeration) and maybe that has something to do with it, but the data is there. You can add user defined columns to a BOM, add content, and then hide and show those columns in the UI and the data persists.

               

              Specifically, these BOMs might be something like this:

               

              Column Type: ItemNumber

              Column Type: PartNumber

              Column Type: UserDefined [with a given Column Title, e.g., "Description"]

              Column Type: Quantity

              Column Type: UserDefined [Column Title 2]

              Column Type: UserDefined [Column Title 3]

              Column Type: UserDefined [Column Title 4]

              etc.,

              etc.,

              Column Type: UserDefined [An equation.]

              Column Type: UserDefined [An equation.]

              etc.

               

              Maybe the BOMs you tried this on, Amen, have no user-defined columns or something. Although I can't see how that would make a difference.

               

              So, that's what I'm driving at. Simply setting the ColumnHidden property of a given column to false using the API does not seem to be enough to actually unhide the column in such a way as to make its data "available". There is an additional "refresh" event that has to happen. And my hypothesis is that this refresh event gets gets invoked when you unhide a column in the UI but it is NOT invoked when you implement the unhide API call that (supposedly) corresponds to the operation in the UI. I don't want to get ahead of myself, but I think there is some oversight involved here.

               

              Well, this is all more detail than you care to hear about, but I'm just trying to be as explicit as possible.

               

              And I do want to honor your advice about taking a chill pill. I'm sure I need one. But it won't do me much good if I have to engage with such a poorly documented and refractory API as this.

               

              Erich

            • Re: Programmatically "Unhiding" Columns in a BOM
              Amen Allah Jlili

              Have you tried recording a macro of hiding and showing the columns? Let's see if something is captured?

                • Re: Programmatically "Unhiding" Columns in a BOM
                  Amen Allah Jlili

                  My hypothesis (this can only be considered by SolidWorks API support and of course I can be wrong):

                   

                  With table annotations, there are two properties for cell values:

                   

                  The first one is .Text and second is .DisplayedText. You can think of those two are two layers of data.


                  According to the API:

                   

                  .Text set and gets the text inside cell: I'd use this for a formula, a custom property.
                  .DisplayedText, however, shows the text actually displayed in the cell which should be the return .Text ie whatever formula, custom property etc was taken from the text property.


                  You should get this by now...

                   

                   

                  I suspect that with those user-defined columns, whenever the user edits the cell, the only thing that gets changed is the displayed text property (the .text returns an empty string or nothing) which is not covered by the ColumnHidden property implementation but handled well by the built show/hide button (apparently it keeps an array of displayed text). Otherwise, why would ColumnHidden = false work and show data from hidden columns that are linked to custom property (as my testing showed to be true when I modified your subroutine. I tested on a part number column on sw standard bom).


                  I hope this is clear.

                   

                  When you unhide the columns, perhaps, try refreshing the table by setting the displayedtext to the displayedtext?

                   


                  I hope this works!

                    • Re: Programmatically "Unhiding" Columns in a BOM
                      Erich Tisa

                      Hey, Amen.

                       

                      Forgive me for not getting back to you for a while. I had to put this aside to work on other things.

                       

                      In answer to your question about recording a macro in an attempt to see what API members get called, yes, of course, I did try to record a macro, although doing so is almost always uninformative in SolidWorks. Try it yourself. Try to carry out most of the context menu operations on a BOM table while recording a macro and you will see that much of the time, the only thing that gets recorded is selecting the BOM table. (Certain operations, like splitting a table, do get recorded.) It's up to the developers of the product to expose things in the API such that they can be captured by a macro recording in VBA, and the SolidWorks API folks apparently didn't see fit to make most of of the non-trivial (i.e., useful) operations recordable.

                       

                      As for the difference between between the Text and DisplayedText properties, yeah, that was one of the first things I thought may have been involved a while ago. But whether you use the Text or the DisplayedText property, the result is the same: If a column has been hidden in the UI, and you unhide it programmatically, the contents of its cells are unavailable whether you try to access the contents using either the Text or the DisplayedText property.

                       

                      You might think that setting the DisplayedText property to a value (even itself) would refresh the table, but, of course, the DisplayedText property of a cell is read-only.

                       

                      Now, the Text property of a cell has a setter method as well as a getter method, so you can give it a value programmatically. And, there are some inspiring remarks in the documentation indicating that a table is "rebuilt" (whatever that means) when you set the Text property of a cell. So your hunch about refreshing a table using the DisplayedText property may apply here. The "trick" to refreshing a table may be setting this Text property. And it is true that setting a value for the Text property of a cell triggers a rebuild of the entire table (although this is horribly inefficient, as indeed the documentation concedes). I've carried out the operation and the table is rebuilt, but it is rebuilt as it was when the columns were hidden. That is, if you iterate through the columns of a table that has hidden columns and set the ColumnHidden property for those columns to false, you will see that the columns show up (although, as I've said, devoid of any content). If you then programmatically set the Text property of any cell to any value you wish, the table will be rebuilt and the columns will be hidden once again. You will not find this mentioned anywhere in the documentation.

                       

                      So, it seems to me that the only "solution" that seems to be available (given the current state of the API) is a semi-automated solution. That is, you can programmatically unhide columns of BOMs and then give the user the opportunity of selecting individual BOM tables, clicking "Show/Hide", and then clicking off the BOM table. Then you can proceed to export the data programmatically.

                       

                      This circumstance is, to me, horribly broken. And I note that all of the samples in the API documentation are careful to dance delicately around this matter of the ramifications of hidden and visible columns. There are no non-trivial examples in the documentation related to this, as if the developers know it's a problem but they're pretending it isn't. (I mean, come on. Look at this bit of whistling-in-the-dark: http://help.solidworks.com/2015/English/api/sldworksapi/Hide_or_Show_First_Column_in_Hole_Table_Example_VB.htm).

                       

                      You seem to be more trusting in the efficacy of the API than I am, but I'm not sure whence comes your trust. Once I start seeing API methods like InsertBomTable4 and GetSelectedObject6, I begin suspecting that I'm dealing with an API that was not particularly well thought out beforehand but tacked on to the product as an afterthought. (I used to be responsible for the developer documentation for the APIs of most of the Microsoft Office applications---now those APIs were hairy---and I know it's no easy matter to design and update an object model interface. So I don't want to seem flippant here. But, as I understand it, SolidWorks is a "best-in-class" product in its field.)

                       

                      At any rate, thanks for your time and consideration and help on this. If you detect any frustration here, it is by no means directed at you. I do appreciate your time. I just wish this simple operation was a more straightforward and tractable affair.

                        • Re: Programmatically "Unhiding" Columns in a BOM
                          Josh Brady

                          I have a macro that does basically exactly what you're describing.  It works no prob.  I will try to look at it sometime next week if I remember.  I assume the data in hidden cols is linked to custom prop?

                           

                          The reason you see things like InsertBomTable4 is that the SW API is backward compatible.  I'm not sure when it was first made public, but I know it was well before 2002.  As the program has expanded, so have the API functions expanded/improved/etc.  They get new arguments, new returns, etc.  If new versions of the API calls weren't given new names, old code would break.  With every release, thousands of aggravated customers' macros would no longer work. 

                            • Re: Programmatically "Unhiding" Columns in a BOM
                              Amen Allah Jlili

                              Can someone remount this the API support?

                              • Re: Programmatically "Unhiding" Columns in a BOM
                                Erich Tisa

                                Hey, Josh.

                                 

                                I'd be interested in taking a look at what you have. There must be something I'm missing. I hope you have something that works in the way I've been describing, although I'll have to admit that I'm a little skeptical. But don't be dismayed by that! I have to be wrong here. The only reason I'm skeptical is that I've tried to get at the data through configurations, views, custom properties, layers, etc. I've exercised all 64 members of the SldWorks.TableAnnotation object in one way or another, and all the members of the SldWorks.BomTableAnnotation object, casting between BomTableAnnotation and TableAnnotation as necessary. And, for good measure, all of members of the SldWorks.BomFeature object as well.

                                 

                                In answer to your question, many of these columns are of type "UserDefined" (as returned by the GetColumnType method of the SldWorks.TableAnnotation object). But this hidden/visible phenomenon seems to be problematic no matter what type of column it is. The column may be one of the standard column types (e.g., Part Number of Quantity) or a custom property column.

                                 

                                It may be that this "refresh" operation, on which the success of this functionality seems to depend, is stimulated from the component part (that it, the *.sldprt file) rather than from the BOM table in the drawing doc and I'm going about this all wrong. I don't know.

                                 

                                As for the incremented interface members (and this is entirely tangential and unimportant), yeah, I understand the purpose of the incrementation. My entirely uninteresting point is that if you find yourself dealing with an object model interface with a lot of such incrementations, it is likely that the API was not thought out well in the first place. (And this is not something I came up with; it's a Knuthian principle of good software design). It is usually a sign that too many normal but unrecognized use cases were not taken into account in the initial design of an API, and so such incrementation becomes necessary (at least with COM) in order to provide standard expected functionality over time. And, really, it's just another reason, in addition my experiences having worked with this API, that I don't necessarily trust that most things are "doable".

                                 

                                I don't want to bash the SolidWorks API too much here. It happens in many, many cases, and cruft can sometimes be unavoidable. It's why a lot of APIs get rewritten from the ground up after some time in the wild. But I wouldn't describe the SolidWorks API as particularly well-designed. (And there is no question that it is poorly documented. The GetAnnotation method of the TableAnnotation interface is said, for example, to "get the annotation for this table annotation". Lexicographers call that a tautological definition, i.e., a non-explanation.)

                                 

                                In any event, none of that is to the point. I'd love to see what you have, Josh, because, as I say, I would like to be utterly contradicted here in my assertion that it is not possible to do what I'm trying to do with the SolidWorks API.

                                  • Re: Programmatically "Unhiding" Columns in a BOM
                                    Josh Brady

                                    Erich,

                                     

                                    I tried to reproduce the behavior you describe, but I didn't have any luck.  When I run my code or your code the columns and data of the BOM are displayed immediately on-screen after the "ColumnHidden = false" loop, and the code can read the data.  I used some columns driven from custom properties and some columns with user-entered data. Which version of SolidWorks are you using?  I understand that due to confidentiality you can't post any of your customer's data, but can you create your own dummy assembly/drawing/BOM that exhibits the same behavior?