topic Re: Using a DataGridView control in VBA or iLogic in Inventor Programming Forum

Using a DataGridView control in VBA or iLogic

JBerns — Mon, 15 Jul 2019 16:56:55 GMT

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

Re: Using a DataGridView control in VBA or iLogic

pball — Mon, 15 Jul 2019 18:36:10 GMT

As far as I know VBA and iLogic do not support datagridview natively. A few years back I wondered the same thing as I was trying to make more complex scripts. I ended up moving all of my VBA scripts to my VB.net dll add-in and I'm happy with all the extras a .Net language has over the Inventor VBA/iLogic.

Re: Using a DataGridView control in VBA or iLogic

JamieVJohnson2 — Mon, 15 Jul 2019 19:00:20 GMT

You can also do a VB.Net WPF form instead of Windows Forms for a much more style managed system. I use stand alone exe files that 'grab' Inventor as needed, so they don't even have the standard Add-In limitations (or features for that matter). A standalone app has the benefit of surviving an Inventor crash, and detriment of dealing with multiple Inventor applications (there can be only 1!)

The Inventor VBA system is limited to Windows Forms 2.0 controls which is VERY OLD, you would have to use dll import commands to get controls from other platforms, and/or go through a complex dll registering system for every computer you install the code upon to make sure other users can even access your custom control. Much of these custom control installation issues have been resolved and simplified in the current .Net environment.

Re: Using a DataGridView control in VBA or iLogic

JBerns — Mon, 15 Jul 2019 20:39:20 GMT

@JamieVJohnson2 / @pball,

Creating external programs will be a new experience for me.

I have many years of experience as an AutoCAD AutoLISP developer. I have a novice knowledge level of VBA/iLogic, so this will be a big change to standalone code/apps.

Would this be considered a Plug-in?

Should I start with the tutorial, My First Inventor Plug-in Overview, or would you recommend another resource?

I appreciate the feedback.

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

JamieVJohnson2 — Mon, 15 Jul 2019 21:39:28 GMT

Get Microsoft Visual Studio (Community Edition is free with lesser debugging tools, Pro is better with way too many debugging tools! Enterprise is flat out overkill for a single user.)

Terms:

Application = .EXE

User Control Library = .DLL

External program has 2 paths:

1. Application plug in - you create the code and save it as a DLL. The code is formatted per the 'my first plug-in' tutorial in such a way then when placed where Inventor is looking, it will know what to do with it and load it as one of its normal plug ins.

2. Stand alone application - you create the code and save it as an EXE. The code has its own entry point, and uses invApp = Marshal.GetActiveObject("Inventor.Application") to hook into an active Inventor and begin to do work with it.

The plug in (1) is well scripted for you to begin, but may introduce issues getting it to work with the appropriate version of Inventor. (I recommend the Microsoft - WPF User Control Library, or the Inventor Application Plug-In template that gets installed with the Inventor SDK)

The stand alone (2) requires you to learn to create an application (I recommend using the Microsoft - WPF Application for VB.Net template in Visual Studio).

Besides WPF (Windows Presentation Foundation found in the System.Windows.Controls namespace), you can also use Windows Forms Application/User Control Library to rely on the older Windows Forms controls you may be more familiar with from VBA.

