Alex-

I have struggled for a good month trying to figure out how to get images in from Excel. Thanks for posting that.

And thank you for your example spreadsheet. That is another way to do it. It gives me something else to think about.

Dennis

Dennis -

I agree, a direct function or wrapper would be awesome, but seems to be tricky. As others have said.

I quickly realized that the only way I could utilize the Aisc search spreadsheet was by individual single calls as you posted in your latest reply. This is far too cumbersome. So I went back to looking up an entire row in the shapes database spreadsheet.

I would love to use a more robust procedure though.

Not sure if I am grasping the root of the issue, but you can fairly easily write a wrapper as a smath user defined function using excel_IO. One would think that this way you can write a wrapper for any custom .dll for EXCEL.



All I have done was creating a simple Index(Array,Match(),Match()) lookup in EXCEL (with proper implementation of SMath units), and used excel_IO to work with it.

Seems to work very fast.

As always - please test for bugs and use at your own risk.

P.S.: Update excel plugin for proper handling of cell error values

AISC Shape Lookup.xlsx (3 MiB) downloaded 448 time(s).
Steel Properties Look-up.sm (20 KiB) downloaded 194 time(s).

Alex I appreciate your efforts with this.

I seem to be having a difficult time trying to use the "function/wrapper" inside a function.
My goal is be able to define a larger number of variables as quickly as possible. Lets assume that I need to define all the section property variables inside SMath in order my calculation to work properly. I like to establish these properties inside a function for two reasons - 1) it helps prevent variable definition overwriting. 2) I can call it many times through-out the page.

Ultimately I would set up an Smath page with multiple beam designs on the page.

Here is the condensed version of my issue:
Steel - Property Lookup Test.sm (29 KiB) downloaded 83 time(s).
AISC Shape Lookup.xlsx (2 MiB) downloaded 85 time(s).

Here is what I'm working towards:
Steel - Function - WF Design.sm (84 KiB) downloaded 110 time(s).

Ioan - First, thanks for your efforts! However for some reason when I try to open the reader file the program crashes, any thoughts on why that might be?
Next, I'm trying to understand the basic strategy for obtaining the section properties as it appears that there is a few things going on in the SMath sheets. Meaning, lets say I have a new project where I wish to use the steel properties. What files do you copy in to the project folder?
Once I get this working I should be able to figure this out.

Thanks again!

For me also, when I try to open the reader file the program crashes.

The issues are in rfile/wfile; in these functions data is shared using the user settings, and crashes when you have argument separator or decimal separator different from those used in the file (in this case, ';' is needed to load correctly the file shared by Ioan)

1) to bypass the issue, launch the worksheet in the root folder and output the fWTImp_AISC file in the "Reader" folder

2) the crash is a bug, reported as SS-2288


Happy new year to everyone

Happy New Year - 2016 - to Andrey, to All Developers and to All SMath Users !
______________________

Thanks ioan,

Same to all from Jean

Few days ago, I posted a nice WS "Season Greetings ! 2016"
in [Samples, Pattern shop].

Hey all-

с новым годом !

Felice Anno Nuovo!

Bonne Année!

Xρόνια πολλά

I am trying to catch up....

ioan, i am working on getting the files downloaded and figured out. but reading the comments, i think that the root of the issue is getting missed.

the database and spreadsheet by design is supposed to have blank vertical regions because not all shapes have all the same properties. we don't want to process it further. we really done want to decompose it and condense it. that will lead to errors and confusion.

the database and spreadsheet get regularly updated. this file in fact has been in use for 15+ years. when updates to shapes occur, these files get republished. that way, there is some predictability in accuracy in information, and repeatably.

ryan- look again at the example that Alex.M put up. I think I emailed you another example sheet when i got it figured out. all you do is program the shape and property you are looking for and it talks to excel and it spits it back to you. you don't have to worry about lookups and column numbers.

