Community
Inventor Forum
Welcome to Autodesk’s Inventor Forums. Share your knowledge, ask questions, and explore popular Inventor topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Setting Multi-Value Parameter from Excel Named Range Values

9 REPLIES 9
SOLVED
Reply
Message 1 of 10
codyc-9096
2661 Views, 9 Replies

Setting Multi-Value Parameter from Excel Named Range Values

I have a Multi-Value Parameter "MV" that I want to populate using a Named Range from an Excel file that is already populating other parameters for the same file.

I currently have the file working by populating "MV" by using the snippet from iLogic below...

MultiValue.List("MV") = GoExcel.CellValues("filename.xls", "Sheet1", "A2", "A10")

The reason I don't like this method is because if ever that range changes... all the iLogic code will have to be updated as well.

 

Does anyone know how to set a Multi-Value Paramter using a Named Range from an Excel file? I assumed the below snippet would work but I am receiving "Array was not a one-dimensional array." as an error. 

MultiValue.List("MV")=GoExcel.NamedRangeValue("Named_Range_Value")

 Am I even on the right track? Any help would be greatly appreciated!

Thank You,
- Cody C
9 REPLIES 9
Message 2 of 10
xiaodong_liang
in reply to: codyc-9096

Hi,

 

NamedRangeValue returns an array. You need to extract the values of the column we are interested in.  I wrote a blog for you:

http://adndevblog.typepad.com/manufacturing/2013/11/setting-multi-value-parameter-from-excel-named-r...

Message 3 of 10
codyc-9096
in reply to: xiaodong_liang

Thank you for your reply in regards to my posted question.

 

Although the rules you described are completely new to me, from what I understand I still have to specify the number of rows to add to the ‘list’.

In doing so… if my range in Excel is currently 5 rows long and I add new rows to the range in the future I still have to open the iLogic rules and update the ‘GetLength’ accordingly, correct?

Is there any other way about doing this? What I am ultimately looking for is a way to tell iLogic that the “NamedRange” in Excel needs to populate my multivalue parameter, regardless of its length.

Thank You,
- Cody C
Message 4 of 10
QuasiMojo
in reply to: codyc-9096

Without having tried it yet, my read is that the 'rangeArray.GetLength'  extracts the number of rows from your array and then populates the values every time it's run.  If you change your array (Named Range), you would have to run this again to update it.  You could also code up the updater, too.

Message 5 of 10
xiaodong_liang
in reply to: codyc-9096

Hi,

 

>>from what I understand I still have to specify the number of rows to add to the ‘list’.

 

as I commented in the code, rangeArray.GetLength(0) means how many rows. i.e. if range in Excel has N rows, the  rangeArray.GetLength(0) will be N. In another word, code can know how many rows. So you do not need to specify the number of rows. But of course, you need to run the iLogic code to update the parameter if range changes.  What you need to make sure is there is only one column with the range, OR, which column you want to get the values. e.g. in my demo code, I extracted the first column oList.Add(rangeArray(oIndex,1)).

 

hope this explains. 

 

'rangeArray.GetLength(0) means how many rows

For oIndex = 1 To rangeArray.GetLength(0)
' add value of each row one by one
oList.Add(rangeArray(oIndex,1))
Next

Message 6 of 10
Jefkee
in reply to: xiaodong_liang

Hi,

Thanks for this rule. It works great! But..

 

How should i go about this when the range is in the other direction? 1 row , multiple columns

 

my range is:
$B$26:$D$26

 

Thanks

Inventor 2013
Message 7 of 10
xiaodong_liang
in reply to: Jefkee

Hi,
rangeArray.GetLength(0) means how many rows, while rangeArray.GetLength(1) means how many columns.

please see the updated blog

http://adndevblog.typepad.com/manufacturing/2013/11/setting-multi-value-parameter-from-excel-named-r...                             

Message 8 of 10
dkatz
in reply to: xiaodong_liang

Hi,

 

Thanks for all your posts, first. I have learned a ton by reading and trying them out.

 

I have had mixed success with this rule. The first time I run it, I get "Object reference not set to an instance of an object." error. The second time I run it, it works exactly as intended. And if you keep running it, the third one doesn't work and the fourth one does, and so on.

 

When it chokes, it is on this line:

rangeArray = GoExcel.NamedRangeValue("MyRange")

 

The only difference between your code and mine is the filename and extension (xlsm for me vs xlsx for you). I'm wondering if rangeArray needs to be defined and set to nothing each time and how one might go about that. Have you ever run into this problem?

 

Thanks,

Dave

Message 9 of 10
Timothyx.Besser
in reply to: dkatz

>>

When it chokes, it is on this line:

rangeArray = GoExcel.NamedRangeValue("MyRange")

 

Hello, 

I am currently running in to this problem now. Did you find a solution?

Message 10 of 10
dkatz
in reply to: Timothyx.Besser

This was a few years back, but I think I just quit relying on the GoExcel function, because I got a lot of errors. So, I just accessed the name through an excel application in iLogic. Turns out to be not that much more code.

 

Here's what I did:

 

SyntaxEditor Code Snippet

Sub Main


    Dim DataFullFileName As String
    DataFullFileName = "C:\Designs\Data.xlsm"
    
    excelApp = CreateObject("Excel.Application")
    excelApp.DisplayAlerts = False
    excelWorkbook = excelApp.Workbooks.Open(DataFullFileName)
    
    ImportRangeToParameter ("Pipe_Size", "AvailablePipeSizes",excelApp, excelWorkbook)

    excelWorkbook.Close (Savechanges:=False)
    excelApp.Quit
    Try    
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
    Catch
    End Try
    
    excelApp = Nothing    
    'excelApp.Visible = True
    RuleParametersOutput()
    InventorVb.DocumentUpdate()
    
End Sub

Sub ImportRangeToParameter (ParameterName As String, RangeName As String, excelApp As Object, excelWorkbook As Object)     
    
    Dim AvailableValues as New ArrayList
    Dim RepeatedValue As Boolean
    Dim i As Long
    Dim Value As Object
    
    
    For Each Value In excelApp.Range(RangeName)
        AvailableValues.add(Value.Text)
    Next
    
    MultiValue.List(ParameterName) = AvailableValues
End Sub

 

 

You'd replace the DataFileName with your location and file name, and in the Import Range to Parameter call, you'd change Pipe_Size to whatever the name of your parameter is, and AvailablePipeSizes to whatever the name of your named range is in your Data File.

 

If you have questions, let me know.

 

I think the code could be optimized or better written, but I'm just giving you what I did back then to get things working (I hadn't revisited it in some time...which should give you some idea about the stability) 

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report