Excel Components and Functions (Embedded & WRITEEXCEL)

[MikeArmstrong]

Mainly a question for Tom Gutman, but other members can chip in if they know the answer.

There are 2 functions in the attached worksheet both supplied by Tom.

The First function uses a textbox to write data to an existiting Excel worksheet. This has a benefit over the ‘WRITEEXCEL’ function which is a new function M15 and Prime, as it allows the user to specify which cells the data can be passed too, instead of always being A1.

The second function ‘Excel storage object’ passes a range to Excel using a function. The main advantage this has over the above is that there is no external file and the Excel component stays within the Mathcad Worksheet.

I have been trying to combine positives of both functions. I want the XL function to be able to take an array as shown in function 1 and display in an embedded Excel component. At the present it doesn't work quite the way I would like.

There is a description of each of the functions within the worksheet.

I think the problem is in the VB code of the embedded Excel component, but cannot seem to solve it.

Mike

[TomGutman]

Neither of your functions work on my system. The first fails when it is unable to open the (rquired existing) XL file. Besides the file not existing, there may be a problem as my version of XL doe not support the XLSX format.

The second fails, probably because the embedded XL file is a version not supported by my XL. The code looks OK. But the examples have an inconsistency between the shape of the specified XL region and the provided data. That may be your problem.

You could also adapt the code from the first one using an embedded XL object. You would remove the code to obtain the workbook from the name and use the embedded object for WB.

[MikeArmstrong]

Tom Gutman wrote:

Neither of your functions work on my system. The first fails when it is unable to open the (rquired existing) XL file. Besides the file not existing, there may be a problem as my version of XL doe not support the XLSX format.

The second fails, probably because the embedded XL file is a version not supported by my XL. The code looks OK. But the examples have an inconsistency between the shape of the specified XL region and the provided data. That may be your problem.

You could also adapt the code from the first one using an embedded XL object. You would remove the code to obtain the workbook from the name and use the embedded object for WB.

Tom,

I have attached your original sheets which where created and saved in M11 format. The Excel file can simple be created onto your desktop, as I'm sure you know.

See if these work

Mike

[TomGutman]

The first file works fine on my system, under MC14. No problem. But the shapes of the regions and the data match.

The second file just uses non-existent functions, with no definitions. I don't have (and will probably never have) MC15.

[MikeArmstrong]

Cheers for looking at the files. Not quite sure what you mean 'Mathcad 15'. Haven't got Mathcad 15 either.

I don't have the VB knowledge to combine both code's so this might be a lost cause. You posted both of these examples in the old collab so thought you where the best person to ask for help.

The XL component is a perfect tool for collecting data and displaying within a worksheet worksheet. Actually, the only valid function I have found to date.

Mike

[jeanGiraud]

DAEP [Data Analysis Extension Pack] , Mathcad 11

Just a different and versatile data WRITE/READ

jmG

[MikeArmstrong]

jean Giraud wrote:

DAEP [Data Analysis Extension Pack] , Mathcad 11

Just a different and versatile data WRITE/READ

jmG

Jean,

Another good method which could be used. I am going to try modify the XL function so it accepts a Matrix and can be wrote to specific cells with the embedded Excel sheet.

I will post when / if done.

Mike

[jeanGiraud]

What I don't understand Mike is your lost effort and mine and more from collabs to help. This "Excel" read/write with Mathcad is reccurent, dragging lasting circular like Santa Claus [just more often than Santa]. Collabs have been told not to close Excel and go away in Mathcad. Rather, they were advised for years to drag over the cells and paste table in Mathcad. Any kid would do to make it work. I have passed the image to reproduce the DAEP functionality, your thread has been visited 104 times, there are only 6 mutual collab conversations involved but not a single acknowledge . You can be sure it will come again and again and those using 15 don't report if it works for them . That monkey busines of components is useless to me. In year 1989 [just to give a date], I was still in the age of running as fast as my shadow [not faster as Lucky], waiting for Edward to scale down 25 % the last piece of Autocad 11 , Jean jumping next door and asking the old man to make me a favor ... "please print, the client is shouting BS" . The print shop had no AutoCad, no Intergraph, just printer reading *PRN. These two DAEP functions [2003, Mathcad 11] that's what they do : establish the conviviality between Excel and Mathcad. Thus no need for Excel components and any other gyzma.

To resume my visit:

1. drag the cells, copy, paste table

2. use the functions here attached

3. zap, scrap the component and else.

That it will work with all new Excel versions and the latest and future Mathcad versions, users will discover and plug as a real problem that PTC can address rather than trying to solve each individual misspractice and use of Mathcad.

[MikeArmstrong]

Jean,

File all red see attached image.

I can see the advantage of such functions, but why bother with additional Excel files when they're not needed. Once I manage to adapt both codes into one from my first post I'll have an embedded Excel component which will accept ranges.

The whole idea was to have a results table within a calculation which will updates as variables are changed.

Mike