Using External Rules and Global Forms for Pop-up Calculators

[RNDinov8r]

So, it's been a hot minute since I have dabbled in the iLogic. I have created a number of Forms and Rules specific to a given file template (like something we use to always create a certain type of door frame, for example). I was wondering if an external rule and a global form could be used to create a sort of Pop-up calculator. For example, we have a pretty big spread sheet that calculates SCFM of machines. But, it's a spread sheet, and you always have to go hunt it down on the server...I was wondering, is there a way to write an external rule that works in a global form, to accomplish that, so you don't have to leave the IV environment? The challenge I noticed when I started makng it, was that for the global form to contain any parameters, those parameters have to live in a file (either .ipt or .iam)...is there a way to do it, so that the form is file agnostic?

[Pineapple2024]

I'll say no.

Everything tie to a parameter, property or rule.

You can't even add buttons for "+" "-".  Unless they're iLogic rules.

And you'll want to tie them to keyboard.

It'll be better to use addin.

[RNDinov8r]

So I found what I thought was the next best thing...use the Excel data snippets to just open the file using a rule.

This is what I have. It doesn't give me any errors, but it also doesnt' open the file. 

excelApp = GoExcel.Application

GoExcel.Open("L:\Mechanical\CALCULATORS\AIR CONSUMPTION CALCULATOR.xlsx", "CALCULATOR")

[Michael.Navara]

Hi @RNDinov8r 

Function GoExcel.Open opens the excel sheet in background. Later on you can use another functions and methods for manipulating with this sheet. For example you can set value of cell and read value from another to get result. Your calculation cen be placed in this sheet.

Another approach is to convert the calculations to the external rule. It requires little bit more coding, but it can be more readable then complex excel calculations. 

[WCrihfield · Accepted solution]

Hi @RNDinov8r.  I am not sure how familiar you may be with accessing Excel related stuff by code, but the Excel application has its own API system, and we can access it much the same way we can access Inventor's API.  The iLogic 'GoExcel' rule object represents the IGoExcel Interface, which provides simpler/shortcut ways to do simple stuff with Excel, without needing to know anything about Excel's API.  Anyways, if want to use those GoExcel tools, to keep things simpler, but do need to 'see' Excel open visibly, then there is a relatively simple way to do that...at least it works OK for me.  In the Excel API, the Application object has a 'Visible' property with a Boolean value that is Read/Write, so this example is just taking advantage of that.  But working with Excel by code is finicky, and does not always work the same for everyone.  Below is a very simplistic example iLogic rule that opens an existing Excel file in the Temp folder named "Test", shows Excel visibly, then sets/changes a cell value, then pauses with a MsgBox so you can review the change, before the next change happens, then changes that cell value again, followed by another MsgBox, allowing review.  Then it closes that Excel file.  If that was the only Excel file open in any instance of Excel, it also visibly closes the Excel application.

Dim sFile As String = "C:\Temp\Test.xlsx"
Dim sSheet As String = "Sheet1"
GoExcel.Open(sFile, sSheet)
Dim oExcel As Object = GoExcel.Application
oExcel.Visible = True
GoExcel.CellValue("D3") = "45 in"
MsgBox("Review Cell D3 for '45 in' value.", , "")
GoExcel.CellValue("D3") = "12.34 in"
MsgBox("Review Cell D3 for '12.34 in' value.", , "")
oExcel = Nothing
GoExcel.Close

You will also notice that this code example does not actually 'save' those changes (using GoExcel.Save), so if you open that Excel file after running this, it has not actually changed.  If you want to keep changes, you must save after making the changes, but before closing it.

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.