Thread: How to create one file from many excel files

Hello my first post,

First of all great place, I plan to spend a lot of time here, because I'm new to excel and I need to know many things.

I will be very thankful for help ...

Here is what I need.I have several excel files, and I want to create one from them.First option is copy past but that is time consuming, so is there any other way.

So lets say that is my first file:

A B
a1 b1
a2 b2


And let's say that is my second file

A B
a3 b3
s f
v h

So I want to create this one

A B
a1 b1
a2 b2
a3 b3
s f
v h

Is there a way, please?I have many excel files that are in one directory.

BodMov,

Welcome to the forum. As you are an inexperienced excel user I would do the following.

Have all files open including the consolidation file.
When you right click a tab (bottum) you can copy/move it to the consolidation file.

Hi BodMov, this will be a VBA question, in case you didn't realize. I have a stock macro to import all the files in a folder into a single stacked worksheet. Here's the code:

Code:

Sub Consolidate()
'Author:     JBeaucaire'
'Date:       9/15/2009     (2007 compatible)'
'Summary:    Open all Excel files in a specific folder and merge data'
'            into one master sheet (stacked)'
'            Moves imported files into another folder'
Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet

'Setup'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Set wbkNew = ThisWorkbook
    wbkNew.Activate
    Sheets("Master").Activate   'sheet report is built into'
    
    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
    
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        Cells.Clear
        NR = 1
    Else
        NR = Range("A" & Rows.Count).End(xlUp).Row + 1
    End If

'Path and filename'
    OldDir = CurDir             'memorizes the current working path'
    fPath = "C:\2010\"
    fPathDone = "C:\2010\Imported\"
    ChDir fPath
    fName = Dir("*.xl*")      'filtering key, change to suit'

'Import a sheet from found file'
    Do While Len(fName) > 0
        'Open file'
            Set wbkOld = Workbooks.Open(fName)
        'Find last row and copy data'
            LR = Range("A" & Rows.Count).End(xlUp).Row
            Range("A1:A" & LR).EntireRow.Copy _
                wbkNew.Sheets("Master").Range("A" & NR)
        'close file'
            wbkOld.Close False
        'Next row'
            NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        'move file to IMPORTED folder'
            Name fPath & fName As fPathDone & fName
        'ready next filename'
            fName = Dir
    Loop

'Cleanup'
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

'restores original working path'
    ChDir OldDir
End Sub

========
SETUP:

1) Edit the code above so the fPath is where your files to import exists.
2) On your computer, create another folder inside that path call Imported, and then make sure that path is listed in the code for the fPathDone variable.
3) Put the macro into your report workbook:

How/Where to install the macro:

A. Open up your report workbook
B. Get into VB Editor (Press Alt+F11)
C. Insert a new module (Insert > Module)
D. Copy and Paste in your code (given above)
E. Get out of VBA (Press Alt+Q)
F. Save your sheet in a different directory from the import files, or save it into the ...\IMPORTED folder.

The macro is installed and ready to use.

=======
USAGE:
Press Alt-F8 and select Consolidate from the macro list.

BodMov,

I didn't want to direct you to VBA just yet, but like JB states: inescapable VBA

Use JB's solution. That's the best approach when dealing with >100 files per day.

Either create a sheet in you workbook called Master

or

Change that line of code and put in the sheetname you want used.

The next error that I see in debud is :

Name fPath & fName As fPathDone & fName

actually where do I have to see my new excel document?

VBA is a tough nut to crack for the first time. Let's start over.

Tell me:

1) The name of the SHEET in your workbook you want to compile this report into. In my macro it's called Master...

2) The complete path to the folder where your 100s of files will be

3) How many sheets are in those 100s of files? Can we simply import the active sheet when each one opens?

If not, what is the sheet name you want imported from those files?

4) Do the import sheets have titles in row1? Do those need to be skipped or can we just import it all?

5) Have you created another folder called IMPORT inside the path that holds the files?

NOTE: When you run this macro from inside the Report workbook, that workbook needs to running from a different path than your import files. I've seen in the past where people put the report into the same folder...it imports all the files and then gets to the report file itself and reopens it...effectively erasing the work already done. Oops.

Here, just take this workbook and save it somewhere, open it and run the Consolidate macro.

Glad to hear it.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.