Writing to multiple sheets in Excel

[dabber]

Hi All,

I'm just settling into Mathcad 15. I use Excel a lot along with Mathcad and I need to transfer data back and forth between these two applications so the ability to work with Excel 2007 is a good thing for me. The data files I read into Mathcad are usually CSV files and have a lot of parameters. I load the CSV files into Mathcad then I use Mathcad to clean up the data and perform some manipulation. The finished product is a nested matrix containing matrices of the cleaned up parameters. Each parameter is a 2 column matrix containing time and the parameter data. The reason I use a nested matrix is that each parameter has a different timebase. The problem comes when I want to output the cleaned up data from Mathcad. The only thing I can do now is write CSV files, one for each parameter but with a lot of parameters (which is usually the case) I end up with a lot of CSV files, which is inconvenient.

I want to be able to write the cleaned up parameters back to Excel but in a single file with multiple sheets each labelled with the parameter name. I've looked through the help files and can't see anything that does this. Does anybody know how I can do this?

[MikeArmstrong]

You can use a scripted component as it the attached worksheet.

Just changed the file path within the text box.

Mike

[dabber]

Thanks for the input Mike. I can't get the script to run in my mathcad although. I've attached a JPG of the error which seems to indicate that there is something wrong with the VBScript in the component. I've gone through the script, changed the file location in the text box and looked at the Developer's reference but I can't see what is causing the error.

Regards

Doug

[MikeArmstrong]

Have you created the Excel file.

I think the file has to be created first.

Mike

[dabber]

Thanks for the info Mike. I got the script to run but it didn't do what I wanted to do so I modified the script. The Excel file doesn't have to exist now - it's created in the script. I've added a bit of documentation to make it clearer. A copy of the modified script is attached.

So, for anybody who needs a routine to write data to Excel spreadsheet, here's another one. I hope it's helpful

Regards

Doug

[MikeArmstrong]

Thanks for the info Mike. I got the script to run but it didn't do what I wanted to do so I modified the script. The Excel file doesn't have to exist now - it's created in the script. I've added a bit of documentation to make it clearer. A copy of the modified script is attached.

So, for anybody who needs a routine to write data to Excel spreadsheet, here's another one. I hope it's helpful

That sounds a good improvement, but I can't seem to get the worksheet to work. Keep getting the following error.

Mike

[dabber]

I was able to duplicate the error by having duplicate names in the first column. In Excel you can't have worksheets with the same name so the script would generate an error.

Doug

[MikeArmstrong]

I was able to duplicate the error by having duplicate names in the first column. In Excel you can't have worksheets with the same name so the script would generate an error.

Right I got it to work.

It seems that data can only be passed to newly created sheets within Excel, is this correct?

Mike

[dabber]

That's right. The script creates a new worksheet for each row and labels it according to what's in the first column for that particular row.

Doug

[MikeArmstrong]

That's right. The script creates a new worksheet for each row and labels it according to what's in the first column for that particular row.

It would be nice if you could delete the unused default worksheets.

Here is a modified function which creates the Excel file, but only writes data to the standard worksheets. (sheet1,sheet2...etc)

Mike

[dabber]

You're right, getting rid of the default sheets would clean things up. I'll have a look at that

Doug

[MikeArmstrong]

I see a fundamental problem with your function.

On many occasions I need to write multiple data to one sheet. With the way your is function working the data would have to be collated into a single matrix and then passed to that worksheet within Excel. That would take a lot of time and become cumbersome.

Mike

[dabber]

You're right again Mike, The modifications I made to the script doesn't do what you are requesting. What I needed to do was to take a number of different parameters and write them to different worksheets in one workbook as part of my data extraction and repackaging process.

[dabber]

A new version of the script. This version gets rid of sheets 1,2 and 3.

A prompt response to customer input!

Doug

[MikeArmstrong]

A new version of the script. This version gets rid of sheets 1,2 and 3.

A prompt response to customer input!

Cannot get it to work.

Have you managed to get it to run?

Mike

[dabber]

This is a bit frustrating. The script works from my end. It may be that I sent the wrong file. I've attached the latest file

BTW, the script is disabled in the file. I do this because of my perverse sense of neatness.

Doug

[MikeArmstrong]

Wahooooooooo, it works.

I found the problem and it was me.

I was opening the Mathcad file and trying to change the location where the Excel worksheet was to be saved without saving the Mathcad file to my Hard drive.

Mike