Using a DataGridView control in VBA or iLogic

[JBerns]

Community,

I would like to design a UserForm for use in Inventor that includes a DataGridView control (table). For example:

I have searched the forums, and this article comes closest to a match:

https://adndevblog.typepad.com/manufacturing/2013/11/table-editing-from-ilogic.html

I have not found a way to add the ComboBox and buttons. I was hoping to find a more native (VBA/iLogic) solution rather than depending on an external file created and compiled with VB.NET.

Is it possible to build a UserForm shown above from within VBA or iLogic? The table would be populated with information from sheet metal parts. The ComboBox will get names from a SQL database. The Update button will apply the selected customer name to the custom iProperty in the sheet metal parts.

Thanks for your time and attention. I look forward to the replies.

Regards,

Jerry

[JBerns]

Knowing the "1, 10, 11, 2, 3, ..." sorting issue, I added leading zeros to improve sorting.

If I add a blank row at the top, then the 01 part sorts correctly, both ascending and descending. Interesting though, the blank row is always at the bottom. Remove the blank row and the sorting problem returns.

Perhaps there is an issue when sorting Index 0 in a DataGridView.

This is my workaround until someone has a better solution. Thanks for the response and suggestion.

Regards,

Jerry

[JamieVJohnson2]

Well, the better solution is not 'super easy' but it is very correct.  Your list of data (the background data that is supplied to the datagrid) has a sorting method, also the datagrid calls a sorting method.  You need to hook into the sorting method, and overwrite it's logic to treat the sortable values as numeric (cdbl(value), cint(value), ctype(value,numeric type), trycast(value, type)) then implement the compare.  All this gets very fun when you create your own class of widgets that implement iComparable, and then create your own comparing routine that allows the objects to return -1, 0, or 1 to say object A is before, same as, or after object B when a generic sort is called upon them.

You can keep it simple by overriding a custom sort, or complicated by creating a custom comparer in a custom class, OR even this; make sure the numeric value is stored as a numeric data type from the very beginning, and not a string data type, then rely on windows to use the proper sort method instinctively.

[JBerns]

Jamie,

That sounds complicated. I will use my workaround for now until an alternate solution is required.

Thanks for the info though.

Regards,

Jerry

[NachoShaw]

You could also add a column to the data collection as an integer and try sorting by that although, it may also become a second instance of the issue you already have...

[JBerns]

@JamieVJohnson2 / @pball / @NachoShaw / @matt_jlt ,

I was able to get the code working using only a DataGridView and a workaround to resolve a sorting issue.

To expand my knowledge, I rewrote portions of the code to use a DataTable, DataView, and a DataGridView.

I have encountered challenges that involve cell BackColor and a column named, FullFileName. I store the reference document name in this column, but don't want to display it.

Here is a summary of the code:

1. Dim a DataTable (myTable), a DataView (myView), and a DataGridView (myDGV)
2. Build myTable with columns (BuildBaseTable)
3. Set myView based on myTable.DefaultView [line 159]
4. Sort myView by Flat Pattern, Component [line 161]
5. Populate the DataTable with sheet metal component info (RetrieveReferences)
6. Format myDGV [line 169]
7. Add myDGV and button controls to myForm
8. Display the Form (myForm)
9. Wait for button picks

Challenge 1:
Since switching to a DataGridView which is linked to a DataView, which is linked to a DataTable, I cannot change the BackColor of certain cells. I want cells containing "No" to be BackColor = Yellow
See line 76 in RetrieveReferences : index out of range error when line is uncommented

Can you change cell BackColor before the DataGridView is displayed in the Form? Although disabled in the code, I can change BackColor if the Form and DataGridView when they are already displayed or.

Challenge 2:
If I display the FullFileName column, then user can select rows to open those selected documents (Open Selected button).
If I hide the FullFileName column, then errors occur when user tries to open the selected documents.
See lines 238 - 239 for the various ways I have tried to hide the FullFileName column.

ERRORS:
System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataTable'.
at FormClass.OpenSelected()
or
System.ArgumentException: Column named FullFileName cannot be found.
Parameter name: columnName

