cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X

Excel to Mathcad and Mathcad to Excel data transfer

PatriceBujard
1-Newbie

Excel to Mathcad and Mathcad to Excel data transfer

Dear all,

I got thanks a discussion chain some Mathcad macro's to read data in any worksheet in an excel file but thw woksheet name has to be known.

My data acquisition system generates automatically excel data table with every time new worksheet names.

Is it possible to obtain all the worksheet names of an excel file directly from Mathcad ?

Second question:

It is possible to read directly from Mathcad data within worksheet of Excel file and it works well.

Is it possible to do the reverse, ie to write data direclty from Mathcad in specific worksheets of an excel file ?

I have Mathcad 14 (will have mathcad 15 next year)

Thank you

P.Bujard

1 ACCEPTED SOLUTION

Accepted Solutions

The answer is yes to both.

Firstly have a look at the attached worksheet. It reads tabs within a specified Excel worksheet. Save both worksheets to your desktop before running the scripted component.

Mike

View solution in original post

26 REPLIES 26

The answer is yes to both.

Firstly have a look at the attached worksheet. It reads tabs within a specified Excel worksheet. Save both worksheets to your desktop before running the scripted component.

Mike

Thank you Mike,

As far as I remember I already got from you the macros to extract data from excel files.

Now I can read the tabs name without having to open the excel sheet, thank you because contrary to Excel I am not skilled at all to write macros in Mathcad.

The unique things which is still missing is to be able to write data from Mathcad toward a specific worksheet in Excel.

Patrice

have you got the WRITEXCEL function in M14, forget. If so, you now know the Excel tab names you can use that. Please see below for an example.

Clipboard01.jpg

Mike

Hello thank you Mike,

Yes you are right I have in the utilities which you sent the function WriteExcel(file,data).

I have tried to use it but I am always getting the an error message

"error HRESULTE_FAIL has been returned from a call to a COM component"

I don't find what I am doing wrong.

Thank you again your help

Patrice

That looks like a worksheet I have passed many times. Make sure you save the worksheet to your hardrive and that the Excel component has been created and the path is correct.

Mathcad 15 has a built in WRITEEXCEL function which I posted above. I cannot remember if 14 had. Maybe it didn't and that's why I posted the above.

Mike

I also forgot to mention that once the scripted component fails the following steps must be taken.

  • Right click and select edit script
  • Click apply (the tick sign)

For some reason this must be done every time the components fail.

Mike

Another clever worksheet you might like. This time the component creates an Excel workbook and deletes all the default tabs and lets the user name them with Mathcad and write data

Mike

Thank you Mike excellent file.

also I tried to edit the script, aplly and so on... after 5 times it works now

Patrice

No problem at all. These are good worksheets. Make sure you file them

Mike

With this 'WriteExcel' function, is it possible to write to an .XLSX file instead of an .XLS one?

I have been using the 'WriteExcel' function along with the 'READEXCEL' function built into M15 to transfer data between 2 MathCAD Sheets using an Excel workbook containing multiple sheets.

However, when I try to use the 'READEXCEL' function to read the .XLS file, it returns an error saying "File contains corrupted data", the only way I can get round it is to open the .XLS file in Excel and save it again as an .XLSX file, the 'READEXCEL' function then works.

Is there a simple solution to this problem that I am somehow missing, or is this not possible?

MikeArmstrong
5-Regular Member
(To:ptc-4304491)

With this 'WriteExcel' function, is it possible to write to an .XLSX file instead of an .XLS one?

Yes it is.

So your trying to read an Excel 2003 file from Mathcad 15?

Mike

MikeArmstrong
5-Regular Member
(To:ptc-4304491)

Have a look at the attached worksheet and see if it works for you. Remember save the worksheet to your hardrive and make sure the Excel file is present and the path (file) is correct.

Mike

That sheet is producing the same error as before. It isn't creating an XLSX file, but just an XLS file with the wrong extension. I managed to get round the problem by editing the script though, changing the line where the file is saved to:

workbook.SaveAs(Inputs(0).Value), 51

Sets the file type to XLSX

MikeArmstrong
5-Regular Member
(To:ptc-4304491)

Ok David,

At least you have found the solution

Mike

Dear all,

After looking at the script regarding data transfer from Excel to mathcad and vice-versa I adapted them to my own needs and surprisingly it works well.

One issue which I have is the VB language in the script. It is similar but not the same to the VBA for excel macro's. I don't know where to find out the VB language description for scripts in mathcad. I checked in my mathcad help but I get nothing.

Can someone tells me where I can find the VB language description for the mathcad scripts ?

Thank you

Patrice

Dear all,

After looking at the script regarding data transfer from Excel to mathcad and vice-versa, I adapted them to my own needs and surprisingly it works well.

One issue which I have is the VB language in the script. It is similar but not the same to the VBA for excel macro's. I don't know where to find out the VB language description for scripts in mathcad. I checked in my mathcad help but I get nothing.

Can someone tells me where I can find the VB language description for the mathcad scripts ?

Thank you

Patrice

Dear all,

After looking at the script regarding data transfer from Excel to mathcad and vice-versa, I adapted them to my own needs and surprisingly it works well.

One issue which I have is the VB language in the script. It is similar but not the same to the VBA for excel macro's. I don't know where to find out the VB language description for scripts in mathcad. I checked in my mathcad help but I get nothing.

Can someone tells me where I can find the VB language description for the mathcad scripts ?

Thank you

Patrice

What exactly are you after? It might have already been done.

The best way to learn IMO is to look a components already written.

Mike

Dear Mike.

I found this piece of advice very helpful and answers my needs of writing multiple output of various runs to a single excel workbook under different sheet names. However, unfortunately, I could not get it to run. Surely I am missing something!.

I am new to script.

Any help will be appreciated.

Thanks,

Hussein

Thank you Mike,

As far as I remember I already got from you the macros to extract data from excel files.

Now I can read the tabs name without having to open the excel sheet, thank you because contrary to Excel I am not skilled at all to write macros in Mathcad.

The unique things which is still missing is to be able to write data from Mathcad toward a specific worksheet in Excel.

Patrice

Dear all,

After looking at the script regarding data transfer from Excel to mathcad and vice-versa, I adapted them to my own needs and surprisingly it works well.

One issue which I have is the VB language in the script. It is similar but not the same to the VBA for excel macro's. I don't know where to find out the VB language description for scripts in mathcad. I checked in my mathcad help but I get nothing.

Can someone tells me where I can find the VB language description for the mathcad scripts ?

Thank you

Patrice

VBScript is by Microsoft, and is based on Visual Basic. Just search the net for VBScript.

Patrice have you sorted this issue?

Mike

Mike,

Yes I sorted the issue. After adapting existing scripts the data excahnge between Excel and Mathcad runs well. Mathcad cheks the existing sheet in a specific excel workbook and then delete some's or create others depending on the data to transfer.

Thank you for you help

Patrice

MikeArmstrong
5-Regular Member
(To:Youki)

No problem at all.

Mike

Dear all,

I believed I have all trasnfer between Mathcad and Excel under control but there is still an issue. How can I know from Mathcad if an excel workbook is already open and if yes close it ?

I can check if a file exits, delete it or not, check the sheet names or create new sheets and write or read any data in any sheet but I don't know how to check if the workbook is already open or not. It is important because obviously an open workbbok cannot be mofidied by a Mathcad script and it generates an error, which then force the closing of the mathcad file.

Thank you for the asnwer.

Patrice

Top Tags