topic Re: Use Excel worksheet functions MATCH and INDEX in Ilogic in Inventor Programming Forum

Use Excel worksheet functions MATCH and INDEX in Ilogic

CAD_CAM_MAN — Fri, 26 Feb 2016 17:37:31 GMT

Inventor 2015...

I am writing an Ilogic program that accesses data from an Excel workbook.

I would like to use the "MATCH" and "INDEX" worksheet functions in the Ilogic code however I am not sure if they are supported. I have used similar code with ".WorksheetFunction" in VB.net but can't seem to get it to work in Ilogic. I get the following message...

"Unable to get the Match property of the WorksheetFunction class"

Code as follows...

TEST = GoExcel.Application.WorksheetFunction.Match(THREAD_DIAMETER,GoExcel.CellValues("A1","A1000"),0)

To start I want to find the cell that holds the current value of the THREAD_DIAMETER parameter. This only uses the Match function as the portion where I need the Index function is built on the return of the match.

Is it possible to use the Match and Index functions in Ilogic? If yes what am I missing? Can you provide a sample syntax?

Re: Use Excel worksheet functions MATCH and INDEX in Ilogic

HermJan.Otterman — Sat, 27 Feb 2016 17:30:25 GMT

you can use the first defaut snippet from the excel data links in iLogic:

i = GoExcel.FindRow("filename.xls", "Sheet1", "columnName", "<=", 0.2, "columnName", "<=", 4.1)

index = GoExcel.FindRow("YourExcelfilename.xls", "Sheet1", "A", "=", THREAD_DIAMETER)

it will look for the Value of "Thread_Diameter" in Column "A" on "Sheet1" in YourExcelFile

Re: Use Excel worksheet functions MATCH and INDEX in Ilogic

Owner2229 — Mon, 29 Feb 2016 05:59:17 GMT

Hi, here is how to use the ExcelApp instead of GoExcel. It's similiar to VB.Net.

You might want to try these functions with this.

excelApp = CreateObject("Excel.Application")
excelApp.Visible = False
excelApp.DisplayAlerts = false
excelWorkbook = excelApp.Workbooks.Open(Filename: = "C:\Path\MyExcelTable.xlsx")
excelWorkbook.Save
excelWorkbook.Close
excelApp.Quit
excelApp = Nothing