topic Embedded Spreadsheets and iLogic in Inventor Programming Forum

Embedded Spreadsheets and iLogic

meck — Fri, 14 Jan 2011 19:25:27 GMT

I have an Embedded spreadsheet in a .ipt file. I want to access the data in it using iLogic, but i keep getting this error...

GoExcel: 3rd Party worksheet not found: "3rd Party:Embedding 1", Sheet: "Sheet1"

Do I need to declare the spreadsheet somehow, or is there something else I missing?

This is the only code I have in the rule...

i = GoExcel.FindRow("3rd Party:Embedding 1", "Sheet1", "ID", "=", 1)

Thanks

Re: Embedded Spreadsheets and iLogic

Anonymous — Tue, 18 Jan 2011 04:01:05 GMT

No need to declare the embedded spreadsheet, i use embedded spreadsheets and the goexcel code frequently with no problems

Your code looks okay

Did you rename the embedded worksheet in the model browser?

What are the versions of Inventor and Excel you are using? GoExcel does not work with certain combinations (eg INV 2010 and Excel 2000)

Re: Embedded Spreadsheets and iLogic

meck — Tue, 18 Jan 2011 13:11:21 GMT

Thanks for the response!

I did not rename the spreadsheet or the worksheet. I'm using Inventor 2011 and Excel 2003.

Where is it listed what combinations work and which ones do not? I did not see that in the help file. This will be an major benefit to me if I can get this working.

Re: Embedded Spreadsheets and iLogic

MjDeck — Wed, 19 Jan 2011 02:28:14 GMT

How did you embed the spreadsheet? iLogic cannot find it if you use the Insert Object command. You have to embed it by using the Link button on the Parameters dialog.

Re: Embedded Spreadsheets and iLogic

Anonymous — Thu, 20 Jan 2011 03:17:04 GMT

Hi,

Inventor 2010 and beyond needs Excel2003 or Excel2007 so it would seem that is not the problem in your case

Re: Embedded Spreadsheets and iLogic

meck — Thu, 27 Jan 2011 20:00:03 GMT

I'm embedding the spreadsheet using insert because I do not want to have to supply a spreadsheet file along with the part file. Inserting the spreadsheet as an object has the spreadsheet as part of the .ipt and I can just give that file to whomever needs the file.

If there is another way to link a spreadsheet without the external file having to travel with the part I don't know how to do it.

Re: Embedded Spreadsheets and iLogic

MjDeck — Thu, 27 Jan 2011 20:04:57 GMT

You have to embed it by using the Link button on the Parameters dialog.

Remove your existing link. Then start the Parameters command and hit the Link button. This gives you the option to Embed instead of Link. Set the Start Cell to a blank cell that is below your data in the spreadsheet (for instance A50).

After you embed the file this way, it will show up under 3rd Party in the browser.

Re: Embedded Spreadsheets and iLogic

meck — Thu, 27 Jan 2011 20:11:44 GMT

Excellent!

Worked great!

Thank you!

Re: Embedded Spreadsheets and iLogic

Anonymous — Tue, 01 Feb 2011 18:42:05 GMT

Hello,

I am trying to accomplish something similar to what yu are doing. I want to use an embedded spreadsheet that contains rows with detailed finishing instructions. Each line item is for a different finish and may contain 10 words describing the finish in the cell. I would like to use a custom iProperty multi-value 1-24 so that the designer can choose a finish; lets say Finish "5" from the iProperties tab. Then on file save the iLogic rule would look for line item '5' and return the text in the cell next to it to a user parameter 'item_finish'. Then this 'item_finish' parameter can be used in a drawing sketched symbol to display the text. I don't seem to be able to get the text data from the speadsheet into the user parameter.

Thank you for any advice....

Mark

Re: Embedded Spreadsheets and iLogic

meck — Tue, 01 Feb 2011 22:00:10 GMT

Below is an example of how I get values from a embedded spreadsheet to a parameter.

The "C", "D", "B" ... in the code is the spreadsheet's column letter.

I hope this helps.

i = GoExcel.FindRow("3rd Party:Embedding 1", "Sheet1", "PartNo", "=", PartNumber)
Width=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "C" & i)
Length=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "D" & i)
Thickness=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "B" & i)
Angle=GoExcel.CellValue("3rd Party:Embedding 1", "Sheet1", "E" & i)


iProperties.Value("Project", "Part Number")=PartNumber

iProperties.Value("Project", "Description")="SUPPORT BAR " &  RoundToFraction(Thickness, 1/16, RoundingMethod.Round) & " X " &  RoundToFraction(Width, 1/16, RoundingMethod.Round) & " X " &  RoundToFraction(Length, 1/16, RoundingMethod.Round)

Re: Embedded Spreadsheets and iLogic

Anonymous — Wed, 26 Oct 2011 11:07:33 GMT

I have similar coding but work with no embedded Excel Sheet.

I have no Idea why the return value is always "-1" !

The excel file, sheet and column is found . The error only appears when using i to fill the parameters.

Format:HTML Format
Version:1.0
StartHTML: 165
EndHTML: 17041
StartFragment: 314
EndFragment: 17009
StartSelection: 314
EndSelection: 314

MultiValue.List("Bohrbild_Oben")=GoExcel.CellValues("SSH_rev00.xls", "Tabelle1", "W25", "W35")
i=GoExcel.FindRow("SSH_rev00.xls", "Tabelle1", "Ausf2","=",Bohrbild_Oben)
MessageBox.Show(i, Bohrbild_Oben)

