Excel optimization using iLogic and solver

[Anonymous]

I am trying to optimize a drawing using Excel's solver function.  My goal is to use Excel to change several dimensions and then have Inventor return a driven dimension that will be used as a constraint for the solver.  The driven dimension constraint will be used to prevent parts from overlapping while solver finds a maximum area.  In order to do this I will need to work with a live Excel sheet.  The only way I can get Inventor to export a value to Excel is when excel is closed.  Any suggestions?

[mslosar]

I'm not saying i'm totally right, but, my understanding is that you can link a spreadsheet to your ipt or iam and change values in the spreadsheet and they'll update to inventor (have to hit the update button if the part's open). The only way to export values from inventor to excel is via export which creates a new excel file.

You may be able to write something that can take certain fields/parameters/whatever from inventor and place them into specific cells in an existing spreadsheet. I'm not that up on my excel vba/vb options, but it may be possible from the code to get the excel file to re-run it's formula's.

[Anonymous]

I would need the links to run live in order for this to work.  I have seen some discussion of using vba to automatically update Inventor/Excel with changes, but I have not been able to get any of it to work.

[mslosar]

If you're talking simply making changes in an Excel file and having them update into Inventor, that works just fine.

Just like any parameter, you can define this in a spreadsheet:

MY_PART_LENGTH 12 in

When that is linked into excel, you'll have that parameter accessible to you. In a dimension or constraint, you can simply punch in MY_PART_LENGTH and it's bound to the value in excel.

The only 'catch' is a change in the excel file isn't always immediately available. If you have nothing open, then the next time you open the file, it'll update automatically. If something is open, Inventor 'detects' the excel file has changed and highlights the Update button on the Manage tab. When you hit this button, all the changes from excel will propogate to the model.

It's been fairly simple and effective for me. I've got a few things I need some major league equations for that inventor just can't do.

[Anonymous]

Thanks, but I have gotten that much to work and it is very useful.  My ultimate goal is to run a genetic algorithm in Excel in conjunction with Inventor to optomize a process.  The Excel sheet will optomize the process while the Inventor link will make sure that everything geometrically fits into a given space.  The problem is that I need to Excel to "feed" Inventor and Inventor to "feed" Excel live without prompts.

[Vladimir.Ananyev]

You may open and modify any excel workbook using Excel API directly from your iLogic rule.

Scenario

1) open (create new) excel workbook

2) put required data into excel cells,

3) call excel solver with arguments that properly define your optimization problem,

4) read results back to iLogic rule and

5) finally change Inventor dimensions.

Then you may close excel file or repeat steps 2-5 with new input data.

Here is the sample iLogic rule that illustrates access to excel api:

AddReference "Microsoft.Office.Interop.Excel"
Imports Microsoft.Office.Interop.Excel
 
Sub Main()
         Dim oExcelApp As New Microsoft.Office.Interop.Excel.Application     
         'make Excel visible (not required - for debugging only)
         oExcelApp.Visible = True
         'opem some Excel file
         oExcelApp.workbooks.Open("C:\TEMP\DATA.xlsx")  
        
         'reference to the active workbook
         WB = oExcelApp.activeWorkbook
         'Reference to the active worksheet by its name
         WS = WB.Sheets("InputData")  
         'Reference to the specified excel cell (Range object). Row = 3, column = 2
         R = WS.Cells(3, 2)
         'do something - e.g., put some value
         R.Value = oExcelApp.WorksheetFunction.PI()
        
'        WB.Save
         oExcelApp.Quit 'close Excel
 
End Sub

 cheers,

[Anonymous]

I cannot find the the Reference "Microsoft.Office.Interp.Excel".  Any idea where this is located?  I have a lot of Microsoft references, but nothing pertaining to Excel.

[Anonymous]

Sorry, I figured out the Reference. I was thinking I need to run this sub in the VBA editor.

[Anonymous]

Vladimir - Your code is working and is sending the referenced geometries to Excel.  I still cannot run solver because Inventor requires Excel to be saved before the Inventor file is updated.  Also, the code only seems to work when Excel is closed.  If I run it when Excel is open, it opens a new Excel file and tries to save it as a copy.

[Vladimir.Ananyev]

Try this please

http://xldennis.wordpress.com/2007/11/25/access-running-instances-of-excel-in-vb/

System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") should work for you.

Cheers,

[Anonymous]

Hi!

Anybody know if it's possible to import a .bas file???

WB.VBProject.VBComponents.Import ("C:\Module3.bas") 

Thanks for your help!

[adam.nagy]

Hi,

This seems related to this thread:

http://forums.autodesk.com/t5/inventor-customization/ilogic-configuration-problem-in-inventor-2016/m...

There I provided a link to an article which shows how to import a bas file into an Excel Workbook:

http://www.excelforum.com/excel-programming-vba-macros/356856-importing-bas-files.html

Did you try it?

Did you get an error?

Cheers,