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
If you want to start to use the software as soon as possible, then you can:
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.
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.
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