Compile All Data from Folder with EXCEL , VBA , Python - 3 in 1 Class in Hindi

1. Using Excel (Power Query)

Steps:

1. Open Excel and go to Data → Get Data → From File → From Folder.
2. Select the folder containing your files.
3. Click Combine & Load to merge all files.
4. Modify transformations if needed.

?? Best for: Quick, UI-based solution for structured files.

2. Using VBA (Excel Macro)

Sub CompileData()
    Dim ws As Worksheet, wb As Workbook
    Dim FolderPath As String, File As String
    Dim LastRow As Long, PasteRow As Long
    
    ' Define folder path
    FolderPath = "C:\YourFolderPath\"  
    File = Dir(FolderPath & "*.xlsx")  ' Change file type if needed
    
    ' Set active worksheet
    Set ws = ThisWorkbook.Sheets("MasterSheet")
    PasteRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' Loop through files
    Do While File <> ""
        Set wb = Workbooks.Open(FolderPath & File)
        With wb.Sheets(1)  ' Assuming data is in Sheet1
            LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
            .Range("A2:D" & LastRow).Copy  ' Adjust range as needed
            ws.Cells(PasteRow, 1).PasteSpecial Paste:=xlPasteValues
            PasteRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        End With
        wb.Close False
        File = Dir()
    Loop
    MsgBox "Data Compilation Completed!"
End Sub
        

?? Best for: Automating file merging directly within Excel.

3. Using Python (Pandas)

import pandas as pd
import os

# Define folder path
folder_path = r"C:\YourFolderPath"

# List all files
files = [f for f in os.listdir(folder_path) if f.endswith(".xlsx")]

# Create an empty DataFrame
df_combined = pd.DataFrame()

# Loop through files and append data
for file in files:
    file_path = os.path.join(folder_path, file)
    df = pd.read_excel(file_path)  # Modify sheet name or columns if needed
    df_combined = pd.concat([df_combined, df], ignore_index=True)

# Save to a new file
df_combined.to_excel("Combined_File.xlsx", index=False)

print("Data Compilation Completed!")
        

?? Best for: Handling large datasets efficiently and exporting results in a structured format.

Which One Should You Use?

• Power Query (Excel) → Best for simple merging without coding.
• VBA (Macro) → Best for automation within Excel.
• Python (Pandas) → Best for large datasets and advanced processing.

please call +918802579388 (sujit sir)