Solved

Macro to extract data from files

Posted on 2011-09-23
5
219 Views
Last Modified: 2012-05-12
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.      Close the current file from where the data has been copied
6.      Then open the next file and the repeat the steps 2 to 5,
7.      But macro should copy the range data adjacent to the range copied from file 1 and so on
8.      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
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
hippohood earned 500 total points
ID: 36591333
The code below should do it for you. You have to define all the constants (input sheets directory, sheet name and ranges; output  workbook, shete and range to start). Every new copied range will be pasted to the right from previous. Let me know if you have any problem
Sub JustDoIt()

'
Const theDir = "P:\My Documents"
Const theSheet = "Sheet 1"
Const theRange = "A2:B10"
Const OutputWorkbook = "P:\My Documents\output.xlsx"
Const OutputSheet = "Sheet 1"
Const Output_StartRng = "A1"
    
Dim fs, f, f1, fc, s

Dim i%, c%
Dim outRange As Range
    ChDir theDir
    Workbooks.Open Filename:=OutputWorkbook
    Sheets(OutputSheet).Select
    
    Set outRange = Workbooks(OutputWorkbook).Worksheets(OutputSheet).Range(Output_StartRng)
    i = 0
        
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(theDir)
    Set fc = f.Files
    For Each f1 In fc
        Workbooks.Open Filename:=f1.Name
        Sheets(theSheet).Select
        Range(theRange).Copy
        c = Range(theRange).Columns.Count
        Workbooks(OutputWorkbook).Activate
        outRange.Offset(0, c * i).Select
        Workbooks(OutputWorkbook).Worksheets(OutputSheet).Paste
        Workbooks(f1.Name).Close
        i = i + 1
    Next
    
End Sub

Open in new window

0
 

Author Comment

by:Excellearner
ID: 36595082
Hi Hippo Hood,

Thank you for the vba query.

I changed the details as per the below:
Sub JustDoIt()

'
Const theDir = "C:\Documents and Settings\********\Desktop\consol"
Const theSheet = "OUTPUT"
Const theRange = "b3:e5"
Const OutputWorkbook = "C:\Documents and Settings\********\Desktop\consol\Macro\Data output.xlsx"
Const OutputSheet = "Sheet1"
Const Output_StartRng = "A1"
I got the below error also:

*****
'Run time error '1004':
Application-defined or object defined error
******

Also, can i request to amend the vba to copy the name of the sheet in row 1 from where the data is being copied.

Kindly help.

Doc1.doc
0
 

Author Closing Comment

by:Excellearner
ID: 36596342
the solution was not complete.
0
 
LVL 7

Expert Comment

by:hippohood
ID: 36601233
Learner,

I could not come up with the answer to your other question so quickly. I t was weekend and I am in a different ime zone.

Do you stil need help?
0
 

Author Comment

by:Excellearner
ID: 36602307
Hi hippohood,

I still need help. I thought it was better to close the current question and put forward another question.

I also included a new condition in the new question i put forward. The reference number of the new question i have put is 27326878.

Hope you had a good weekend.

Yes i am desperately waiting for the solution.

Thank you
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

828 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