How to Import and Manage Multiple Excel files from a Master File in Excel

Being a manager is really a tough job. Managing your employees, collating their work, preparing a report and sending it to the senior management really needs a lot of time. With Excel and macro build a tool which will quickly merge all your employee’s workbook into one workbook. You can then share it with your senior management

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

This tool requires 2 sheets. In your macro-enabled workbook, rename sheet1 as “Import” and rename sheet2 as “Master”. As shown in the image, design the sheet “Import” to allow the user to browse and pick a folder. All files from the selected folder will be merged into Master file. The import button will allow the user to run the macro.Modify The Sheet Master

You can modify the sheet “Master” as per your requirement. However, please do note that all other files that are to be imported should follow the same format as the Master sheet. Else the macro might throw a error or the data will be imported at different columns.Modify The Sheet Master

Let’s make it functional

Copy these scripts to a new module in your macro enabled workbook. Attach the macro “Select_Folder” to the button “Browse” on the sheet “Import” and the macro “Lets_Prepare_Master” to the button “Import”. Add necessary files to the selected folder before running the macro.

Sub Select_Folder()
    Dim v_startfolder
    v_startfolder = ThisWorkbook.path & "\"
    Sheets("Import").Range("D9").Value = f_Pickafolder(ThisWorkbook.path & "\") & "\"
End Sub

Function f_Pickafolder(Optional v_startatthis As Variant) As Variant
    Dim v_obj1 As Object
    Set v_obj1 = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Select folder to import Excel files", 0, v_startatthis)
    On Error Resume Next
    f_Pickafolder = v_obj1.self.path
    On Error GoTo 0
    Set v_obj1 = Nothing
    Select Case VBA.Mid(f_Pickafolder, 2, 1)
        Case Is = ":"
        If VBA.Left(f_Pickafolder, 1) = ":" Then GoTo errorsocomehere
            Case Is = "\"
            If Not VBA.Left(f_Pickafolder, 1) = "\" Then GoTo errorsocomehere
                Case Else
                GoTo errorsocomehere
            End Select
            Exit Function
            errorsocomehere:
            f_Pickafolder = False
End Function

Sub Lets_Prepare_Master()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim v_var1 As String, v_var2 As String, v_var3 As Integer
    Dim eb As Workbook
    Dim wb As Workbook
    Dim lrineb As Long
    Dim rineb As Long
    Dim lrinm As Long
    Set wb = ThisWorkbook
    v_var1 = wb.Sheets("Import").Range("D9").Value
    v_var2 = v_var1 & "\*.xls"
    v_excel = Dir(v_var2)
    Do While v_excel <> ""
        Debug.Print v_excel
        Set eb = Workbooks.Open(wb.Sheets("Import").Range("D9").Value & v_excel)
        lrineb = eb.Sheets(1).Range("A" & Rows.count).End(xlUp).Row
        lrinm = wb.Sheets("Master").Range("A" & Rows.count).End(xlUp).Row + 1
        eb.Sheets(1).Range("A2:F" & lrineb).Copy _
        Destination:=wb.Sheets("Master").Range("A" & lrinm)
        eb.Close False
        v_excel = Dir()
    Loop
End Sub

How does it work?

When the button “Browse” is clicked, a folder browse will open and the user will be prompted to pick a folder. The full path of the selected folder will appear on the sheet “Import”. User can also type or copy paste the full path of folder from windows explorer. When the button “Import” is clicked, each file in the selected folder is opened and data from the sheet1 is imported into the Master sheet.

Modify it

The macro will throw error when it tries to open a corrupted Excel file. You can write an error handler to capture errors and skip those files. Or you can consider use third-party tools to do an Excel repair manually on those files.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Word damage and outlook recovery software products. For more information visit www.datanumen.com

2 responses to “How to Import and Manage Multiple Excel files from a Master File in Excel”

  1. You are talking about opening a source file, copying the source data en bloc, and then appending that data at the bottom of a database in a separate master file. No, this is rarely done.

    First of all, files are not “imported”. You make it sound like some action is taken on a source file. No. Data from a source file is copied and integrated into a database in a separate file but nothing is done with the source file itself. It is not moved nor modified. This may sound like a distinction without a difference but I believe this “file import” terminology is conceptually misleading. And it sounds amateurish. IMO. (Import this, export that: ugh!)

    Second, the action of integrating data from a source file into a database is rarely a simple matter of appending at the bottom of a destination list of exactly the same structure as the source. Very often, the source file is formatted differently, columns are labeled differently, columns are ordered differently, extra columns exist in the source and some columns that exist in the database do not exist in the source (and vice versa), etc.

    If so, and the above is usually the case, data must be picked from specific fields in the source to individually build/format each new master record.

    Even then, more work needs to be done. When adding a new record, the database must first be checked to see if such a record already exists. If so, a new record is not added; instead, the existing record is updated.

    If you thought you knew for a fact that there could not be existing duplicate records in the master database, you would be wrong. For instance, if you forgot that you performed this operation yesterday, and did it again today, you would have duplicate records in the master database. And this would always be a possibility/concern if you did not have code in place to continually check for existing duplicates.

    Most of this work can be done automatically if one has coded simulated queries. For instance, I have an UpdateQuery() subroutine that updates an arbitrary list B from an arbitrary list A. The column structures of source/destination need not match. Similarly, I have an AppendQuery() subroutine that appends records from an arbitrary list A to an arbitrary list B. Again, the column structures don’t have to match and duplicates are automatically skipped based on common values in user-specified fields of the source and destination lists.

    While your macro is certainly a step in the right automated direction, IMO much more flexibility is possible and needed.

Leave a Reply

Your email address will not be published. Required fields are marked *