Cycle through worksheets with Readfile

[Raiko]

Hello folks,

I have about 80 Excel files with each containing 24 worksheets full of data. I'd like to cycle through each Excel file's worksheets to read out the data in a loop using Readfile. For obvious reasons doing this via the import wizard is too cumbersome.

Readfile however does not offer to choose a certain worksheet within an Excel file. Has anybody an idea how to overcome this shortcoming?

Thanks in advance

Raiko

[MikeArmstrong]

Raiko Milanovic wrote:

Hello folks,

I have about 80 Excel files with each containing 24 worksheets full of data. I'd like to cycle through each Excel file's worksheets to read out the data in a loop using Readfile. For obvious reasons doing this via the import wizard is too cumbersome.

Readfile however does not offer to choose a certain worksheet within an Excel file. Has anybody an idea how to overcome this shortcoming?

Thanks in advance

Raiko

Raiko,

Can you post an example, think I've got something that will work.

Mike

[MikeArmstrong]

Please see attached worksheet.

Tom Gutman posted the sheet a while back which writes data to multiple worksheets within a Excel file. I am hoping Tom or another collab can modify the script in the textbox so data can be read from mulitple sheets instead of writing.

Mike

[MikeArmstrong]

Another example.

I have used an Embedded Excel component and specified each of the sheets when defining the data range. One problem is that M14 is only compatible with Excel 2003 - Will this be a problem?

Mike

[Raiko]

Hello Mike,

thanks for your effort.

I think I nailed the problem. In MathCad help it says, quote

"To read cells from a specific worksheet in an Excel file, use the same naming conventions as in Excel itself, for example, Sheet2!B1:C5 gets the values in cells B1 through B5 and C1 through C5 from Sheet2."

unquote.

So, in priciple and in theory it should work, but doesn't. Maybe some MC bug that prevents it from executing. I've added a simple MC11 sheet with an Excel sheet as a data source to illustrate. The Excel file can be read in easily with the input wizard but refuses to do so when using READFILE.

Raiko

[MikeArmstrong]

Raiko,

What was wrong with the example I posted?

It achieved exactly what you where asking.

Mike

[Raiko]

Sorry Mike, but I missed your post somehow.

Anyhow, there's only a textbox with a three element vector visible (to me that is). I can't even open the script of the textbox. Is it protected in some way? I'm using MC14. Please post it again or directions how to open it properly.

Regarding the READFILE: I got the following answer from PTC

quote:"...it is not possible to use the Excel notation e.g. Sheet1!A1:A3 within the READFILE arguments, it is possible to read the data in using the Data Import Wizard or the File Input option from the Insert menu. Both of these methods allow the Sheet/tab of the Excel file to be specified. The drawback is that they cannot be used in a program (to iterate through a collection of files).

However, new to Mathcad 15 is the READEXCEL function which does allow the worksheet tab to be specified, here is the excerpt from the Mathcad 15.0 Help:

READEXCEL("file", "range")

Returns a matrix from a defined range in an Excel worksheet.

WRITEEXCEL(M, "file")

Writes a matrix M to an Excel file.

Note:

These functions support Excel 2007 (Open XML) format...."unquote

So it seems as if the MC15 help erroneously slipped into MC14.

It is sad, but PTC forces one to adopt practices like the one I've attached in the screen shot: I (literally) had to use tons of file inputs each directed at a single worksheet within an Excel file. After doing that Jeans suggestion, to export first every worksheet into a single Excel file didn't sound that ludicrous anymore 😉

Raiko

[MikeArmstrong]

Raiko Milanovic wrote:

Sorry Mike, but I missed your post somehow.

Anyhow, there's only a textbox with a three element vector visible (to me that is). I can't even open the script of the textbox. Is it protected in some way? I'm using MC14. Please post it again or directions how to open it properly.

Raiko

Raiko,