WPF is newer, and has many more features, but requires the learning curve from hell! Ok not that bad, but uses XAML (a type of XML that when written creates the VB.Net code page automatically in a hidden file *.g.vb) front end (define your controls) with the VB.Net (or C#.Net) back end (define what your controls do). I used a book Pro WPF in VB 2010 - MacDonald to learn it 9 years ago. It is much easier to use once you get the hang of it, just kind of hard to get the hang of (from a straight VBA point of view) due to its method of passing data around being a new thing; read about MVVM Model View - View Model structure (but don't get lost in it, its not that big of a deal).

Now the down side. You want to use the WPF DataGrid or WF DataGridView. The default data grid is fully functional pretty awesome free-bee, and totally basic compare to today's awesome program standards. You may find yourself expanding that datagrid's features to the point where you could 'invest' in another data grid (Telerik for one). 3rd Party DataGrids may have many features you are looking for, but you don't necessarily get control over what they do in the background code. Personally I created a custom control that inherits the standard datagrid, and adds sort/filtering controls that make it function much like the MS Access DataGrid control (which is way enhanced when you really look at it).

Re: Using a DataGridView control in VBA or iLogic

JBerns — Fri, 19 Jul 2019 13:13:23 GMT

Jamie (@JamieVJohnson2),

Thank you for the wealth of information!

I hope to explore example plugins and add-ins over the next few days.

The WindowsForm template is most familiar to me. The WPF code looks very unfamililar (for now).

At this time, I think I will stay with the tools offered by Visual Studio. I cannot justify $1000 3rd-party apps at this time based on my coding experience with VB.

Questions about the DataGridView control:

Would it be possible to sort columns interactively? By that I mean clicking a column header. Once for Ascedning. A second time for Descending. Each subsequent click toggles. That way a user could sort by 'Material' or most importantly, 'Has Flat Pattern'.
Would it be possible to add a context menu? If a user discovers a part is missing its flat pattern, it would be great if user could right-click the row and select 'Open' from a context menu. The form would close and then open the selected part. It would be impressive if user could select multiple parts that were missing their flat pattern and open all selected.
If context menus are not possible with DataGridView, perhaps instead an Open button would be enabled on the UserForm when a row or rows are selected. Maybe a button to 'Open Parts Missing Flat Pattern'.

As mentioned in the previous post, I must also be able to connect to a SQL database. The customer names would be listed on the UserForm in a ComboBox for single selection.

This is probably a big goal for a beginner with my skills, but I must start somewhere and this is the challenge presented.

Thanks again for the information and recommendations. I greatly appreciate the support offered on these forums.

Regards,
Jerry

Re: Using a DataGridView control in VBA or iLogic

pball — Fri, 19 Jul 2019 13:41:11 GMT

Sorting by clicking the column headers is a built in feature of DataGridViews, no work required for that.

Right click menus are fairly easy to setup. A ContextMenuStrip needs to be created on the form and then some code written for the MouseUp event on the DataGridView to have the ContextMenuStrip appear. From there each option in the ContextMenuStrip gets it's own subroutine. There should be plenty of examples that are easy to find. Selecting multiple rows is also possible and the code would just have to loop through the selections.

When you get more specific questions feel free to ask and someone should be able to help out. I know I have a bit of experience with DataGridView since I made an addin that shows all parts in an assembly and allows editing of properties or searching/filtering by different properties.

Re: Using a DataGridView control in VBA or iLogic

JBerns — Fri, 19 Jul 2019 14:49:26 GMT

@pball,

Thanks for the info!

That is great news on column sorting - one less thing to code.

I'll explore the options of either a ContextMenuStrip or a button to open selected part(s).

The goal is to have a button on the Inventor Assembly ribbon that the user will click. This would open the UserForm to review the sheet metal parts, assign a customer name, and then update the parts.

I think I will start by building a simple Addin. I'll see what is available on forums and the AU Online classes. Any other resource suggestions are welcome.

Thanks, all!

Regard,
Jerry

Re: Using a DataGridView control in VBA or iLogic

pball — Fri, 19 Jul 2019 15:52:17 GMT

The forum and the Mod the Machine blog are two of the major places I learned about VBA and then moving VBA into an addin. Below are links to the blog and two AU pdfs on addin coding. The VBA to Addin pdf gives a good look at how Inventor addins can be created. It also details how to mody a VBA macro so it works in VB.net.

https://modthemachine.typepad.com/

https://modthemachine.typepad.com/files/VBAtoAddIn.pdf

https://modthemachine.typepad.com/files/Upgrading%20to%20Ribbon.pdf

As for any non Inventor specific VB.net questions I find googling "VB.net datagridview right click menu" or the like to return good resources, usually vb.net focused forums or stackoverflow.

Re: Using a DataGridView control in VBA or iLogic

matt_jlt — Mon, 22 Jul 2019 04:56:25 GMT

Hi Jerry, you can definitely build forms within iLogic without using an external program / makign an addin. It is a bit more tedious and it's all written in code and no form designer but the added advantage is it is all contained within the ilogic rule. If it's a pretty simple function you can do it quite easily.

see datagrid view example below

Matt.

AddReference "System.Drawing.dll"
Imports System.Windows.Forms
Imports System.Drawing

Public Class FormClass
	Public Sub Main()
		Dim dgvMain As New DataGridView
		With dgvMain
			.Location = New Point(12, 12)
			.Size = New Size(500, 200)
			.ColumnCount = 4
			.RowCount = 5
		End With
		
		Dim myfrm As New Form
		With myfrm
			' Set up form	
			.FormBorderStyle = FormBorderStyle.FixedToolWindow
			.StartPosition = FormStartPosition.CenterScreen
			.Width = 550
			.Height = 400
			.TopMost = True
			.Text = "Custom Form 1"
			.Name = "Custom Form 1"
			
			' Add controls
			.Controls.Add(dgvMain)
		End With
		myfrm.ShowDialog()
	End Sub
End Class

Re: Using a DataGridView control in VBA or iLogic

JBerns — Mon, 22 Jul 2019 12:24:48 GMT

@pball,

Great resources! Thanks.

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

JBerns — Mon, 22 Jul 2019 12:33:55 GMT

@matt_jlt,

Being so new to VB.NET, I am still learning imports, classes, and more. Great to know that a simple form can be created without Designer or a DLL. That will make the first iteration of the program simpler.

Thank you for the DataGridView example. I will build on this to create the UserForm.

The number of rows in the table will vary with the number of sheet metal parts in the assembly, but I can get the part count before creating the grid. There should be no need to add/delete rows. Row selection and column sorting will be future features I foresee.

Thanks again, Matt!

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

jberns-newell — Mon, 22 Jul 2019 16:00:59 GMT

@matt_jlt,

The sample code you provided should allow additional controls to be added, correct?

I created a ComboBox and an OK button. When I add these controls,

			'Add controls
			.Controls.Add(dgvMain)		'Data grid
			.Controls.Add(ComboBox1)	'Select customer
			.Controls.Add(btnOK)

only the DataGridView control appears.

If I comment out the dgvMain control,

			'Add controls
'''			.Controls.Add(dgvMain)		'Data grid
			.Controls.Add(ComboBox1)	'Select customer
			.Controls.Add(btnOK)

then the ComboBox and btnOK appear as expected.

NOTICE: I trimmed the graphics to keep the image small. I believe I have the correct positions (Top, Left) defined so the controls do not overlap.

Can you or anyone offer suggestions to see all controls? I have more buttons and a textbox to add.

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

jberns-newell — Mon, 22 Jul 2019 16:06:52 GMT

DISREGARD:

I should have experimented with order before posting. Adding the DataGridView control last resolved the issue:

Can anyone explain why it has to be last?

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

JBerns — Tue, 23 Jul 2019 18:37:52 GMT

When I add the following code to the external rule:

    If ThisApplication.ActiveDocumentType <> kAssemblyDocumentObject Then
        MsgBox("Please run this routine from an assembly file.", vbCritical, "WARNING")
        Exit Sub
    End If

the following errors occur:

ERRORS

Rule Compile Errors in Prep_for_ProNest_2019-07-22_C, in Part1

Error on Line 124 : 'Color' is ambiguous, imported from the namespaces or types 'Inventor, System.Drawing'.
Error on Line 125 : 'Color' is ambiguous, imported from the namespaces or types 'Inventor, System.Drawing'.
Error on Line 131 : 'Point' is ambiguous, imported from the namespaces or types 'Inventor, System.Drawing'.
Error on Line 139 : 'Color' is ambiguous, imported from the namespaces or types 'Inventor, System.Drawing'.

These errors do not occur prior to adding the code. What could be causing the ambiguity?

Here are the References and Imports:

AddReference "System.Data"
AddReference "System.Drawing"
AddReference "System.Xml"
Imports System.Windows.Forms
Imports System.Drawing
Imports System.Data.OleDb
Imports System.Data

If I remove (comment out) any of the Add/Import lines above, more errors occur. So these are required I believe.

I need to test if the rule is running in an active assembly. If so, I then need to get the list of referenced documents. This code works when tested in VBA. Fails when run as internal rule.

I tried code from this site, Inventor Trenches - Determine file type for ilogic rule, but it too results in the ambiguous errors.

Any assistance or insight would be appreciated.

Kind regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

JamieVJohnson2 — Tue, 23 Jul 2019 19:26:06 GMT

Basically what is happening, is (for a single example lets use color) color is being defined the several imported namespaces. I don't believe the line you posted is the actual line of error, because it doesn't mention color (or the other overlapping named items). A solution would be to use imports that convert to a simpler namespace like a 3 letter abbreviation, that forces the use of the namespace, to separate the multiple "color" named objects, but shorten the namespace path with the abbreviated value.

example:

Imports Excel = Microsoft.Office.Interop.Excel
Imports SIO = System.IO

Imports SD = System.Drawing

now to find color you must go SD.Color not just Color

It can be a pain when you import multiple namespaces that have overlapping object names inside them, but it does happen in larger coded structures.

Also bear in mind many (but not all) System namespace may be 'default' in the reference and imports, so you may be competing with them.

Re: Using a DataGridView control in VBA or iLogic

JBerns — Tue, 23 Jul 2019 20:35:11 GMT

@JamieVJohnson2,

Thank you for the insight, Jamie, as to what is causing the error. I wondered if too many Imports were the issue.

I have made some progress on eliminating some errors, but have now introduced others. I will keep experimenting to find the correct text to use in the Imports and declaration statements.

Thanks,

Jerry

Re: Using a DataGridView control in VBA or iLogic

NachoShaw — Wed, 24 Jul 2019 05:00:25 GMT

Hey

You could try flexgrid. I haven't tested it with inventor but it is a Microsoft data grid control for vba. I have used it in the past with Access and there is a wealth of information surrounding its usage.

I now use a DevExpress subscription in vb.net but prior to my vb.net migration, I used flexgrid a lot. You never know, it may be the thing you are looking for 🙂

Re: Using a DataGridView control in VBA or iLogic

JBerns — Wed, 24 Jul 2019 22:22:15 GMT

@NachoShaw,

Thanks for the suggestion, but I have DataGridView (DGV) working mostly well for me at this time.

One peculiar behavior though is sorting.

After I populate the DGV, I sort the column named "Component" in ascending order.

dgvMain.Sort(dgvMain.Columns("Component"), 1)

However, the result is not correct - Part_02 is first and Part_01 is last. Sorting works correctly in Bill of Materials, so I know it is not a typo or spelling issue.

If I sort using descending order:

dgvMain.Sort(dgvMain.Columns("Component"), 0)

The order is correct with 4 being first and 1 being last.

Is there a bug in the Sort algorithm?

Regards,

Jerry

Re: Using a DataGridView control in VBA or iLogic

NachoShaw — Thu, 25 Jul 2019 05:36:43 GMT

I have seen this behavior before. AFAIK Its related incremental sorting. If for example, your numbers were 101, 102 etc it would probably sort correctly however, because they all start with a zero, it sort of organizes them like 2,3,4,5,6,7,8,9,10