iLogic not writing data to Excel correctly

[Anonymous]

so this blob of code is meant to write the flat area of a part to an excel file for the purpose of calculating material cost to make the part at various sizes. the code runs just fine but when I go and look at the actual Excel file, the lengths 12, 24, 36 and 48 are behaving very strangely, they keep getting the same numbers seemingly at random, like 12 and 24 will show the same flat area or 24 and 26 or 36 and 48, 12 and 36, etc. jsut those 4 and none of the others just don't seem to want to write the data properly. help? I have pasted the code here and attached the excel file.

SyntaxEditor Code Snippet

GoExcel.Open("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet1")

MessageBox.Show(SheetMetal.FlatExtentsArea, "FlatArea")



Select Case Parameter("Table_Length")
Case  Table_Length >= 12 And Table_Length < 24
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B2") = SheetMetal.FlatExtentsArea
Case Table_Length >= 24 And Table_Length < 36
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B3") = SheetMetal.FlatExtentsArea
Case Table_Length >= 36 And Table_Length < 48
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B4") = SheetMetal.FlatExtentsArea
Case Table_Length >= 48 And Table_Length < 60
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B5") = SheetMetal.FlatExtentsArea
Case Table_Length >= 60 And Table_Length < 72
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B6") = SheetMetal.FlatExtentsArea
Case Table_Length >= 72 And Table_Length < 84
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B7") = SheetMetal.FlatExtentsArea
Case Table_Length >= 84 And Table_Length < 96
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B8") = SheetMetal.FlatExtentsArea
Case Table_Length >= 96 And Table_Length < 108
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B9") = SheetMetal.FlatExtentsArea
Case Table_Length >= 108 And Table_Length < 120
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B10") = SheetMetal.FlatExtentsArea
Case Table_Length >= 120 And Table_Length < 132
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B11") = SheetMetal.FlatExtentsArea
Case Table_Length >= 132 And Table_Length < 144
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B12") = SheetMetal.FlatExtentsArea
Case Table_Length = 144
	GoExcel.CellValue("Catalog Sheet - Dbl Roll Table 1.xlsx", "Sheet3", "B13") = SheetMetal.FlatExtentsArea
End Select

GoExcel.Save
GoExcel.Close

MessageBox.Show("FlatArea Updated", "Notice:")

[Mark.Lancaster]

@Anonymous

Programming needs and issues should be address in the Inventor Customization forum for best results

https://forums.autodesk.com/t5/inventor-customization/bd-p/120

I will have the moderator relocate your posting there to better suit your needs.

[HermJan.Otterman]

hi, do you also have the part that goes with it?

[rossano_praderi]

Hi,

did you tried to add "InventorVb.DocumentUpdate()" on top of your code?

This will update the "SheetMetal.FlatExtentsArea" value before become used.

Bregs

Rossano Praderi

[HermJan.Otterman]

does this code realy work???

put a " case else " in before end select and put a messagebox in it and you wil see that your case is wrong.

(at least that happend with me)

the right way for the case , with between values would be:

SyntaxEditor Code Snippet

Select Case Table_Length
		Case 51 To 100
			MsgBox("it realy works")
			
		Case Parameter("Table_Length") >= 72 And Parameter("Table_Length") < 84
		MsgBox("this does not work!")
		
		Case Else
		MsgBox("Else 001",,"just checking")
		End Select
	

 also to write back some data I had to add a line that read the exel file like:

SyntaxEditor Code Snippet

MsgBox(GoExcel.CellValue("c:\temp\Catalog1.xlsx", "Sheet3", "A2"))

 if I did not do that, the code seemed to work, but the excel file did not change.

I hope this helps..

[rossano_praderi]

Hi,

I fully agree what Mr.Otterman has wrote, I've made some changes to your original code.

Seems to be more readable, don't you think?

For the conditional statements "Case Select..." you can refer to the follow LINK

InventorVb.DocumentUpdate()

'MessageBox.Show(SheetMetal.FlatExtentsArea, "FlatArea")
'MessageBox.Show(Table_Length, "Notice:")

Select Case Table_Length
Case 12 To 23	: cell = "B2"
Case 24 To 35	: cell = "B3"
Case 36 To 47	: cell = "B4"
Case 48 To 59	: cell = "B5"
Case 60 To 71	: cell = "B6"
Case 72 To 83	: cell = "B7"
Case 84 To 95	: cell = "B8"
Case 96 To 107	: cell = "B9"
Case 108 To 119 : cell = "B10"
Case 120 To 131 : cell = "B11"
Case 132 To 143 : cell = "B12"
Case 144	: cell = "B13"
Case Else	: cell = ""
End Select

If cell<>"" Then
  fName="3rd Party:Embedding 1"
  GoExcel.Open(fName, "Sheet1")
  GoExcel.CellValue(fName, "Sheet3", cell) = SheetMetal.FlatExtentsArea
  GoExcel.Save : GoExcel.Close

  MessageBox.Show("FlatArea Updated (cell=" & cell & ")" , "Notice:")
Else
  MessageBox.Show("FlatArea not Updated", "Notice:")
End If

Bregs

Rossano Praderi