Skip to main content
R
Raiko17-Peridot
17-Peridot
September 17, 2010
Question

Cycle through worksheets with Readfile

  • September 17, 2010
  • 3 replies
  • 19031 views

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

3 replies

M
MikeArmstrong1-Visitor
1-Visitor
September 18, 2010

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

M
MikeArmstrong1-Visitor
1-Visitor
September 18, 2010

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

R
Raiko17-PeridotAuthor
17-Peridot
September 20, 2010

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

M
MikeArmstrong1-Visitor
1-Visitor
September 21, 2010

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

M
MikeArmstrong1-Visitor
1-Visitor
September 22, 2010

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

R
Raiko17-PeridotAuthor
17-Peridot
September 22, 2010

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

M
MikeArmstrong1-Visitor
1-Visitor
September 22, 2010

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

Terms & ConditionsAccessibility statement