Readexcel, Excel component

[SFares]

Hello,

I don't know what i am doing wrong. Please see attached files.

Thanks,

Sam

[Werner_E · Accepted solution]

Two errors:

[SFares]

Thanks a lot, Werner!

I am attaching he latest sheet.

Two follow up questions:

1. When i start adding units to the looked up values, Weight, Ag, works fine as shown below. But once Ii add a unit "in" to bf or to the rest i get an error, as shown in the second picture

2. Instead of typing the section size, is there an easy way to create a combo box of all the sections without typing in all the sizes inside the combo box

Regards,

Sam

[Werner_E]

Adding unit "in" to d fails because this vector does not consist of values alone but also contains a lot of strings like "-", starting at index 1534.

Even worse with b.f, where most values are strings, already starting at index 427.

Prime does not allow to assign a unit to a string. Basically assigning a unit means a multiplication and you can't multiply a string with a number.

So you either shorten the vector or you may omit the unit in the Excel component and use a small utility program which replaces the strings by NaNs. Assigning a unit to NaN is allowed (and sometimes even necessary).

The utility function could be used for other vectors as well, of course.

According the combo box. No, the combo box is not scriptable.

But you could use a list box from the advanced controls. Use variable "Section_Table" as Input and a script which copies the entries of the input vector to the listbox via "Listbox.addstring(...)".

A simple example is given in the attached sheet. As far as I remember more sophisticated version were posted here in the forum in the past now and then

Prime 11 sheet attached

[SFares]

i appreciate it. Thank you Werner!

I have Prime 9. Can the file be saved as Prime 9?

Best,

Sam

[Werner_E]

There is no way to save in the format of an older version. You would have to open both versions side by side and copy - but not in one go, but every region on its own - Thank you, PTC! 😞

So you see that its important to say which Prime version you are using - especially if you are not using the latest one.

According the list box - scripted advanced controls were introduced in Prime 10, so they are not available in Prime 9. 

That said I fear that there is no easy way to achieve what you asked for in Prime 9.

A combo box allows values to be copied from a vector, but copy & paste is severely broken in Prime (some may say its just implemented in an idiotic way). The combo box does not allow to copy the data from Excel directly. You may copy from a prime table, but only the portion you see on one page (select only the data, not the complete table). Once you scroll you can't expand the selection. You may copy a vector (you can select the whole vector this time), but only the data visible will be copied.

In the attached Prime 9 sheet I expanded the vector Selection_Table so it shows about 208 values and was able to copy them to the combo box. I guess it may be possible to attach data by scrolling the vector (also implemented in a very silly manner) and copying the next bunch of data. But in any case it will be very tedious to get all 2299 values to the combo box (and I don't know if the combo box has a limit of values...).

Last option would be to modify the Prime file via an external program you have to write. A Prime file is a ZIP file and once you rename it to .ZIP you can look inside and amongst others will find a file with the name worksheet.xml. This file contains all regions of your Prime sheet coded in XML format and you will also find the combo box with the 208 entries I created. You may invastigate how the format is and append all the necessary data. Save back the file, rename the zip archive to .mcdx and when you open it you may see the combo box with all the 2299 entries.

I vaguely remember that @terryhendicott  once posted that kind of solution for a similar question. I guess it was at times where no advanced controls were available.

Good luck!

[terryhendicott]

Version 9 does not have programmable combo boxes. Would do it a different way now in Version 11 using two list boxes one for shape type and other for items of the chosen shape rather than having one large list box.  Same for the metric and imperial choosing metric or imperial in one box and corresponding values in another. 

This was done for AISC v15 of tables in 2022.  Don't expect there to be much difference with version 16.

version 15 has 2092 shapes looks like version 16 has 2299 shapes?

 https://community.ptc.com/t5/Mathcad/Combo-box-input-is-a-Vector/m-p/802418#M201622

You can copy the list boxes from this sheet to another..

Ask nicely and I will update to version 16 for you?

Cheers

Terry

[Werner_E]

Thanks Terry, I knew you would jump in if I 'ping' you. I could not find the thread when using the search but I am pretty sure that the thread you reference is the exact thread I had in mind.

It's really a shame that copy & paste was implemented so poorly and incompetently in Prime and hasn't been fixed to this day.

The same goes for scrolling in larger matrices.

[SFares]

Thank you a Ton, Werner!

[SFares]

I appreciate it. Thank you so much, Terry!