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

M
MikeArmstrong1-Visitor
1-Visitor
September 18, 2010

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

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

R
Raiko17-PeridotAuthor
17-Peridot
September 27, 2010

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

Terms & ConditionsAccessibility statement