I have attached the code, since I thought it was too large to include in the message body.

To test, use any assembly with sheet metal components. For example, the Inventor Sample model named Personal Computer.iam.

Thank you for your time and attention. I look forward to your replies.

Kind regards,
Jerry

[JBerns · Accepted solution]

Success!

I was able to get the "No" cells highlighted. I had to use a DataBindingComplete Event. Excerpts of the code follows:

Public WithEvents myDGV As New DataGridView

Public Sub HighLightMissingFlatPatterns()
    If myDGV.Rows.Count > 0 Then
        For i = 0 To myDGV.Rows.Count - 1
	    Dim CellChange As String = myDGV.Rows(i).Cells("Flat Pattern").Value.ToString().Trim()
            If CellChange.Contains("No") = True Then
                With myDGV
                    .Rows(i).Cells("Flat Pattern").Style.BackColor = System.Drawing.Color.Yellow
                End With
	    Else
                With myDGV
                    .Rows(i).Cells("Flat Pattern").Style.BackColor = System.Drawing.Color.Empty
                End With
            End If
        Next
    End If
End Sub

Private Sub myDGV_DataBindingComplete(ByVal sender As Object, _
	ByVal E As DataGridViewBindingCompleteEventArgs) _
	Handles myDGV.DataBindingComplete
    ' Call procedure to highlight cells of components missing their flat pattern
    HighLightMissingFlatPatterns()
End Sub

Also, I was able to get the FullFileName column hidden (that was easy) AND still be able to open selected files. The solution was to perform a cross-reference search from myDGV to myTable.

Function BuildBaseTable() As DataTable
    ' ... Add columns.
    myTable.Columns.Add("Component", GetType(String))
    myTable.Columns.Add("Flat Pattern", GetType(String))
    myTable.Columns.Add("SM Style", GetType(String))
    myTable.Columns.Add("Inv Material", GetType(String))
    myTable.Columns.Add("Thickness", GetType(String))
    myTable.Columns.Add("FullFileName", GetType(String))
    ' ... Hide columns.
    myTable.Columns("FullFileName").ColumnMapping = MappingType.Hidden    myTable.TableName = "TableName"
    Return myTable
End Function

Public Sub OpenSelected()
    ' Initialize
    Dim SelectedRowCount As Integer = myDGV.SelectedRows.Count
    If SelectedRowCount > 0 Then
        Dim i As Integer = 0 
	For i = SelectedRowCount - 1 To 0 Step -1
	    ' Search for component name from myDGV in myTable, select if found
	    Dim SearchedTable() As DataRow = myTable.Select("Component = '" & myDGV.SelectedRows(i).Cells("Component").Value & "'")
	    ' If component name was found, then get FullFileName from myTable, open the file
	    If SearchedTable.Count > 0 Then
	        Dim SearchedValue As String
	        SearchedValue = SearchedTable(0).Item("FullFileName")
	        ' Prep to open files
	        Dim oc As Inventor.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Inventor.Application")
	        Dim oPart As PartDocument = oc.Documents.Open(SearchedValue, True)
	        ' View model from Isometric
	        SetIsoView
	    End If
	Next i
    End If
End Sub

I have attached the updated code if anyone is interested.

Thanks to all for helping to complete my first major development for Inventor!!

Regards,

Jerry

[Will.Ehrendreich]

Dude... Absolutely awesome.. I'm so glad there's a way to extend the functionality.

Has anyone figured out how to get a reference to the form that is created natively? Like I have a form that's already created in ilogic, but i want to extend what it does with a data Grid. I'm trying to find it in the object model but I fear it isn't included in the api.

anyway, fantastic work, thank you for sharing it, I'm certainly going to mine it for goodies. you rock!

[Maxim-CADman77]

@JBerns 
Worth to be marked as thread Solution, isn't it?

I wonder if it possible to:

1. Avoid hardcoding of column quantity (like "myTable.Rows.Add("", "", "", "", "", "", "", "", "")")
2. Highlight raw according to thepresence of flat pattern while keep "Flat pattern" column itself hidden (If I mark the column as hidden I then get error "Column named Flat Pattern cannot be found." )?