It's not a textbox, it's an Embedded Excel component (There's no script involved). The only problem is that the data would have to be pasted into the component. (I meant my second example sorry. The first example needs the script modifying to read data instead of writing data to multiple files)

Can you post an Excel worksheet of typical data with several sheets included.

Mike

Message was edited by: MIke Armstrong

[Raiko]

Hi Mike,

here we go. You've been warned - this file contains 26 worksheets. 😉

Raiko

[MikeArmstrong]

Ok Raiko,

Looking at your data am I right to assume that the only values that you'll carry through to Mathcad are "X_Value" & "cDAQ1Mod2_ai10"?

If this is the case you could call all your data from all worksheets into the first sheet and then pass to Excel.

Mike

[Raiko]

Yes Mike, you're right.

That's how I did it in the beginning but then I found it less tedious to call up the single worksheets from MathCad with separate data imports.

Cheers

Raiko

[MikeArmstrong]

Raiko,

I'm not sure if I can post this hear, but hey....

You'll be glad to know that this has been rectified in Mathcad Prime, see attached image.

A very nice Implementation by PTC.

Mike

[Raiko]

Mike,

that's exactly what I heard from the PTC support. It has been rectified in MC 15.

I've tried the scripted component, but my MathCad (MC 14) refuses to execute it claiming that it doesn't support the method GetValue.

Raiko

[MikeArmstrong]

Raiko,

I have just looked at the help menu and it seems that the you can't specify worksheets when using the "READFILE" function. You can using the function I previously posted.

The help text which you posted comes under the heading "Importing/Exporting/Copying Data once from/to a File", which doesn't seem relative to the "READFILE" functions.

Mike

[f.kohlhepp]

You can do this.

My scripting skills need work, but given a starting point, I can hack.

The attached file (original courtesy of Mike Armstrong and Tom Gutman now has a readfile component that takes two inputs: a file name and a matrix of sheets and ranges, first column sheet, second column range in that sheet to be read. Mathcad reads one sheet at a time, iterates through the sheets to create a vector of readings for each sheet.

Script of TextBox: (Note that I couldn't get the iteration into the script, had to do it in MathCad.)

Sub TextBoxEvent_Exec(Inputs,Outputs)

Dim WB

Dim DATA, i

Dim Pages

Pages = Inputs(1).Value

Set WB = GetObject (inputs(0).value)

Rem For i = 0 to UBound(Pages,1)

DATA= WB.Sheets(Pages(0,0)).Range(Pages(0,1)).Value

Rem Next

Outputs(0).Value = DATA

End Sub

[Raiko]

Hi Fred,

thanks for you contribution.

my MathCad (14) doesn't execute the scripted component claiming that it doesn't know or support the method GetValue.

Any ideas how to work around this?

Raiko

[f.kohlhepp]

Raiko Milanovic wrote:

Hi Fred,

thanks for you contribution.

my MathCad (14) doesn't execute the scripted component claiming that it doesn't know or support the method GetValue.

Any ideas how to work around this?

Raiko

Sorry,

My scripting capacity has been exceeded. There are others who are better than I, I would have thought this was done by now. Have you searched the old collaboratory?

[MikeArmstrong]

Fred Kohlhepp wrote:

You can do this.

My scripting skills need work, but given a starting point, I can hack.

The attached file (original courtesy of Mike Armstrong and Tom Gutman now has a readfile component that takes two inputs: a file name and a matrix of sheets and ranges, first column sheet, second column range in that sheet to be read. Mathcad reads one sheet at a time, iterates through the sheets to create a vector of readings for each sheet.

Script of TextBox: (Note that I couldn't get the iteration into the script, had to do it in MathCad.)

Sub TextBoxEvent_Exec(Inputs,Outputs)

Dim WB

Dim DATA, i

Dim Pages

Pages = Inputs(1).Value

Set WB = GetObject (inputs(0).value)

Rem For i = 0 to UBound(Pages,1)

DATA= WB.Sheets(Pages(0,0)).Range(Pages(0,1)).Value

Rem Next

Outputs(0).Value = DATA

End Sub

Cheers Fred,

I will have a look later. Looking at the script you have produced, your scripting skills are better than mine.

Mike

[MikeArmstrong]

Fred,

That is a fantastic piece of work, well done.

Raiko,

Have a look at the attached worksheet – It reads data three different worksheets within one Excel file. For your example you could write a function to loop through each of your sheets.

I have also attached an image to prove its working on my machine

Mike

[Raiko]

Fred, Mike,

many thanks for your effort.

I still have a problem with the scripted object, as the debugger maintains that it doesn't know/support the the GetObject method.

Any suggestions?

Raiko

[MikeArmstrong]

Raiko,

Are you using Mathcad 14 & Excel 2003 formats?

Have you got the correct directory when calling in the file?

Once you have changed the directory to suit your computer,right click on the object and click edit script. Hit the tick at the top, this seems to reset the script from the last process.

I have modified the worksheet - It now succesfully calls your data.

Mike

[Raiko]

Mike,

the scripted object is wreaking havoc on my machine (Win XP SP3 with Office 2003 and MathCad 14 version M030) so I won't go further into this and go for an upgrade to MC 15 soon.

Thank you very much for all the work you put into this

Raiko

[MikeArmstrong]

the scripted object is wreaking havoc on my machine (Win XP SP3 with Office 2003 and MathCad 14 version M030) so I won't go further into this and go for an upgrade to MC 15 soon.

Thank you very much for all the work you put into this

Raiko

Raiko,

That is such a shame, the function has been developed within this thread and the only person unable to use it is you and it was your request.

I would still like to help if possible.

Have you managed to call in data from the first sheet within Excel?

Mike

[Raiko]

Mike,

in the beginning MathCad refused to execute your scripted object caliming that the Getobject method is unknown (Error 0). Your second seemed to work but than Excel opened on its own and tried to execute some macros. Regardless whether I declined or allowed macro execution Excel froze - sometimes MathCad as well. The task manager wasn't always successful in killing the application so I had to give the cord a yank.

I don't know what's going wrong but I probably will upgrade to MC 15.

Thanks again for your patience

Raiko

[MikeArmstrong]

Strange strange behavior!!!!!

Such a shame as this function is worth its weight in gold when working.

Mike