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
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.