multi-value list from excel sheet names?

[Anonymous]

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? 

[Anonymous · Accepted 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.

[ACEDeSmedt]

you can try using the events of excel to auto run your macro like:

On Selection Change,

On Sheet Activate,

...

[MjDeck · Accepted solution]

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