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.

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.

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