Solved

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

Posted on 2011-09-25
16
450 Views
Last Modified: 2013-11-27
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,
0
Comment
Question by:Excellearner
  • 6
  • 5
  • 3
16 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 36596766
hi,

I'm off to bed so I won't be able to post any follow up comments for a while, but when I saw your question I thought that you may find Ron DeBruin's free add-in useful. I haven't checked if it does exactly what you want, but I'm sure it is very close - see the below link for screenshots & plenty of details:
http://www.rondebruin.nl/merge.htm

hth
Rob
0
 
LVL 2

Expert Comment

by:jan24
ID: 36713217
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
0
 

Author Comment

by:Excellearner
ID: 36716169
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,
0
 
LVL 2

Expert Comment

by:jan24
ID: 36717742
Here it is again set up to do 'sheet1'!b2:c6.  Try it and let me know if it works!
Book10a.xls
0
 
LVL 10

Expert Comment

by:broro183
ID: 36718807
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
0
 
LVL 2

Expert Comment

by:jan24
ID: 36719924
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.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36720623
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Excellearner
ID: 36720697
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
0
 
LVL 10

Expert Comment

by:broro183
ID: 36771333
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
0
 

Author Comment

by:Excellearner
ID: 36817765
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
0
 
LVL 10

Expert Comment

by:broro183
ID: 36890223
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...
0
 

Author Comment

by:Excellearner
ID: 36892376
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
0
 

Author Comment

by:Excellearner
ID: 36892406
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,
0
 
LVL 10

Expert Comment

by:broro183
ID: 36900945
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
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now