Retrieve data from an Excel sheet

ptc-4089697 · ‎Apr 04, 2012

Hello.

I need some assistance in writing a small script that allows me to use a listbox object to A) populate the list with a specific column from the database and B) retrieve and manipulate other datacells in that sheet. I have poor programming skills but for a week or so I have been studying various scripted objects found here and there in this forum and I hope that I have some basic understanding in how it works now. Nevertheless I am a bit stranded with this problem.

I have enclosed the MathCAD file called 'Bolt selection' and an excel sheet with some very basic data. Here's what I want to do:

Populate the listbox with values from the A column of the excel sheet (from A2 and downwards) and assign the cursor chosen value to the output variable.
Retrieve data from other cells in the sheet (possibly in the corresponding line to the chosen row) and assigning these values to other output variables. (Has this to do with the function GetValues?)
If possible, assign the unit 'N' to the values chosen from the A column

Finally, if anyone can recommend to me a good book on VBScript so that I can learn this stuff from the start, I would be very grateful.

I appreciate any help I can get.

Thanks.

Helgi.

RichardJ · ‎Apr 04, 2012

Can the Excel worksheet be embedded in the Mathcad document as an Excel component? The solution is much easier if that is an option.

ptc-4089697 · ‎Apr 04, 2012

Fundamentally, there is nothing in the way of embedding the excel sheet in the Mathcad document as you suggest. Would the table be visible on the document, or can it be 'hidden' somewhere out of sight?

I guess I was inspired by Tom's AISC steel shapes document, where the whole thing is neatly out of sight and hidden in scripted objects. I have actually tried to taylor fit that script to my needs with some minor luck. I manage to populate the listbox with the entire data in the excel sheet, but I still don't know how to display one and only one column in the listbox.

MikeArmstrong · ‎Apr 10, 2012

The table could be hidden or displayed, entirely your choice.

Mike

ptc-4089697 · ‎May 01, 2012

Hello again.

I have made some progress having my MathCad document retrieve data from Excel data sheets.

I now have a document which has two listboxes, each reading data from a different Excel sheet.

The second listbox (Select bolt length) is supposed to populate its list depending on which line is chosen in the first listbox (Select size and pitch).

A more detailed description: The second listbox should be populated with a particular interval of values from the A column (in the Excel sheet), corresponding to the rows that are not empty in the (X) column. (The column (X) is selected based on the output of the first listbox).

Here's the funny thing: I have actually made it work like I want it to. The only problem is that the second listbox doesn't update its values if I change the selection in the first listbox. I find myself needing to 'Edit Script' for the second listbox and click on the 'Apply' button and then close the script dialog box, to force it to refresh its values.

Does anybody know what the problem is?

See attached script + Excel files

MikeArmstrong · ‎May 01, 2012

Here's the funny thing: I have actually made it work like I want it to. The only problem is that the second listbox doesn't update its values if I change the selection in the first listbox. I find myself needing to 'Edit Script' for the second listbox and click on the 'Apply' button and then close the script dialog box, to force it to refresh its values.
Does anybody know what the problem is?

This tends to happen every time a script bombs out. I have no idea why but if a script fails to run, the same procedure has to be done.

Mike

RichardJ · ‎May 01, 2012

I didn't go through your script in detail, but in the ListBoxEvent_Exec, the only place you call FillList is in this If statement:

If FName <> FileName Then

FileName = FName

FillList Inputs(2).Value

End If

When you change the selection in ListBox1 the filename does not chnage, so FName = FileName and FillList is not called.

ptc-4089697 · ‎May 01, 2012

I see. If the filename stays the same, but Inputs(2).Value changes in ListBox 2 everytime a different selection is made in Listbox 1, I simply have to make the script repopulate the list each time that happens?

How would I do that though?

I suppose it wouldn't make much sense to write:

If Inputs(2).Value <> Inputs(2).Value Then

FillList Inputs(2).Value

End If

RichardJ · ‎May 02, 2012

I suppose it wouldn't make much sense to write:
If Inputs(2).Value <> Inputs(2).Value Then
FillList Inputs(2).Value
End If

Probably not

It's really just a minor extension of what you are already doing though. You just need to check if either the filename has changed or Inputs(2).Value has changed.

ptc-4089697 · ‎May 11, 2012

Thanks Richard.

So the trick is dimensioning a variable at the top of the script and assign it to be "" and later make the script check if it is equal to the input variable and subsequently make them equal to one another?

It works beautifully now 🙂

H.

olivierlp · ‎Jan 03, 2022

Hello,

We are archiving your idea as part of a general review. This action is based on the age of your idea and the total number of votes received, as per this announcement.

You can always post a new idea with all the details required in the form.

Thank you for your participation.