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?
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.
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.
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.
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,
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.
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.
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,
Hi!
Anybody know if it's possible to import a .bas file???
WB.VBProject.VBComponents.Import ("C:\Module3.bas")
Thanks for your help!
Hi,
This seems related to this thread:
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,