it pains me to say so, but i think that you are doing way more work than is needed in your example calc. and there is the issue of verification. by burying the calcs in hidden sections and then recalling it is hard to check and verify. are you really saving much time doing this sort of calc in SMath vs something like RAM Sbeam? for one beam design, its hard to beat.

here are some examples of how the calcs are done in excel. first picture has 2 halves. left half is what i print. right half if i need to put it in the calcs as a reminder or for review/permit. individual cells in gray on the left are user input.
the second picture, the stuff in gray is what is behind the image of the connection on the right side, and is some of the work in the background. and yes, i am little guilty of doing the same thing i pointed out to ryan as bad form, but the calc topic and result is more explicit.

File not found.File not found.

I think the best solution right now is what Alex provided us so far- giving us a pseudo-wrapper to push/pull from excel. we can simply define the shape and the property we need and move on with our calcs.

I am working on some handrail calcs right now that I am updating to the new way and will post some examples soon.

Dennis

Afternoon all,

I have been following this topic with interest over the last couple of months and to some degree agree with sinneD, the data from the spreadsheet is used within a set of calculations.

The data for a shape or serial size as we call them in Europe vary rarely gets amended - the last change occured when we started using EC3 and the axis notation altered.

EC3.zip (41 KiB) downloaded 59 time(s).

I have attached zip file containing a cut down version of what I use based on EC3 xls data file, both files must reside in same folder.

Not pretty but it works - amend the shape/section highlighted "green" to read another set of properties.

Ian

Hi All,

While I am uncertain what would the the best approach to this problem, I am dedicated to make my excel plugin work as intended.

To RFreund and SinneD:

It seems that when my plugin is called inside a programmed function it has a difficult time identifying the working directory on its own. See a work around attached (define absolute path by concatting workingdir() and xlsx filename). I might try to pinpoint the bug if I have time - use the work around for the time being.

To ioan92:

I would much appreciate if you could let me know why the plugin does not work for you - again I am dedicated to fixing it if necessary. A good starting point is to delete and redownload the plugin from extension manager - it is still work in progress.

File not found.File not found.

Hi Ioan :-)

First of all I would not want to take credit for something I have not done - it was RFeund who created the majority of the SMath workbook you were looking at. I just helped him to make it work as he intended. So I will leave it up to him to incorporate first of your two very relevant comments.

Small note though - the latest version of the plugin (0.2.0.28218) should import values with units from excel. Those units can be converted to inches as per usual SMath operation:



I will want to learn how to use combobox plugin - I beleive the worksheet you attached earlier is a good guide :-)

Thanks,

Alex M.

Wrote

I was well surprised that your plugin closes automatically the Excel files already opened. I suggest to Davide to adopt this solution also for his xlsx import plugin.

Hello Ioan, xlsx files in my plugin are opened and closed on the fly, what kind of problem have you experienced?

Hi Ioan,

The reason why excel_IO plugin connects to an already open file is because it works with EXCEL api and can connect to running instance of EXCEL. It does not quit the EXCEL process; instead it connects to it, makes it invisible, and does the calcs. Once SMath workbook is closed the plugin will make the EXCEL window visible again.

I believe Davide's plugin reads/writes xlsx files directly (unless the file is locked by EXCEL or any other process) - this is very useful for folks who either do not have EXCEL installed, or do not want SMath to rely on any dependecies (both are very valid reasons / desires).

ioan92-

i think that you are taking things out of context and reading too much from my critique. no offense or insult intended. if you want to share the holiday next year, i will be happy to raise my glass in a toast.

i was trying to point out that we have gone very far from the original question posed by ryan and i (is there any way to directly implement a search dll that gets called by an Excel VBA macro into SMAth directly). we are now solving a different problem, one that we have intentionally created by trying to work around a different one.

i think that the discussion has prompted good development and valuable discussion. I am grateful for Alex's comments because it has give me new tools with which to use within both Excel and SMath.