[JBerns]

@Maxim-CADman77,

Since I asked and then answered my own question, I was reluctant to Accept Solution. It seemed self-serving.

However, it would help others to know the question does have a solution. Other solutions, such as your suggestion, could be another possible solution. It would take a lot of recoding to test your suggestion. At this time, my code is implemented and working for our team. The code is shared above, so feel free to test. Please share your successes.

Regards,

Jerry

[Maxim-CADman77 · Accepted solution]

Regarding my question #1:

to not hardcode like

myTable.Rows.Add("", "", "", "", "", "", "", "", "")

can use

myTable.Rows.Add(myTable.Columns.Count)

[JBerns]

@Maxim-CADman77,

I misunderstood - yes, that is a great suggestion. Fewer places to manage code changes. 

Thanks,

Jerry

[Maxim-CADman77]

(sorry for a bit offtop-ing)
@matt_jlt 
This your post was one of those few that considerably changed my approach of developing own Inventor-automation.

When I just testing some ideas I hope to implement I prefer to speed-code external iLogic rule(s) using Notepad++ (rather than use Visual Studio with all those Inventor run/close cycles).

The main drawback of Notepad-Only approach for me was inability to have my own interface.

Now, after I've developed several VB.net AddIns, I need to know the best way to transfer from iLogic to Visual Studio in context of WinForms - I'm not happy to redraw the form I already have using VS-form-designer (I even don't consider it much easier).

I believe it is possible to re-use form-related code but just don't know where to start from.
Any ideas?

UPDATE:
After playing around a bit I've managed to re-compose the iLogic/VB.net code in such a way that VS now can show the form at runtime ... but it is not a complete solution - VS does not show the form Design (shows a blank form instead).

I'd like to know if there a way to convert VB.net coded form to full-featured .vb class of MS Visual Studio project?

PS:
Just in case I'm attaching two VB net codes to generate forms extracted from PDFs by WCrihfield.

[JBerns]

@Maxim-CADman77,

I have experimented with the process of designing a form in VS and then taking the code to an iLogic rule. I have had mostly success given the simple forms I use.

Due to the complex integration of multiple files in VS, I think it would be more difficult to take code from iLogic to use in VS. I am not sure how VS would recreate the form in VS Designer.

I saw this post on StackOverflow - How to rebuild WinForm with just Designer file 

Maybe it could be useful.

I look forward to your post if you are successful in your iLogic-to-VS form transfer.

Regards,

Jerry

[NachoShaw]

Why cant you create the form and all of the form objects in VS and the compile it as a dll then reference that in your iLogic code? You can pass args into the dll during the instantiate or simply send the args as function params after? Be a whole lot better than trying to get the iLogic IDE to create it for you, there is no real design in iLogic

[JBerns]

@NachoShaw,

I have tried that in the past, but struggle sending arguments/parameters between the environments.

For example, how to get the Inventor library list of sheet metal rules (styles) passed to the form so user could select.

Then, pass the selected style back to iLogic and act upon it. 

Can the form remain open or does the form have to close to see the user selection applied to a sheet metal part?

I would prefer developing the interface in VS.

Regards,

Jerry

[NachoShaw]

Hi

Typically you are going to add the reference of your dll then import it in your header, something like
'AddReference "MyForm.dll"

Imports MyForm


Then you should make a New() Instance of it
Dim MyForm As New MyForm()

If you have a New() method constructor in the class that receives params,
Dim Param1 As List(of String)
Dim Param2 As String = "Some text"
Dim MyForm As New MyForm(Param1, Param2)

or if there are public functions / methods within your class, you can call them
Dim MyForm As New MyForm()
MyForm.MyMethod(MyParam)

Typically you would pass what you want from iLogic to the class reference. If you're passing a list, you can pass the list as a List(Of String)

Your class dll should reference inventor and when instantiating, pass in the inventor document. Then use the form to do whatever you need to process the document with the settings, thats how you would update the document. Then close the form to continue etc