How to Write an iLogic Rule with a Loop that Outputs a Table of Values to Excel

[Anonymous]

I would like to create an iLogic rule that contains a loop. Each time the loop runs, I want the rule to output a set of values from my part model to an Excel spreadsheet, creating a table of values.

For example, my part is the fluid inside a large bucket (not the real part because of confidentiality). I can split / slice the part with an offset plane, removing the portion of the part above the plane. I want the code to move the offset plane by some increment, record the offset distance and the volume of the part after the split to the spreadsheet (say, cells A4 and B4), then loop through this process, writing the next values into the next cells (A5 & B5) until the the offset distance exceeds the height of the bucket. At the end, it would save and close the Excel file. The table of X-Offset and Y-Volume values could be viewed, printed, charted or otherwise used from within Excel.

Using this code, I was able to create a loop that moved the plane by my desired increment. But, I could not figure out how to increment the cell addresses from one loop to the next. I ended up with just (2) values, located in the original (2) cells (A4 & B4).

i = 0
Offset = i * (1 in / 1 ul)
GoExcel.Open("Table.xlsx", "Sheet1")
String1 = "A4"
String2 = "B4"
GoExcel.CellValue("Table.xlsx", "Sheet1", String1) = Offset
GoExcel.CellValue("Table.xlsx", "Sheet1", String2) = iProperties.Volume

Do Until Offset > Height + 1
	GoExcel.CellValue("Table.xlsx", "Sheet1", String1) = Offset
	GoExcel.CellValue("Table.xlsx", "Sheet1", String2) = iProperties.Volume
	i = i + Inc
	Offset = i * (1 in   / 1 ul)
	A = Val(String1) + Inc
	String1 = Left(String1,1) & CStr(A)
	String2 = Left(String2,1) & CStr(A)
Continue Do
Exit Do
Loop

GoExcel.Save
GoExcel.Close

Any help on this would be appreciated.

Steven

[rikard.nilsson]

Hi,

If I use this code I get it to write more rows..

I added these rows to make it work

Height = 100

Inc = 1

And changed the way to read digits from String1

And removed "Continue Do"  and  "Exit Do"

i = 0
Offset = i * (1 in / 1 ul)
GoExcel.Open("Table.xlsx", "Sheet1")
String1 = "A4"
String2 = "B4"

GoExcel.CellValue(String1) = Offset
GoExcel.CellValue(String2) = iProperties.Volume
Height = 100
Inc = 1

Do Until Offset > Height + 1
	GoExcel.CellValue( String1) = Offset
	GoExcel.CellValue( String2) = iProperties.Volume
	i = i + Inc
	Offset = i * (1 in    / 1 ul)
	
	Dim myChars() As Char = String1.ToCharArray()
	Dim resultString As String = ""
	For Each ch As Char In myChars
	     If Char.IsDigit(ch) Then
	          resultString = resultString & ch
	     End If
	Next
	A = CStr(resultString) + Inc


	String1 = Left(String1,1) & CStr(A)
	String2 = Left(String2, 1) & CStr(A)

'Continue Do
'Exit Do
Loop

GoExcel.Save
GoExcel.Close

Regards

Rikard

[Anonymous]

Rikard,

Thank you for the response. Your code did help. It writes data to successive cells just like I wanted.

Unfortunately, the volume property does not update from one loop to the next. The volume written into the cell at every elevation is the full volume (after the loop is completed) instead of the incremental volume at each step. I searched additional topics for ways to force the mass properties to update and have not found anything that solves this portion of the problem.

Thank you,

Steven

[rikard.nilsson]

Hi,

I have written a code that use and update mass without any problem after moving a work plane that cuts a part. I just had an “update part” before I red the value. 

If if you could add a simple example then I can take a look. 

/Rikard

[Anonymous · Accepted solution]

Rikard,

Your solution worked. I made a couple adjustments based on the outputs I was getting in my Excel file.

1) When the plane offset was 0, Inventor was reporting the "full" volume. To correct this, I dumped the "full" volume into a new parameter value that is written into the last volume cell after the loop is finished. I also added another line to rewrite the volume at the 0 offset to be 0 gallons.

2) The iProperties.Volume value is in cubic inches. Therefore, I added some math to convert the cubic inches into gallons and rounded the figure to 1 decimal place.

3) I eliminated the "+1" at the beginning of the loop statement. This eliminated 1 loop from the sequence.

The final version of the iLogic code is pasted below.

InventorVb.DocumentUpdate()
Feature.IsActive("Split1") = True

i = 0
Parameter("Offset") = i * (1 in / 1 ul)
GoExcel.Open("Table.xlsx", "Sheet1")
String1 = "A4"
String2 = "B4"

GoExcel.CellValue(String1) = Parameter("Offset")
GoExcel.CellValue(String2) = Round(iProperties.Volume / 231, 1)
Parameter("Full") = Round(iProperties.Volume / 231, 1)

Height = H2H
Inc = 1

Do Until Parameter("Offset") > Height
        GoExcel.CellValue(String1) = Parameter("Offset")
        GoExcel.CellValue(String2) = Round(iProperties.Volume / 231, 1)
        i = i + Inc
        Parameter("Offset") = i * (1 in    / 1 ul)
        
        Dim myChars() As Char = String1.ToCharArray()
        Dim resultString As String = ""
        For Each ch As Char In myChars
             If Char.IsDigit(ch) Then
                  resultString = resultString & ch
             End If
        Next
        A = CStr(resultString) + Inc
        String1 = Left(String1,1) & CStr(A)
        String2 = Left(String2,1) & CStr(A)

        InventorVb.DocumentUpdate(False)    'required to get current mass properties
Loop

GoExcel.CellValue(String1) = Parameter("Offset")
GoExcel.CellValue(String2) = Parameter("Full")
GoExcel.CellValue("B4") = 0

GoExcel.Save
GoExcel.Close

Feature.IsActive("Split1") = False

I am very grateful for your time and expertise in helping me solve this. I will mark this topic as "Accepted Solution"

Sincerely,

Steven