EDIT: regarding my admiration's- you misjudge me. I think Excel is the wrong tool for many applications, propagated by fools. The AISC database is a pain the behind, but it's the accepted consensus method. I was baptized in the river of Mathematica by none other than Theo Gray himself. Then I was forced to drink from the fountain of MathCAD. I choose to use SMath.


Excel- I really don't like Excel, but many engineers in the US (and any other math based profession) had it crammed down our throats as a tool to use because it came with PC's throughout the 90's and there are so many legacy calculations we have to maintain. As you can see from Ian's comments, we are not the only ones dependent on Excel for this sort of work. Our database gets updated everytime the code comes out. And there is the historic data one too that we have not even looked at. The format of the files are consistent between revisions.

Excel- excel seems to remain open, hidden or not. Alex described it exactly above. I often already have Excel open calculating other things already that I use to manually input into SMAth worksheet. I noticed that if SMath takes the focus from Excel doing work called up by SMath, then the other open spreadsheets get flaky really quick.

I have taken to:
-explicitly defined the directory and file that within SMath
-force Excel to be visible.

This is what I have been putting in my worksheet to be explicit for definitions. I am still trying to work through Davide's example.

Wrote

The reason why excel_IO plugin connects to an already open file is because it works with EXCEL api and can connect to running instance of EXCEL. It does not quit the EXCEL process; instead it connects to it, makes it invisible, and does the calcs. Once SMath workbook is closed the plugin will make the EXCEL window visible again.

I believe Davide's plugin reads/writes xlsx files directly (unless the file is locked by EXCEL or any other process) - this is very useful for folks who either do not have EXCEL installed, or do not want SMath to rely on any dependecies (both are very valid reasons / desires).

100% correct

Wrote

I wish you a Happy New Year !
In the image bellow it is reproduced the situation when the plugin cannot open a Excel file that is already opened

Wishes of a Happy New Year to you too!

I think I can try to bypass this issue, at least while reading. Stay tuned.

Wow, lots of action while I was away. First, thanks for everyone's contribution. I think we have already achieved a lot. I will try to summarize what I think our different objectives are.

The main goal as mentioned by Dennis was to incorporate (by some sort or hard coding in smath) the ability to select a steel shape and instantly have access to its section properties. Essentially all the user has to do is select a shape from a drop down and bang all properties are now defined or could be easily retrieved.

Alex formulated a simple function (aka wrapper) in order to input a shape's name and retrieve whichever property you would like. My two (minor) issues with this is that 1.) I generally need all the properties of the shape (mostly because I will incorporate torsion/warping into my calculation) so it was a bit tedious/ugly to have to call the function multiple times. But I did do this and it worked (thanks Alex!). 2.) So I took it one step further to hide it in a function (uh-oh, nobody likes this). I apologize as I think this has muddied the waters. I do appreciate the transparency of SMath calcs (as I think we all do), but I also like to be able to repeat a calculation on a single .sm page. Therefore the need for the function.

Ioan has created a program that succeeds in the task as well, I just need to try and fully understand it.
Alex also seems to have a work around for my function request, thank you!

So we seem to have solutions on the table, now I just need to figure out what's fastest/simplest to implement. And yet we won't be happy until we can select a shape from a drop down and have all the section properties ready to be defined into variables without having to fiddle with the excel sheet at all. <-- That sounds like the life of a software engineer :d

Again thanks to All!

Wrote

So we seem to have solutions on the table, now I just need to figure out what's fastest/simplest to implement.

Ryan, will you be willing to share the end result of your work (use-at-your-own-risk style)? I would think alot could be learned from that.

Of course. It just might take me some time... (sorry)

Hi all,

I always learn new stuff when reading the comments. Thanks to everybody.

I would consider myself a basic SMath user so I haven't quite figured out a quick way to incorporate the units into the data yet.

The following sheet is kind of slow and defining each variable might be a hassle but I think it sort of answer the need of using the combo box and having the values adjust.

AISC_Label_Prop.sm (56 KiB) downloaded 116 time(s).