یکپارچه سازی چند فایل اکسل (ادغام چند فایل اکسل)

در بسیاری از مواقع میخواهیم ادغام چند فایل اکسل را در یک فایل اکسل انجام دهیم، در حقیقت عموما  اطلاعات مورد نیاز جهت تحلیل و گزارش گیری در چند فایل اکسل موجود است و برای گزارشگیری نیاز به یک پارچه سازی آنها میباشد.

برای مثال حسابداری که گزارشات خود را به صورت ماهیانه در فایلهای اکسل جداگانه نگهداری میکند و میخواهد گزارش سالیانه ارائه دهد یا پروژه ای که اطلاعات آن به صورت روزانه در فایلهای اکسل مجزا نگهداری میشود. برای یک پارچه سازی این فایلها میتوان از ابزار Power Query (اکسل ۲۰۱۳ به بعد) یا VBA استفاده نمود.

برای مثال فرض کنیم تمامی فایلهای اکسل در فولدر Test در درایو C قرار دارند و  میخواهیم اطلاعات محدوده A1:N12 را از شیت به نام Report جمع آوری کنیم و در شیتهای مختلف در فایل حاظر قرار دهیم که این کار از طریق VBA طی مراحل زیر قابل انجام است.

گام ۱: خاموش کردن حالت نمایش تغییرات (به منظور افزایش سرعت اجرا)

Application.ScreenUpdating = False

گام ۲: تعریف متغیر های مربوط به مسیر و استفاده از تابع Dir جهت پیدا کردن اولین فایل با پسوند xlsx

directory = “c:\test\”
fileName = Dir(directory & “*.xlsx”)

گام ۳: تعریف حلقه ای برای جست و جوی تمامی فایلها در مسیر تعریف شده فوق

Do While fileName <> “”

Loop

گام ۴: باز کردن فایل اکسل شناسایی شده

Workbooks.Open (directory & fileName)

گام ۵: ساخت شیت جدید در فایل جاری و کپی نمودن اطلاعات از فایل باز شده به فایل موجود.

Thisworkbook.Sheets.Add After:=ActiveSheet

ActiveWorkbook.Sheets(“report”).Range(“A1:N12”).Copy

ThisWorkbook.ActiveSheet.past

گام ۶: بستن فایل باز شده.

Workbooks(fileName).Close

گام ۷: پیدا کردن فایل اکسل بعدی

fileName = Dir()

 

 

در قسمت زیر کل کد به صورت یک پارچه آورده شده است.

 

Sub Directory_cod()
Application.ScreenUpdating = False
directory = “c:\test\”
Filename = Dir(directory & “*.xlsx”)

Do While Filename <> “”
    Workbooks.Open (directory & Filename)
    ThisWorkbook.Sheets.Add After:=ActiveSheet
    ActiveWorkbook.Sheets(“report”).Range(“A1:N12”).Copy
    ThisWorkbook.ActiveSheet.past
    Workbooks(Filename).Close
    Filename = Dir()
Loop

End Sub