Importing Text User paramaters from excel

[paul.mcnam]

Hi all,

I'm developing a rather complex excel spreadsheet that allows me to have full control of an assembly.

This involves straight number inputs for dimensions, as well as text inputs for component colour, part numbers and descriptions.

As my research and experimenting shows, you can't directly import text from excel into a User Parameter like you can with numbers.

You need to create an iLogic rule that specifies a unique cell number in your sheet and then assign it to a manually created User Parameter.

This is time consuming but also problematic as if you update the excel sheet and the cell location moves, your text import stops working.

The only compromise I have is that I use a different input sheet where I can continually update, format, move rows and cells around etc, and then link the outputs to a fixed sheet i call "Text_Output". On that sheet i put clear instructions to not move any cells around.

Do we have any idea why this is the case? I understand that text was only added to iPart functionality around 5 years ago, so perhaps it's just something left behind that will be updated soon?

It will just be very frustrating to code the text import in all my relevant assemblies only to have this issue corrected shortly after, but i'd prefer this were sooner than later.

Is there a way to put in suggestions to Autodesk? This one seems a bit of a no brainer, but some extra noise from users may not hurt

Cheers

Paul

[MechMachineMan]

Write your program in VBA and run it from excel then?

Or connect to the excel application through your iLogic program and use the features/named ranges to find necessary cells.

[CadUser46]

I was going to say the same as Justin.  Used named ranges instead of explicit cell references.  ie Range("FaceColour") instead of Cells("B7")

I think you could even concatenate named ranges in case Excel doesn't allow you to have the same name on more than one sheet. ie FaceColour + PartName

Alternatively you could set your entire spreadsheet to text then use CInt(NamedRange) in VBA to convert the ones you expect to be numbers.

[paul.mcnam]

I suppose this would be a workaround, but it still gets a bit messy having to name all the cells and then having to write VBA.

As it stands, having column A as the name of the user input and column B with the numeric input, it's simple, works, and requires no coding to import.

I'm also trying to limit the complexity and amount of coding as for one, I have little coding expertise and similarly not many of my colleagues do, so I want this project to be easy for someone else to understand and be able to modify at a later date. Having three seperate ilogic rules, one for text import, one for colour, and one for iProperties, even though they are just one line each, is really the limit to the complexity I'm hoping to have within the files.

So yeah I'm really just wondering why we can't import text as we can numbers, I find it bizzare and was hoping someone from Autodesk could respond.

ps. a nice little trick i figured out for those who use excel and are importing text, I worked out how to ensure the ilogic rules always get triggered in your assembly, even if none of the numeric model parameters change (i.e. you can select the "any model parameter change" trigger, however if you're updating only text input, inventor doesn't know you've changed any inputs until the ilogic code for text import is actually run). What i did was I created a new numeric user parameter in excel called "Refresh_trigger" (cell A1), and have any number for it, say "0" (Cell B1). Then I added this code to This Workbook in VBAproject which adds 1 to the value in B1 each time you press save 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets(1).Range("B1").Value = Sheets(1).Range("B1").Value + 1

End Sub

I then created a sketch that uses this dimension and hid the sketch. I added the rules to the "any model parameter change" event trigger and now pressing save updates my assembly. Interestingly for the ThisDoc.Document.Rebuild() or iLogicVb.UpdateWhenDone = True, to update the whole assembly, i need to press save twice in my excel sheet for the size changes to occur.