Community Tip - If community subscription notifications are filling up your inbox you can set up a daily digest and get all your notifications in a single email. X
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
Solved! Go to Solution.
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
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.
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.
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?
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
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
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
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