' obere Platte
B1=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "L"&i)
IfPlattenanzahl=1ThenB2=B6+2*B5ElseB2=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "M"&i)
B3=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "N"&i)
B4=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "O"&i)
B5=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "P"&i)
B6=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "Q"&i)
B7=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "R"&i)
B10=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "S"&i)
Zeichnung=GoExcel.CellValue("SSH_rev00.xls", "Tabelle1", "A"&i)

'Unter Platte zum Aufschrauben auf Grundplatte 
F6=120mm
F7=150mm
F8=150mm
F9=14.5mm
F10=120mm
'Extrusion
HT=LängeHT-UPlatteDick

Can you help me , please ?

Regards / Thank you
Timo

Re: Embedded Spreadsheets and iLogic

meck — Wed, 26 Oct 2011 12:11:15 GMT

I get that all the time. It is usually from the "look-up: value not matching the format of the cell it is searching. Try adding CStr() to the value you are searching. For example...

i=GoExcel.FindRow("SSH_rev00.xls", "Tabelle1", "Ausf2","=",CStr(Bohrbild_Oben))

Re: Embedded Spreadsheets and iLogic

Anonymous — Wed, 26 Oct 2011 12:38:56 GMT

Thank you meck , However I already tired this . I also copied the row in Excel to another row and put it to TEXT format.

Format:

DimEXLTABAsString="SSH_rev01.xls"

MultiValue.List("Bohrbild_Oben")=GoExcel.CellValues(EXLTAB, "Tabelle1", "W25", "W35")

'MultiValue.SetList("Bohrbild_Oben", "B10130B6250", "B1086B60", "B10180B675", "B1075B6180")
'Bohrbild_Oben = "B10180B675"

'i = GoExcel.FindRow("3rd Party:Embedding 2", "Tabelle1", "Ausf2","=",Bohrbild_Oben)
i=GoExcel.FindRow(EXLTAB, "Tabelle1", "Auswahl","=",CStr(Bohrbild_Oben))

MessageBox.Show(i, Bohrbild_Oben)

' obere Platte
B1=GoExcel.CellValue(EXLTAB, "Tabelle1", "L"&i)
IfPlattenanzahl=1ThenB2=B6+2*B5ElseB2=GoExcel.CellValue(EXLTAB, "Tabelle1", "M"&i)
B3=GoExcel.CellValue(EXLTAB, "Tabelle1", "N"&i)
B4=GoExcel.CellValue(EXLTAB, "Tabelle1", "O"&i)
B5=GoExcel.CellValue(EXLTAB, "Tabelle1", "P"&i)
B6=GoExcel.CellValue(EXLTAB, "Tabelle1", "Q"&i)
B7=GoExcel.CellValue(EXLTAB, "Tabelle1", "R"&i)
B10=GoExcel.CellValue(EXLTAB, "Tabelle1", "S"&i)
Zeichnung=GoExcel.CellValue(EXLTAB, "Tabelle1", "A"&i)

'Unter Platte zum Aufschrauben auf Grundplatte 
F6=120mm
F7=150mm
F8=150mm
F9=14.5mm
F10=120mm
'Extrusion
HT=LängeHT-UPlatteDick

	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	B1	B2	B3	B4	B5	B6	B7	B10	BD	Hersteller	Type	Ausf2	Ausf1	Auswahl
204605705205											230	40	10	10	20	250	50	130	12	JC		B10130B6250		B10130B6250
104093560XXX											140	80	12	10	25	0	27	86	12	Borg Warner		B1086B60		B1086B60
104093560XXX											210	100	10	10	13	75	15	180	15	EB WED20		B10180B675		B10180B675
104093560XXX											100	45	12	10	15	180	13	75	12	EB WED20/A100		B1075B6180		B1075B6180

I also try to have the excel list embedded, but error was the same. Is there a bug because Office 2010 and INvento 2011 do not work together ? But then why does the multilist fill function work ?

Regards / Thank you

Timo

Re: Embedded Spreadsheets and iLogic

meck — Wed, 26 Oct 2011 13:45:01 GMT

I've had this same problem where nothing works. I wrote this code which has not failed, but is a bit slower...

I hope this helps!

Public FunctionGetExcelRow(ExcelPathAsString, SheetNameAsString, ColumnLetterAsString, MatchValueAsString)AsInteger
CellVal="Test"
i=1
DoUntilCellVal=""
 CellVal=CStr(GoExcel.CellValue(ExcelPath, SheetName, ColumnLetter&i))
 IfMatchValue=CellValThen
 GetExcelRow=i
 Exit Function
 EndIf
 i=i+1
Loop

End Function

Re: Embedded Spreadsheets and iLogic

Anonymous — Wed, 26 Oct 2011 16:03:25 GMT

Works great meck - Thank you !

Re: Embedded Spreadsheets and iLogic

serpennica — Wed, 13 Mar 2019 14:54:07 GMT

Excel Embedded, Link thru Fx worked for me. I was using the Manage (tab)/ Insert (panel) / Insert Object (button).

Insert Object does not work for iLogic.

The file came in as "Embedding 3" so excel file name is not carried forward.

When linking you can select to have file "linked" or "embed". That is the check box at bottom of screen. don't miss that.

No i can proceed to write to excel file. still working on that.