Query and select from excel file

[ptc-1643766]

I have an excel file that contains material property data.  I have several sheets in the file; Aluminum, Titanium, Steel etc.   Each sheet contains data for several alloys, one for each row.

In Mathcad Prime 7, I would like to be able to select a material of choice.  I thought of having a combi box that selected what type (Aluminum, Steel etc), then a 2nd combi box that would show a list of materials based on the type and allow the user to select the material from that list.  Once the user selects, I would like to load the material property data for that specific material.  It is important that once the Mathcad file is saved and closed that it retains the selections that were in the combi box.

Is this possible?  Maybe there is a better way...

[terryhendicott]

Hi

Can you supply the EXCEL workbook if not propriety so I can try with Prime to show how it can be done?

Cheers Terry

[ptc-1643766]

Thanks Terry.  See attached.

Scott

[terryhendicott]

Hi,

This is not elegant but it works.

Changed your material file.  Have added a title row to the top where missing on one of the sheets.  Have changed the name of the EXCEL file so it is date stamped.

You will need to set the path to the EXCEL file to suit your system.  Printed a PDF so you can see it working.

You can index individual properties out of the final derived matrix.  Have not done this, left it for you.

You simply select two index numbers, feedback is given on the selections.  The sheet when saved keeps the selections.

Cheers

Terry

[ptc-1643766]

Terry,

Thank you.  That helped a lot.  However, I another question.

I would like to automatically interrogate the excel file to determine how many material rows for each type.  This will make it easy to add more materials in the future and not have to update each template that calls the excel file.

Scott

[terryhendicott]

Hi,

Have transferred the row data to the EXCEL file on a sheet called "Row Data"

You can add rows to the data in EXCEL as long as you also update the row data sheet.

Mathcad now reads the new row data at the start.

Cheers

Terry

[ptc-1643766 · Accepted solution]

Terry, that worked great.  Thanks!

I have a couple other things I can't find answers to.

1) Can I hide the collapsed region line from the print or save as pdf?

2)  Is there a way to show a variable's definition without the "" and without seeing the variable name.  Example:  I want to put the name of the material that was extracted from the excel sheet into the Header show it shows on each page like a title block but I don't want to see Material = "4340 Steel", I just want to see 4340 Steel.

Thanks again

[terryhendicott]

Hi,

1) Can I hide the collapsed region line from the print or save as pdf?

On the Document | Regions | Area ribbon you can put an area in the document.  You can collapse these areas using the little icon in their upper left corner.  They will then not print fully but print as a line in the document.

Put any calculations you do not want to see in one of these areas.

2)  Is there a way to show a variable's definition without the "" and without seeing the variable name.  Example:  I want to put the name of the material that was extracted from the excel sheet into the Header show it shows on each page like a title block but I don't want to see Material = "4340 Steel", I just want to see 4340 Steel.

Only way I know is to cut and paste the text into the header.  In the worksheet you can only select the text with quotation marks.  You need then to remove the quotation marks.  Do this via a great text processor application like "EditPad Classic" that is postcard software.  Copy from the text processor into a text box in the header.

Document | Headers and Footers | Header or double click in the worksheet in the header location.  Insert a Text Box using Documents | Regions Text Box and paste the text without quotation marks.

Cheers

Terry

Cheers

Terry

[LucMeekes]

Any content that you put (far enough) to the right of the right page border in Prime does not get printed.

That way, to hide anything in print-outs you don't need a (collapsed) region that will still show a horizontal line on prints.

Success!
Luc