Community
Inventor Programming - iLogic, Macros, AddIns & Apprentice
Inventor iLogic, Macros, AddIns & Apprentice Forum. Share your knowledge, ask questions, and explore popular Inventor topics related to programming, creating add-ins, macros, working with the API or creating iLogic tools.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Embedded Spreadsheets and iLogic

15 REPLIES 15
SOLVED
Back to Inventor Category
Back to Topic Listing
Reply
Message 1 of 16
meck
Collaborator
4677 Views, 15 Replies
‎01-14-2011 11:25 AM
‎01-14-2011 11:25 AM

Embedded Spreadsheets and iLogic

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

 

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
15 REPLIES 15
Message 2 of 16
Anonymous
in reply to: meck
‎01-17-2011 08:01 PM
‎01-17-2011 08:01 PM

Hi

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)

Report
0 Likes
Reply
Message 3 of 16
meck
Collaborator
in reply to: meck
‎01-18-2011 05:10 AM
‎01-18-2011 05:10 AM

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.

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
Message 4 of 16
MjDeck
Autodesk
in reply to: meck
‎01-18-2011 06:28 PM
‎01-18-2011 06:28 PM

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.

 


Mike Deck
Software Developer
Autodesk, Inc.
Report
Reply
Message 5 of 16
Anonymous
in reply to: meck
‎01-19-2011 07:17 PM
‎01-19-2011 07:17 PM

Hi,

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

Report
0 Likes
Reply
Message 6 of 16
meck
Collaborator
in reply to: meck
‎01-27-2011 12:00 PM
‎01-27-2011 12:00 PM

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.

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
Message 7 of 16
MjDeck
Autodesk
in reply to: meck
‎01-27-2011 12:04 PM
‎01-27-2011 12:04 PM

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.

 


Mike Deck
Software Developer
Autodesk, Inc.
Report
0 Likes
Reply
Message 8 of 16
meck
Collaborator
in reply to: meck
‎01-27-2011 12:11 PM
‎01-27-2011 12:11 PM

Excellent!

Worked great!

Thank you!

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
Message 9 of 16
Anonymous
in reply to: meck
‎02-01-2011 10:42 AM
‎02-01-2011 10:42 AM

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

Report
0 Likes
Reply
Message 10 of 16
meck
Collaborator
in reply to: Anonymous
‎02-01-2011 02:00 PM
‎02-01-2011 02:00 PM

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)

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
Message 11 of 16
Anonymous
in reply to: meck
‎10-26-2011 04:07 AM
‎10-26-2011 04:07 AM

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

 

Report
0 Likes
Reply
Message 12 of 16
meck
Collaborator
in reply to: Anonymous
‎10-26-2011 05:11 AM
‎10-26-2011 05:11 AM

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))

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
0 Likes
Reply
Message 13 of 16
Anonymous
in reply to: meck
‎10-26-2011 05:38 AM
‎10-26-2011 05:38 AM

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

 

 

 F1F2F3F4F5F6F7F8F9F10B1B2B3B4B5B6B7B10BDHerstellerTypeAusf2Ausf1Auswahl
204605705205          230401010202505013012JC B10130B6250 B10130B6250
104093560XXX          140801210250278612Borg Warner B1086B60 B1086B60
104093560XXX          210100101013751518015EB WED20  B10180B675 B10180B675
104093560XXX          10045121015180137512EB 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

Report
0 Likes
Reply
Message 14 of 16
meck
Collaborator
in reply to: Anonymous
‎10-26-2011 06:43 AM
‎10-26-2011 06:43 AM

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

 

Mike Eck
Master Drafter/ CAD Programmer
Using Inventor 2018
Report
Reply
Message 15 of 16
Anonymous
in reply to: meck
‎10-26-2011 09:03 AM
‎10-26-2011 09:03 AM

Works great meck - Thank you ! Smiley Very Happy

Report
0 Likes
Reply
Message 16 of 16
serpennica
Advocate
in reply to: MjDeck
‎03-13-2019 07:54 AM
‎03-13-2019 07:54 AM

 

 

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.

 

 

Link Thru Functions.JPG

Report
Reply
Reply

Forums Links

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Technology Administrators


Autodesk Design & Make Report

Report a website issue