Using the same WRITEEXCEL for the multiple variables

[ptc-5205351]

Hello

I wonder if it is possible to impose multiple variables to the same destination file using WriteExcel or similar function in order to export to excel.

Thank you for your attention.

[Werner_E]

I wonder if it is possible to impose multiple variables to the same destination file using WriteExcel or similar function in order to export to excel.

Not quite sure what you want to achieve. It would help if you include a worksheet to show what you are trying to achieve (and that way we also would see that you use Prime - at least I guess you still do).

If you have some vectors/matrices which you want to write in a single Excel sheet with WRITEEXCEL, there are two possible ways which come to my mind:

1) collect all variables in Mathcad in one single matrix - you may use stack and/or augment to do so and it might be necessary to fill with NaN's as the matrix will have to be rectangualar, of course. Then you could create your Excel sheet with a single call of WRITEEXCEL

2) Call WRITEEXCEL multiple times for each variable, adjusting the third parameter "range" properly so you don't overwrite data already written.

3) (Ok, yes - thats the third of the two possibilities mentioned above) in some way a combination of 1) and 2). First collect all variables as elements in one single vector and then using a loop write them using WRITEEXCEL

According to what you have in mind it might be more comfortable to use the input section of an embedded Excel component putting multiple assignments there.

[ptc-5205351]

O.K. Regarding the second method could give me an example. I do not understand very well what you mean by third range.
Thanks for your answer.

[Werner_E]

I already corrected myself

O.K. Regarding the second method could give me an example. I do not understand very well what you mean by third range.

In Mathcad 15 an below the third parameter of WRITEEXCEL would specify the sheet & cell in the Excel sheet where the writing would begin (much like the "range" parameter in READEXCEL). Unfortunately they changed that in Prime and Prime will always start at A1 in the first sheet. That way multiple successive WRITEEXCEL's are no solution as one will overwrite the date the other has already written.

[Werner_E]

No sorry - thats not a correct answer. At least not for Prime.

They have changed the working of WRITEEXCEL. The thirs parameter, which used to specify where to write the data in the Excel sheet, now has another meaning. WRITEEXCEL will always begin writing at cell A1.

So the methods 2 and 3 will not work in Prime.

I consider method 1 to be a bit cumbersome - depends upon on the type/size of the variables you want to export. If all are scalars or if all are matrices with same numer of rows or same number of columns its easy.

So probably the new Excel component will do the job best.

[ptc-5205351]

O.K. I have i little problem.
My variables don't have a same number of rows. So i can't use the function "augment".
You have be more sugestion for this case.

Thanks for attention!!!

[ptc-5205351]

Exacty what happen in my problem.That's way I try use de "Execel Component" and join all Variables there by Inputs and take all together by Outputs, but i dont know if is possible take all sheet from the Excel, bucause it give me only the range that i especify.

Thanks for your time.

[Werner_E]

Jose Francisco Anunciacação Pinto wrote:

Exacty what happen in my problem.That's way I try use de "Execel Component" and join all Variables there by Inputs and take all together by Outputs, but i dont know if is possible take all sheet from the Excel, bucause it give me only the range that i especify.

Not sure what you want to achieve. A sheet would help for sure.

You want create am Excel file from some variables in yout Mathcad sheet and then read in the whole sheet as output?

[Werner_E · Accepted solution]

So I assume you have some vectors with different length you want to be written?

You could use stack - that way creating a large vector which you then can use.

If you want the vetors side by side you will have to fill the smaller vectors up (best with NaN, I guess) so you cab use augment. You can do it manually or better write a utility routine to do that for you.

Another way could be to create a routine which adds a matrix to an existing one side by side no matter what the dimensions are. Filling non-used cells with 0 would be easier that way as filling them with NaN.

I normally don't use the Excel component (because I don't use Prime for work anyway) and so I am not sure. But wouldn't it be the easiest way to use that component as in the assigments in the Input section you can specify the range where the writing should start. So you could use multiple assigmnets for multiple variables, I guess.

[Werner_E]

Here is a hopefully handy routine in Prime which constructs the matrix from different variables. Similar to augment() but dimensions do not matter and empty cells are filled with whatever you want.

[ptc-5205351]

Thank's so much.......

It will be very useful.

[Werner_E]

You are welcome.

Here is a way to do it using the Excel comopnent. I'm anit happy with it but you have to decide yourself what fits better your needs.

[Werner_E]

For completeness sake I added is a self written routine WriteExcel() for Prime which can write to any position in the sheet, not just beginning at A1. Similar to WRITEEXCEL in Mathcad 15 and below. Don't understand why they removed and changed that feature in Prime.

Limitation of my routine is that it will only write to the first sheet of the Excel file - you cannot specify the full range including sheet name but only the starting cell in the first sheet.

I guess the routine will be rather slow on big Excel tables, as it would read the whole Excel sheet in a MC matrix, adds the data there and then write back that matrix.

As the task was done in Mathcad alone its a bit lengthy routine and still may contain errors - feel free to report them here.