Solved

consolidate multiple excel files to one

Posted on 2014-10-16
10
386 Views
Last Modified: 2014-10-17
I have an issue.  I have a whole bunch of files in a folder with various different names.  The files I want to merge have the word REC at the start of the file
e.g REC_012014.xlsx, REC_022014.xslx..etc.  They all have the same format, and I just want to extract the first sheet with range A1:D50 in all the files and merge them into one excel file.  How do I go about doing this?
0
Comment
Question by:Qazzim Dungey
[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
  • 4
  • 3
  • 2
10 Comments
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 40386328
Here is something to get you started
Sub MergeFiles()
    Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
    Dim sheet As Worksheet
    
    Application.ScreenUpdating = False
    directory = "C:\test\"
    fileName = Dir(directory & "REC*.xl??")
    Do While fileName <> ""
        Workbooks.Open (directory & fileName)
        sheet = Workbooks(fileName).Worksheets(0)
        sheet.Range("A1:D50").Select
        Selection.Copy
        ' do what you need to here
        fileName = Dir()
    Loop
    
End Sub

Open in new window

To use the above create a new Excel spreadsheet and create a macro with the above code - change the path name as required.

You did not say how you wanted to merge the data so you will need to do that yourself but it is easy to work out the code simply do it manually once and record it in a macro then view the code.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40386513
You would easily accomplish this with free Add-In of Ron de Bruin  http://www.rondebruin.nl/win/addins/rdbmerge.htm
0
 

Author Comment

by:Qazzim Dungey
ID: 40386630
JulianH, your are almost there.  What I want to do with it is merge it vertically, so it joins all the files and is the exact same as the other reports, the only difference is this one will just have a larger amount of data.  The issue I am having is I am trying that but how do I keep record of the last row, so I can append without overwriting.  I am fairly new to VBA.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 56

Expert Comment

by:Julian Hansen
ID: 40386738
That's easy you are copying out 50 rows at a time so you have a loop variable that starts at 0.

Each time you go round you paste to the row = loop_variable * 50

Something like this
Sub MergeFiles()
    Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
    Dim sheet As Worksheet
    Dim Row As Integer
    Dim Cell as String

    Application.ScreenUpdating = False
    directory = "C:\test\"
    fileName = Dir(directory & "REC*.xl??")
    Row = 0
    Do While fileName <> ""
        Workbooks.Open (directory & fileName)
        sheet = Workbooks(fileName).Worksheets(0)
        sheet.Range("A1:D50").Select
        Selection.Copy
        Cell = "A" & ((Row * 50) + 1)
        Range(Cell).Select
        ActiveSheet.Paste
        Row = Row + 1

        fileName = Dir()
    Loop
End Sub

Open in new window

0
 

Author Comment

by:Qazzim Dungey
ID: 40386981
It is giving me a subscript out of range when I test it out.  My guess it is referencing something out of the limit.  Can you spot the error?
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40386995
Qazzim,  

Why not use the free Add-In of Ron de Bruin which exactly does what you need.
0
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 40387034
What line is the error on?
0
 

Author Comment

by:Qazzim Dungey
ID: 40387085
sheet = Workbooks(fileName).Worksheets(0)

ProfessorjimJam, I am not using the utility because I need to get this macro for a bigger problem.  This is just one small part to it.
0
 
LVL 56

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 40387284
Thats because it is an object and was missing the 'Set'

Did some testing - this should mostly do what you want
Sub Macro1()
'
' Macro1 Macro
'

'
    Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer, target As Worksheet
    Dim Row As Integer
    Dim Cell As String, sOrigWS As String, sOrigWB As String
    
    directory = "e:\projects\storevue\spar\"
    fileName = Dir(directory & "*.xlsx")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Row = 0
    Set target = ActiveSheet
    sOrigWS = ActiveSheet.Name
    sOrigWB = ActiveWorkbook.Name
    Do While fileName <> ""
        Workbooks.Open (directory & fileName)
        Set sheet = Workbooks(fileName).Worksheets(1)
        sheet.Range("A1:D50").Select
        Selection.Copy
        Workbooks(sOrigWB).Activate
        Sheets(sOrigWS).Select
        Cell = "A" & ((Row * 50) + 1)
        Range(Cell).Select
        ActiveSheet.Paste
        Workbooks(fileName).Close
        Row = Row + 1

        fileName = Dir()
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

726 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