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: 

Multi-Value Parameter from Excel Named Range Values II

6 REPLIES 6
Reply
Message 1 of 7
spackle42
678 Views, 6 Replies

Multi-Value Parameter from Excel Named Range Values II

I've been thru Xiaodong Liang's helpful blog entry on how to automatically update my multi-value parameters.  It all seems fine until I get to the second to last line where oList is written to the parameter, at which point I get the error: "Object reference not set to an instance of an object".  The full rule is below.  'part_numbers' is a named range in the spreadsheet A:A with the top row as a header (hence indexing from 2) and 'top_reel_1' is a key multivalue user parameter.

 

GoExcel.Open("3rd Party:reel_spreadsheet.xlsx", "Sheet1")
rangeArray = GoExcel.NamedRangeValue("part_numbers")
Dim oList As ArrayList
oList = New ArrayList
Dim oRowsCount
oRowsCount = rangeArray.GetLength(0)
Dim oRowIndex As Integer

For oRowIndex = 2 To oRowsCount oList.Add(rangeArray(oRowIndex,1)) Next MultiValue.List("top_reel_1") = oList GoExcel.Close


Justin Smith

Inventor Pro 2015 SP1
HP Z400 Workstation
Intel Xeon W3565 @ 3.20GHz
12Gb RAM
Win 7 64 Pro SP1
Spacepilot Pro v3.17.4
6 REPLIES 6
Message 2 of 7
pcrawley
in reply to: spackle42

That error "Object reference not set to an instance of an object" normally appears when you either try to set something that isn't declared, or when you try to put "nothing" into something that is declared.

In your case, I don't have your spreadsheet to test, but the rest of the logic & syntax makes sense.

If the user parameter top_reel_1 had not been created before you ran the code, you would have received a friendlier message like "MultiValue: Could not find a parameter named: "top_reel_1" - so my guess is that oList is possibly empty (which I doubt), or attempting to read something in the Excel spreadsheet on a line that does not exist (which I can't test)?

 

Have you tried debugging the code? If not, this trick is so valuable: http://adndevblog.typepad.com/manufacturing/2014/08/debug-ilogic.html

Peter
Message 3 of 7
spackle42
in reply to: pcrawley

Thanks Peter.  'top_reel_1' was the first thing I did before commencing on my rules.  The spreadsheet has just several test cells with only 5 rows filled. Column A is listed below:

 

xl_reel_part_number
N/A
PRD493
HRO991
test

 

Basically I had N/A, PRD493, HRO991 already manually listed and I'm trying to get it to update so 'test' is now added. I added a user parameter to output the oRowsCount value which lists it as 1048576, so it should be cycling through all of them, but no dice.



Justin Smith

Inventor Pro 2015 SP1
HP Z400 Workstation
Intel Xeon W3565 @ 3.20GHz
12Gb RAM
Win 7 64 Pro SP1
Spacepilot Pro v3.17.4
Message 4 of 7
pcrawley
in reply to: spackle42

Hi Justin - I found the problem.  The first line of your code is a mix-up of the syntax you'd use for an embedded spreadsheet and an extenal spreadsheet.

 

For an embedded spreadsheet, you would use:

     GoExcel.Open("3rd Party:Embedding 1", "Sheet1") - regardless of the name of the spreadsheet.

 

If the spreadsheet is an external one, then you would use:

    GoExcel.Open("C:\Users\Peter\Desktop\Test.xlsx", "Sheet1")

 

 So all you need to do is insert the path & name of your spreadsheet and it works.

Excel sheet and part file attached for reference.

 

If anyone is reading this post for reference, the updated code reads:

 

GoExcel.Open("C:\Users\Peter\Desktop\Test.xlsx", "Sheet1")

rangeArray = GoExcel.NamedRangeValue("part_numbers")

DimoListAsArrayList

oList = NewArrayList

DimoRowsCount

oRowsCount = rangeArray.GetLength(0)

DimoRowIndexAsInteger

 

ForoRowIndex = 2 TooRowsCount

   oList.Add(rangeArray(oRowIndex,1))

Next

MultiValue.List("top_reel_1") = oList

GoExcel.Close

 

Peter
Message 5 of 7
spackle42
in reply to: pcrawley

Thanks for taking the time on this Peter.  Your answer raises another question:  What's the difference between embedding and linking? Here's a shot of the browser and as you can see, the spreadsheet appears to be linked.  How do I change this to embedded?

 

Capture.JPG

 

EDIT: Found it all by myself! Thanks Peter



Justin Smith

Inventor Pro 2015 SP1
HP Z400 Workstation
Intel Xeon W3565 @ 3.20GHz
12Gb RAM
Win 7 64 Pro SP1
Spacepilot Pro v3.17.4
Message 6 of 7
spackle42
in reply to: pcrawley

Update 2: I've embedded the spreadsheet with the first line being:

GoExcel.Open("3rd Party:Embedding 1", "Sheet1")

but now I'm getting this error:

 

GoExcel: 3rd Party worksheet not found: "3rd Party:Embedding 1", Sheet: "Sheet1"

 

The sheet name (Sheet1) is correct and all the information I can find says that this is correct, so what am I missing?

 

Update 2: 

This just gets weirder and weirder.  I've gone through the rest of my cose and I'm able to reference the linked spreadsheet without issue, which suggests that the original issue has something to do with the MultiValue.List("top_reel_1") = oList command.  I've attached both the ipt file and the spreadsheet, so maybe someone can see where the issue is. the problem code is in set_reel_type_multi_values_rule which I've surpressed until I can work the bug out.



Justin Smith

Inventor Pro 2015 SP1
HP Z400 Workstation
Intel Xeon W3565 @ 3.20GHz
12Gb RAM
Win 7 64 Pro SP1
Spacepilot Pro v3.17.4
Message 7 of 7
pcrawley
in reply to: spackle42

Before looking at the latest bug, take a step back and have a look at what's happening here:

 

If you embed a spreadsheet, it becomes "embeded" in the part file.  So you can delete the original spreadsheet off your hard disk.  You don't need it, and Inventor isn't referencing it any more.  Once the spreadsheet is embedded, only the host part file can reference it.  At this point I'm scratching my head thinking "Why would you embed a spreadsheet to create multi-value parameters that can only be edited by opening the host part file?  Why not just use the parameters dialog box?"

 

Embedding is useful when you want to have an Excel function or output or calculation that isn't possible in Inventor's native parameter editor.  Here's a great example of where embeding works really well: http://designandmotion.net/autodesk/mfg-pages/inventor/inventor-excel/

 

Linking a spreadsheet however is another matter.  If you have a master spreadsheet that contains data that changes, but is common to many models, then Linking is the way to go.  Change the spreadsheet and all the models that you have linked will update too.

 

I think you might be digging yourself a pointless hole by making a multi-value parameter in an Excel spreadsheet that is embedded in a part file.

 

Or I've completely missed the point!

Peter

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

Post to forums  

Autodesk Design & Make Report