Solved

Macro to extract data from files

Posted on 2011-09-23
5
211 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 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

22 Experts available now in Live!

Get 1:1 Help Now