Zum Inhalt springen
Home » Integrating Excel with PTC Mathcad

Integrating Excel with PTC Mathcad

    Over the years, we have had numerous conversations with engineers about which program is better for engineering calculations: PTC Mathcad or Excel. Our answer is usually, “They are both great programs, especially integrated.”

    The wonderful thing with Mathcad is that you can take advantage of both worlds. Excel spreadsheets, with full functionality, can be embedded into your Mathcad worksheets. When an Excel file is embedded within Mathcad, Mathcad will feed values to specific cells within Excel. Excel will do its processing and then Mathcad can extract results from specific Excel cells.

    This blog will focus on the power of Mathcad/Excel integration, but it will not provide specifics on how it is done. We will discuss the Excel Component, the READEXCEL and WRITEEXCEL functions.

    Please note, Excel must be installed on the computer you are using to use the Excel Component. You do not need Excel to be installed to use the READEXCEL and WRITEEXCEL functions.

    Excel Component

    The Excel Component is an Excel worksheet embedded within your Mathcad worksheet. When you double-click on the component, Excel opens, and you can create your spreadsheet as you normally would in Excel. When you close the worksheet, it is now embedded in Mathcad.

    To introduce an existing Excel spreadsheet, you must open a blank Excel Component in Mathcad and then copy all the cells from your existing Excel spreadsheet and paste them into the Excel component.

    The below example is a rather simplistic calculation of the stress in a simply supported rectangular beam. It is easy to do in Mathcad and more difficult to do in Excel, but it illustrates a few concepts, such as inputting data into Excel, the importance of understanding how units affect the input values, and how to get results out of Excel.

    Integrating Excel with PTC Mathcad

    In the following example, Mathcad is using metric units but Excel is expecting values representing feet and inches.

    Note how the Excel inputs are divided by feet and inches as in the previous example, even though metric units were input into Mathcad.

    The output is still multiplied by psi because that is what Excel produced. This value can then be displayed in metric units.

    It is CRITICAL to understand the values Excel needs in the units it is expecting. Do this by dividing the Mathcad value by these units when creating the Excel input.

    Integrating Excel with PTC Mathcad
    Integrating Excel with PTC Mathcad

    The two examples above are simple. You may create complex Excel components, but the concepts are the same.

    • Determine the cell addresses you want Mathcad to populate.
    • Know what units Excel needs, and divide the Mathcad value by the desired unit.
    • Determine the cell addresses of the Excel output you want to bring back into Mathcad and what units the data should have.
    • Attach the same units to the Excel output.

    If you have existing Excel spreadsheets, copy the Excel cells and paste them into a new Excel component. It is a good idea to protect all cells of the Excel spreadsheet except the cells needing input so that you do not accidentally overwrite a value or formula.

    READEXCEL Function

    The READEXCEL function is very useful if you have an Excel table of data that you want to bring into Mathcad. For Example, AISC has an Excel file with the steel properties of all the AISC beam sections. It has more than 70 columns and has more than 270 rows.

    The following examples show a small portion of the data from this Excel file that was brought into Mathcad. It is brought in as an array, and all data from the Shapes database now resides in Mathcad.

    Integrating Excel with PTC Mathcad

    You can also extract a column vector from the Shapes matrix. This will allow for easier data manipulation and searching.

    Integrating Excel with PTC Mathcad

    WRITEEXCEL Function

    The WRITEEXCEL function can be used to export a matrix or a series of matrices to one or more Excel files.
    Another use is if you were performing a series of runs in Mathcad and wanted to capture the inputs and results of each different run in an Excel spreadsheet.

    The below example takes the vectors Area and Label from the above example and creates a new Excel file called AISCData. It writes the Label in column A and writes the Area in column B.

    Integrating Excel with PTC Mathcad

    Note that the [1] in the function definition refers to the first sheet in Excel. If you are writing to the first sheet, this can be eliminated. Use a [2] if you want to write to the second sheet, etc.

    Here is a screenshot of the created Excel file.

    Integrating Excel with PTC Mathcad

    When to Use Each Excel Integration Option

    Let’s compare the Excel Component and the READEXCEL function and discuss when each would be appropriate.

    The Excel Component is needed if you want to use Excel to perform calculations and then have Mathcad extract the results. It allows you to take advantage of Excel functions and features. The data and numbers inside Excel are not available to Mathcad until you extract the data using the Outputs area of the Excel Component. The Excel Component does not maintain a link to any outside Excel file; it embeds and stores the Excel data inside Mathcad.

    On the other hand, when the READEXCEL function is used, all the data from the Excel file is brought into Mathcad and becomes available for use. If you need access to all the data, this is the easiest way to bring the data into Mathcad. Use this function if your Excel file is used only to store data. This function is not appropriate if your Excel file is performing calculations. If the Excel file has a cell with a function, then the result of the calculation is brought into Mathcad, not the formula. The READEXCEL function is linked to the original Excel file and must retain access to that Excel file.

    If your Excel file has a combination of calculations and data, then the Excel Component would be best to use. The data portion of the Excel spreadsheet can be brought into Mathcad from the Outputs area of the Excel Component. You will need to provide cell addresses of the upper left data cell and the lower right data cell. An example of this would be a loan amortisation schedule that uses loan rate, loan terms etc., to create the schedule. Using the Excel Component, you could provide Excel with the loan data, and then Excel would create the schedule. All the data from the schedule could then be brought into Mathcad in the Outputs area as described above.

    Using Excel within Mathcad is a great way to expand the capabilities of Mathcad. If you have existing Excel engineering calculation files, these can be used within Mathcad along with all the other benefits of Mathcad.

    Use Excel where it makes sense but do it within your Mathcad worksheet.

    P.S. This blog article was originally written as a PTC Mathcad Prime 9 worksheet. For the best reading and interactive experience, please download the Mathcad Prime worksheet hier. When you download the sheet, please unzip the file, and have the accompanying Excel spreadsheet in the same directory that you save the Mathcad Prime worksheet.

    Not using Mathcad yet?
    Calculations are at the heart of engineering. PTC Mathcad is mathematical software that lets you easily perform, analyse, document, and share your calculations.

    Get Your 30-Day Free Trial of Mathcad Prime 9 Today!

    Source: PTC