Exporting a matrix to Excel with VBA

[DFrancke]

Hello,

I would like to export a complete matrix from mathcad prime 8.0 to an existing Excel file and in a specified range of cells. While reading through some older posts on this topic, I got the feeling this shouldn't be too hard. I am, however, completely new to VBA. For now i have the following lines:

Sub ExportMatrixToExcel()
Dim MCApp As Ptc_MathcadPrime_Automation.Application
Dim WS As Ptc_MathcadPrime_Automation.Worksheet

Set MCApp = New Ptc_MathcadPrime_Automation.Application
MCApp.Visible = True

MCApp.Open ("E:\Mathcad\test.mcdx")

From here i would like to export the matrix in the mathcad file to my Excel file. The dimension of the matrix is 5x5 and let's say i want to place this matrix in Excel starting at C5 and ending at G9. 

Is somebody willing to help me with this with a general example?

Thanks in advance

[LucMeekes]

Why not use the Excel interface functions of Prime?

Success!

Luc

[DFrancke]

Hi Luc,

I gave this a try before considering VBA. The thing is that the "WRITEEXCEL" function can only write data to a new file, which also only seems te be active for a specific Mathcad worksheet right? This would not be possible for me since I need to export data I calculated to an existing Excel file of my client.

[LucMeekes · Accepted solution]

WRITEEXCEL writes data to an excel file. If it doesn't exist, it creates it. If it exists, it overwrites existing data.

Some remarks:

- It doesn't seem to be able to create new sheets in the Excel workbook, you're limited to 3 sheets when the file is created by Prime.

- the function of the arguments 'rows' and 'cols' is unclear.

Success!
Luc

[DFrancke]

Thanks for the reply! I did not know this function could overwrite existing data, so this could be an outcome.

Mathcad is able to read my existing Excel file, but when i want to overwrite data using the WRITEEXCEL function i get the error: a call target threw an exception. The problem seems to be the matrix. Did I do something wrong here?

[LucMeekes]

The file must NOT be open (in use) by Excel itself at the same time.

As said, I don't understand the function of the parameters cols and rows (the two ones) in the function call, is not described in the info about WriteExcel. What happens if you leave that out?

Good luck!

Luc

Translated by the Community Moderation using Google Translate
-----------------------------------------------------------------------------------------------

De file moet NIET tegelijk open (in gebruik) zijn door Excel zelf.

Zoals gezegd, ik snap de functie van de parameters cols en rows ( de twee enen) in de functie-aanroep niet, is niet beschreven in de info over WriteExcel. Wat gebeurt er als je die weglaat?

Succes!

Luc

[DFrancke]

The problem was caused because my Excel is set to Dutch. "Sheet1!" apparently had to be replaced by "Sheet1!". Now it is possible to write the data to an existing file.

Thanks for the tips and the effort Luc!

Translated by the Community Moderation using Google Translate
-----------------------------------------------------------------------------------------------

Het probleem werd veroorzaakt doordat mijn Excel ingesteld staat in Nederlands. "Sheet1!" moest blijkbaar vervangen worden door "Blad1!". Nu lukt het wel om de data naar een bestaande file te schrijven. 

Bedankt voor de tips en de moeite Luc!

[LucMeekes]

Don't mention it.

Maybe you can mark this item as solved..

Good luck!

Luc

Translated by the Community Moderation using Google Translate
-----------------------------------------------------------------------------------------------

Graag gedaan.

Misschien kun je dit item als opgelost markeren ..

Succes!

Luc