How To Create Automated Data Collator in Excel !!

Collating records from numerous Excel documents to one record is a genuinely stressful process and it takes lots of time and guide effort. Also, there may be a high possibility of mistakes in copying and pasting facts. We might leave out any of the documents or paste the records underneath unique column headers.

Excel constantly plays a crucial position in automating the assignment with a Macro or BA code. In this article, we will discover ways to create a completely automated statistics collator tool in Excel and VBA. Please comply with the beneath steps.

Step 1

Create a New Excel File and save it with the name ‘Automated Data Collator’. Make sure the extension should be ‘.xlsm’ otherwise, the file will not retain the VBA codes.

Step 2

Add two different sheets in the ‘Automated Data Collator’ file. Rename the ‘Sheet1’ to ‘Home’ and ‘Sheet2’ to ‘Collated Data’.

Step 3

Create the label and add a command button (rounded rectangle) from the shape with ‘Collate’ caption. Please see the below image.

Step 4

Add the required column headers in the ‘Collated Data’ sheet in row 1. Please see the below image and use the same headers and formatting.

Step 5

Let’s move to the Visual Basic Application window. To jump to the VBA window, click on Developer Tab then click on VBA in code group. Alternatively, you can press shortcut key ALT + F11.

In this tool, we will utilize FileSystemObject(FSO) and it’s methods. So, let’s add the reference of FSO. To do that, click on Tools menu and then click on ‘Reference’ and select ‘Microsoft Scripting Runtime’ from the available references. Please see the below image.

Step 6

To write the codes, let’s insert a new Module in our project. To add a module, click on Insert menu then click on ‘Module’.

Step 7

Let’s add the below VBA codes in the Module1 code window.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

Sub CollateData() Application.ScreenUpdating = False Dim MyFSO As New FileSystemObject ‘Declaring and Initializing FSO Dim wkbSource As Workbook ‘ Workbook Dim iSourceRow As Long 'To store the last row number available in source file Dim iRow As Long 'To store the last blank row available in collated sheet before pasting data Dim iTotalRow As Long 'To store the last non-blank row available in collated sheet after pasting data Dim sPath As String 'To store the folder path Dim SourceFolder As Folder 'Folder Variable for FSO Dim MyFile As File 'File Variable for FSO Dim FileName As String 'To store the File Name only   Dim iTotalFiles As Long     'To store the count of all excel files available in Selected Folder Dim DialogBox As FileDialog   'File Dialog to select the folder name Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker) 'Assigning FolderPicker Dialog Box 'Code to open the Dialog Box and select a folder With DialogBox     .Title = "Select a Folder"     .AllowMultiSelect = False     .InitialFileName = Application.DefaultFilePath     If .Show <> -1 Then Exit Sub  'no folder selected     sPath = .SelectedItems(1) End With     'Check whether selected folder exist or not If Not MyFSO.FolderExists(sPath) Then     MsgBox "Folder is not available.", vbOKOnly + vbCritical, "Error!"     Exit Sub End If   Set SourceFolder = MyFSO.GetFolder(sPath) 'Get the count of all excel file available in selected folder iTotalFiles = 0 For Each MyFile In SourceFolder.Files     If MyFSO.GetExtensionName(MyFile) = "xlsx" Then         iTotalFiles = iTotalFiles + 1     End If Next MyFile 'Terminate the code if there is no excel file in selected folder If iTotalFiles = 0 Then     MsgBox "No Excel file available.", vbOKOnly + vbCritical, "Error!"     Application.ScreenUpdating = True     Exit Sub End If 'Code to compile all files For Each MyFile In SourceFolder.Files     If MyFSO.GetExtensionName(MyFile) = "xlsx" Then    'Code to find the last blank row number before pasting the data        iRow = ThisWorkbook.Sheets("Collated Data").Range("B" & Rows.Count).End(xlUp).Row + 1        'extracting the file name from MyFile        FileName = MyFSO.GetFileName(MyFile)        'Opening the source file in readonly mode        Set wkbSource = Workbooks.Open(FileName:=MyFile, ReadOnly:=True)        'Code to find the last non-blank row number in source file before copying the data        iSourceRow = wkbSource.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row        'If there is no data in the current file then move to next file and ignore it        If iSourceRow = 1 Then GoTo NextLoop        'Code to Copy the data        wkbSource.Sheets("Data").Range("A2:K" & iSourceRow).Copy        'Code to paste the data        ThisWorkbook.Sheets("Collated Data").Range("B" & iRow).PasteSpecial Paste:=xlPasteValues        Application.CutCopyMode = False        'Code to find the last non-blank row number after pasting the data        iTotalRow = ThisWorkbook.Sheets("Collated Data").Range("B" & Rows.Count).End(xlUp).Row        'Code to update the file name        ThisWorkbook.Sheets("Collated Data").Range("A" & iRow & ":A" & iTotalRow).Value = FileName NextLoop: wkbSource.Close savechanges:=False Set wkbSource = Nothing End If Next MyFile MsgBox "Data have been collated. Thanks for using this tool!", vbOKOnly + vbInformation, "Done" Application.ScreenUpdating = True End Sub

Now, we have done with coding. Let’s move to Excel window and assign the macro on ‘Collate’ button available on ‘Home’ sheet.

Please click on the below button to download the Excel File (Data Collator) used in our tutorial.

DOWNLOAD FILE