[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Macro to extract data from files

Posted on 2011-09-23
5
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
hippohood earned 1500 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

650 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