Link to home
Start Free TrialLog in
Avatar of excel learner
excel learnerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Macro to extract data from a range within a sheet name from several files in a folder

Dear experts,

I have a folder on my laptop/Desktop, which has about 20 excel files in both 2003 and 2007 version. Each file has several sheets, but consistently named. Within each sheet there are several data arrays.

What i need is a macro which will do the following


1.      Go the folder and select each sheet at a time.
a.      The macro should have a location where i can feed the path
2.      When it selects the first file, the macro should seek a sheet
a.      The macro should have a location where i can feed the sheet name
3.      When it selects the target sheet, the macro should seek a range
4.      Then copy the range on to a new sheet.
5.       Copy the name of the sheet in row 1 on all the column the data is being copied. For instance if data is being copied in column A B and C then the name of the source file should be reflected in cell A1, B1 and C1. the data will be copied from row 2 onwards.
6.      Close the current file from where the data has been copied
7.      Then open the next file and the repeat the steps 2 to 5,
8.      But macro should copy the range data adjacent to the range copied from file 1 and so on
9.      For example if we have copied data on the new sheet in column A:b, then the data from second file should be copied in range C;D and so on.

I should be able to change the range details of the data to be copied from the files. In other words, i need to be able to change the range details in the macro so that i can use it several times.


Thank you,
ASKER CERTIFIED SOLUTION
Avatar of Rob Brockett
Rob Brockett
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi
Here's a little tool I have which does something similar to what you're asking for.  Have a go with this and see whether it is good enough.  If not, let me know what changes you need to it.

Instructions:
- enter the path for the folder you want to process in A1
- enter the range names that you want to read in B1:IV1
- press the churn button
- each workbook gets one row, and the flename of the worksheet goes in column A
Book10.xls
Avatar of excel learner

ASKER

Jan24,

thank you for the comment.

I do not have range name, rather i want to range like 'sheet1'!b2:c6.

Kindly let me know if the vba could be amended to incorporate the above requirement.

thank you,
Here it is again set up to do 'sheet1'!b2:c6.  Try it and let me know if it works!
Book10a.xls
hi Excel Learner,

Have you looked the link I previously posted?
It is a soundly developed & tested addin by a well known excel wizz and I believe the screenshot on the page shows that it will do exactly what you have asked in your last post (# 36716169).

Jan24, I glanced through your code and I think the OP wants the layout to be transposed, relative to how your code currently makes it, esp when reading through the initial points # 5 to #9.
I'm curious, why do you use ".evaluate" rather than writing directly to the cell (or cells, for more efficiency - ie minimise "the hits on the spreadsheet")?

Rob
The .Evaluate was so that it was flexible enough to use cell references, range names or local range names, or formulae that generate a range.
Ahh, that makes sense - thanks, you've taught me a new trick :-)
I had read in the past that Evaluate is flexible, but I didn't know what made it flexible - now I do!
It certainly looks like it could be useful in other situations too & makes for shorter code than we'd otherwise get if we were to test the input for each possible option using Typename.

thanks
Rob
Jan 24,

I have put the de4tails, when i clicked the button, the macro did not activate there was no error message either.

Is this macro very difficult. I have been waiting for four days now.

Thankyou
hi Excel Learner,

I have been waiting for a few days too...

Have you looked the link I previously posted?
http://www.rondebruin.nl/merge.htm

This is a FREE soundly developed & tested addin by a well known excel wizz (a certified "Microsoft Most Valued Professional") and I believe the screenshot on the page shows that it will do exactly what you have asked in your earlier post (# 36716169).

So that I can be more effective at helping others in the future (ie to recognise why the addin isn't suitable), can you please test it and tell me how it fails to meet your needs?

I look forward to your response,
Rob
Broro183 brother,

Sorry that my language was inappropriate in my earlier response.

Bulls eye. Your solution did the job perfectly. I am very happy. Sorry for not giving the appropriate time to your comment.

The only reason this has happened was because, I was looking for vba based solution.

But your solution did the job.

Now do you think i can close the question though the solution was not vba driven.

Kindly comment.

Thank you
hi everyone,

ExcelLearner, Phew!
I'm pleased it helped - I was really struggling to see what I might have overlooked. RDB's work is very helpful for me & you may find other good stuff on his site too.
I'm not sure how the E-E Mod's decide exactly what zone a question is held in but...
fyi, this is still a "vba driven" solution, it's just that RDB has done all the work and the vba is now "locked inside" the addin file.

Jan24, sorry about pulling this one out from under you before/after your work with the .
I think your solution is worthwhile, however if you check out RDB's addin you'll probably agree with me that his, existing, addin is sooo much more flexible.

ExcelLearner,
I think you can close the question. It's your choice what you do with the points. You can consider that the transparency of Jan's work allowed you the opportunity to learn... did you?
In contrast, my link provides what you need and more, BUT you can't learn from it as it is a "black box".

Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Broro brother,

I have raised another question with ID: 27362399, this particuarly refers to the situation for my wife.

I could not find any proper coaching places in London which would train people in Ms access and vba, hence i was exploring online tutorials and sending her to India.

can you please comment in that question, if there is a place where they would take people on a voluntary basis and train them or charge them for training in these skills.

Appreciate your help on that.

Thank you
Broro brother,

One observation on the solution you suggested.

I am not quite sure if i can provide two ranges in the addin.

Where as in vba we can make the query to select two or more distinct ranges with an query.

thank you,
hi Excel Learner,

Have you looked at the [Tips] button beside the "Fixed range" section?
I'm not sure if this will do what you want or not, you'll have to try it and see...
Even if it doesn't exactly do what you want, you may still find it faster to run RDB's addin twice (ie once for each range) and consolidate the results.

If you still want a different solution, a Moderator would be able to tell you the best course of action, which may be to ask a new question, or it may be to re-open this thread & continue with Jan24's work.

I'll post in your other thread about MS Access & VBA training.


Rob