So after some digging, I've come across how to generate a multi-value list from referencing a specific excel sheet. However, what I want to do is genetate a multi-value list from all of the sheet names contained within an excel file.
Basically... I have an excel workbook with several sheets, all with unique names (DN20 thru DN1000). I want to create a multivalue list in my parameters with each one of those sheet names as an option.
Anyone point me in the right direction?
Solved! Go to Solution.
Solved by MjDeck. Go to Solution.
Solved by dtompsett. Go to Solution.
So I wasn't able to find a direct solution to this problem. I did find a work around.
I would like to be able to pick up all the sheet names in an excel file to create a multi-list. Since this doesn't seem to be an existing piece of code, here is the work around I found.
In my excel file, I added a sheet called "size_list". On that sheet, I placed a macro I found online:
Sub ListSheets()
Dim ws As Worksheet
Dim x As Integer
x = 1
Sheets("size_list").Range("A:A").Clear
For Each ws In Worksheets
Sheets("size_list").Cells(x, 1) = ws.Name
x = x + 1
Next ws
End Sub
This creates a list in column A with all of the sheet names in individual cells. It generates in order of sheets... so the sheet "size_list" is placed at the start of the workbook, and shows up in cell A1. Then I placed a form button on the size_list sheet, which runs the macro, which allows a user to regenerate the list any time a sheet is added to the excel file.
Back in Inventor, my rule to generate the multi-list is as follows:
MultiValue.List("ring_size") = GoExcel.CellValues("excel_file_name.xlsm", "size_list", "A2", "")
Not as ideal as I'd like it to be... since you still have to run the macro in Excel to update the list (which means a user has to remember to enable macro's when they edit the workbook). But... it works.
you can try using the events of excel to auto run your macro like:
On Selection Change,
On Sheet Activate,
...
Instead of using a macro in Excel, you can get the list of sheet names in the iLogic rule. The GoExcel.Application object gives you access to the Excel objects.
tempA1 = GoExcel.CellValue("ExcelSheetList.xlsx", "size_list", "A1") wb = GoExcel.Application.ActiveWorkbook Dim sheetNames as New ArrayList For Each ws In wb.Worksheets sheetNames.Add(ws.Name) Next ws MultiValue.List("ring_size") = sheetNames