Thread: Import content from separate Excel files to a single spreadsheet?

Hi everyone,

I've got several Excel files that need to be edited separately.
Once they have been edited, I need to paste the content of each file in a single separate Excel file.

Is there a way to retrieve the content from all the files in a folder, and merge it in a single file?

For example:
3 separate files > File1, File2, File3

Merged file, sheet1
File1
File2
File3

I'd really appreciate any help.

Hi paokun
A good place to start is with workbook by ravishankar
http://www.excelforum.com/attachment...eet-nimrod.xls

Hi pike,

Wow, this is a great macro!
Thanks, it does the job pretty neatly.

Now, starting from this sheet, how can I import only the visible cells?

If that's not possible, being able to import only specific columns would already help.

hey paokun
try something like this as its easier to follow and change

Sub ptest()
    Dim wbIn As Worksheet, shtOutOne As Worksheet, shtOutTwo As Worksheet
    Set wbIn = ThisWorkbook.Worksheets(2)
    Set shtOutOne = Workbooks.Open("C:\Documents and Settings\User\My Documents\New Folder\ptest1.xls").Worksheets(3)
    Set shtOutTwo = Workbooks.Open("C:\Documents and Settings\User\My Documents\New Folder\ptest2.xls").Worksheets(3)
    shtOutOne.Range("A1").CurrentRegion.Copy Destination:=wbIn.Range("A1")
    shtOutTwo.Range("A1").CurrentRegion.Copy Destination:=wbIn.Range("A" & wbIn.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Set wbIn = Nothing
    Set shtOutOne = Nothing
    Set shtOutTwo = Nothing
End Sub

Hi paokun

have a look at the code in this one

Pike, Paokun,

This variation on Pike's macro allows you to expand/contract the # of files in question by only editing one line of code...marked in red. Just add more filenames.

Option Explicit

Sub ptest()
Dim wbIn As Worksheet, i As Long, MyFiles
Set wbIn = ThisWorkbook.Worksheets(1)

MyFiles = Array("File1.xls", "File2.xls")

For i = LBound(MyFiles) To UBound(MyFiles)
    Workbooks.Open (ThisWorkbook.Path & "/" & MyFiles(i))
    Worksheets(1).Range("A1").CurrentRegion.Copy Destination:=wbIn.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    ActiveWorkbook.Close False
Next

Set wbIn = Nothing
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Sorry, that was supposed to be an "/" not a "]".

If you want to:

1) Open all the files in a single folder and import them into a single sheet, data stacked
2) Close the imported files

I have a standard macro for that, you won't have to list the files, just set the correct path.

What's the range of data or column(s) you want to import?
What's the path to the folder?
Are all the files named similarly? Or should we just import everything in that folder regardless of name?

Here's the standard macro. I've highlighted the sections you would need to edit to suit your needs.

Option Explicit

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, edit as needed
    
    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 your current working path
    fPath = "C:\My Documents\ExcelFiles"      'files are here
    fPathDone = "C:\My Documents\ExcelFiles\Imported"     'move files to here after import
    ChDir fPath
    fName = Dir("*.xl*")      'filtering key, change to suit

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

'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ChDir OldDir    'restores your original working path
End Sub

Originally Posted by paokun

Ops sorry I was replying to your message then I saw the macro.
Can you edit the macro based on the info I wrote above?

Why don't you take a whack at that, it will help you understand the code if you give it a shot.

Hmm, just read your answers, always more features needed than originally thought...

Originally Posted by paokun

1) Open all the files in a single folder and import them into a single sheet, data stacked
Yes, basically I just put the macro file in the folder and would like to import all the files in this folder. If the macro file needs to be put in a separate folder, then I'd like to import the files that are in: C:\Project1\

You can edit that import path, yes?
I would put the macro file in a separate folder...

2) Close the imported files
Yes, that would be better.

Already does that...

3) I'd like to import columns F M N O and exclude the header line 2 in each file.

Um...FMNO should land in columns ABCD?
Rows3---> bottom of the data? That part is already highlighted, you can edit that...

4) I'd like to have the filename and worksheet appear in the export file so that I know where the text comes from.

Listed where? Think of everything I might as next in regard to this item and include a complete explanation of the output requirements. (nudge)

The four columns above are the only visible columns, all the rest is hidden. Still, probably it's more complicated to have the macro import only the visible text, is that why you asked for the range of cells?

Nah, it's equally cumbersome or equally simple...depends on your comfort level.

Copying all the visible data only, that could be helpful, yes indeed.