+ Reply to Thread
Results 1 to 3 of 3

How to merge multiple excel files..?

Hybrid View

  1. #1
    Registered User
    Join Date
    01-05-2010
    Location
    Sunrise, Florida
    MS-Off Ver
    Excel 2003
    Posts
    2

    How to merge multiple excel files..?

    Hi all, please excuse my new-bee-ness..I have searched far and wide and can not get an answer...

    Situation: I have about 80 excel files I have to input into a database for access.
    Problem: Instead of loading one file at a time (Access import external data allows only one at a time) I wish to upload one file with all the 80 files in one.
    My proposed solution: I was thinking, open each excel workbook, and then move the tab from each of the 80 workbooks into one main workbook (that i will use, ie like a hub)..Then once the main workbook is complete with the 80tabs, I will find a way to move the data from the tabs into one single tab to upload!

    Please assist if you know a smarter, faster way to do this...I have do not want to do this tedious work

    PS: All the files have the same headers, and the tab2 is the one i want, which they all open to tab 2 when you open the files.
    Last edited by clone000; 01-06-2010 at 05:52 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Urgent Help with merging multiple excel files..

    This is my stock macro for this task. You'll need to edit the sections in red so that it functions the way you want.

    Option Explicit
    
    Sub ConsolidateFiles()
    'Open all Excel files in a specific folder and merge data into one master sheet (stacked)
    'Moves imported files into another folder
    'JBeaucaire (9/15/2009)     (2007 compatible)
    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 to build report onto, 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
            Range("A2:BB" & Rows.Count).Clear
            NR = 2
        Else
            NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        End If
    
    'Path and filename
        OldDir = CurDir             'memorizes the user's current working path
        fPath = "C:\My Documents\Files"              'path to files to import
        fPathDone = "C:\My Documents\Files\Imported" 'path to put imported files when done
        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 to report sheet
                LR = Range("A" & Rows.Count).End(xlUp).Row
                Range("A2:A" & LR).EntireRow.Copy _
                    wbkNew.Sheets("Master").Range("A" & NR)
            'close file
                wbkOld.Close True
            'Next row to import onto
                NR = Range("A" & Rows.Count).End(xlUp).Row + 1
            'move imported file to "imported" folder so it won't be reimported accidentally later
                Name fPath & fName As fPathDone & fName
            'ready next filename for import, then loop
                fName = Dir
        Loop
    
    'Cleanup
        ActiveSheet.Columns.AutoFit
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
    'restores user's original working path
        ChDir OldDir
    End Sub
    Just name your "Master" sheet for the import and put in the ROW1 headers. The macro will do the rest.
    Last edited by JBeaucaire; 01-05-2010 at 06:09 PM. Reason: a couple of tweaks, user should put the row1 titles in ahead of time.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-05-2010
    Location
    Sunrise, Florida
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Urgent Help with merging multiple excel files..

    Thanks - did everything but getting

    Runtime Error 9 on the below text (in yellow);
    Range("A2:A" & LR).EntireRow.Copy wbkNew.Sheets("Master").Range("A" & NR)
    let me know the specific steps if I am missing something..

    Essentially,
    I created a master file and put it into a folder called "Done".
    Then i changed the paths and dest paths, and ran the macro but got the error above...

    I can see this almost working, what am i doing wrong tho

    columns in use is A to O, but the data could be as long as line 1,000 (row).
    Last edited by Paul; 01-06-2010 at 05:54 PM. Reason: Added code tags around code. Please do so yourself in the future, thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1