وبلاگ

متد یا رفتار (Method) در برنامه نویسی شیءگرا یک تابع است که عملیاتی را که توسط شیء قابل انجام است را مدیریت می نماید. متد شیء Workbook عملی است که توسط کاربرگ اکسل می تواند انجام شود. در این آموزش با برخی از مهمترین متدهای شیء Workbook در VBA آشنا می شوید.

مدت زمان تخمینی مطالعه: 11 دقیقه

فهرست موضوعی

ماکروی VBA که کاربرگ اکسل را فقط خواندنی می نماید
بستن و ذخیره فایل اکسل با استفاده از VBA
ماکروی VBA برای ذخیره و تبدیل کاربرگ اکسل به فایل پی دی اف (PDF)
پرینت کاربرگ اکسل با استفاده از VBA
نمایش صفحه پیش نمایش چاپ با کمک VBA
قفل کردن فایل اکسل با استفاده از VBA
ذخیره کاربرگ اکسل با استفاده از ماکروی VBA
ذخیره فایل اکسل به عنوان جدید با استفاده از ماکروی VBA
ایجاد و ذخیره کپی جدید از فایل اکسل با استفاده از VBA

متد شیء Workbook چیست؟

شما در یک کاربرگ اکسل عملیات زیادی را می توانید انجام دهید. بعنوان مثال می توانید کاربرگ را پرینت کنید، کاربرگ را ذخیره کنید یا کاربرگ را ببندید. مایکروسافت کلیه عملیاتی را که بصورت مستقیم مرتبط با کاربرگ اکسل می باشد را در متدهای شیء Workbook ایجاد نموده است.

لیست مهمترین متدهای پشتیبانی شده توسط شیء Workbook را می توانید در ادامه مشاهده نمایید.

لیست متدهای شیء Workbook اکسل

متد AcceptAllChanges شیء Workbook

با کمک این متد می توانید شرایط قبول تغییراتی که روی کاربرگ به اشتراک گذاشته شده را تعیین نمایید. دستور این متد به صورت زیر می باشد:

expression.AcceptAllChanges (When, Who, Where)

• expression اختیاری؛ یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• When اختیاری؛ مشخص کننده زمانی است که تغییرات قبول می شوند.
• Who اختیاری؛ مشخص کننده شخصی است که تغییراتش قبول می شود.
• Where اختیاری؛ مشخص کننده مکانی است که تغییرات قبول می شود.

مثال

دستور زیر باعث می شود که تمامی تغییرات اعمال شده در کاربرگ فعال قبول شوند.

ActiveWorkbook.AcceptAllChanges

متد Activate شیء Workbook

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

expression.Activate

expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.

مثال

کد زیر اگر فایل اکسل Book4.xls چند تا پنجره داشته باشد، اولین پنجره آن را فعال می نماید. توجه داشته باشید که Workbooks("BOOK4.XLS") عبارتی است که به یک شیء Workbook اشاره دارد.

Workbooks("BOOK4.XLS").Activate

متد ChangeFileAccess شیء Workbook

با کمک این متد می توانید نحوه دسترسی به ایجاد تغییرات در یک کاربرگ (Read-only یا Read/write) را تعیین نمایید. دستور این متد به صورت زیر می باشد:

expression.ChangeFileAccess (Mode, WritePassword, Notify)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• Mode اجباری؛ مشخصه نحوه دسترسی جدید در کاربرگ می باشد و می تواند یکی از مقادیر xlReadOnly (فقط خواندنی) یا xlReadWrite (خواندنی و نوشتنی) را داشته باشد.
• WritePassword اختیاری؛ رشته ای است که کلمه عبوری است که دسترسی به اعمال تغییرات در فایل را مشخص می نماید. این کلمه عبور هنگام باز شدن فایل از کاربر درخواست می شود و در صورتی که کاربر آن را وارد نکند فایل بحالت فقط خواندنی (read-only) باز خواهد شد.
• Notify اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد به کاربر در مورد وضعیت فقط خواندنی (Read-only) کاربرگ هشدار داده می شود.

مثال

کد زیر کاربرگ فعال اکسل را بحالت فقط خواندنی تغییر وضعیت می دهد.

ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

متد Close شیء Workbook

این متد کاربرگ را می بندد و نحوه ذخیره تغییرات را زمان بستن فایل را مشخص می نماید. دستور این متد به صورت زیر می باشد:

expression.Close (SaveChanges, FileName, RouteWorkbook)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• SaveChanges اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد تغییرات ذخیره و در غیر اینصورت تغییرات حذف خواهند شد.
• FileName اختیاری؛ نامی است که فایل به آن نام ذخیره خواهد شد.

مثال

کد زیر کاربرگ Book1.xls را بدون ذخیره تغییرات آن می بندد.

Workbooks("BOOK1.XLS").Close SaveChanges:=False

متد ExportAsFixedFormat شیء Workbook

با کمک این متد در VBA می توانید ماکرویی ایجاد نمایید که کاربرگ را در قالب فایل PDF یا XPS ذخیره نماید . دستور این متد به صورت زیر می باشد:

expression.ExportAsFixedFormat (Type, FileName, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• Type اجباری؛ می تواند یکی از مقادیر xlTypePDF یا xlTypeXPS را داشته باشد. xlTypePDF برای تبدیل کاربرگ به فایل پی دی اف و xlTypeXPS برای تبدیل کاربرگ به فایل XPS.
• FileName اختیاری؛ نامی است که فایل به آن نام ذخیره خواهد شد. می توانید از مسیر کامل برای FileName استفاده نمایید (بعنوان مثال C:\New Folder\test.pdf). اگر از مسیر کامل استفاده نکنید اکسل فایل را در پوشه فعلی ذخیره خواهد کرد.
• Quality کیفیت فایل را تعیین می کند و می توانید یکی از مقادیر xlQualityMinimum (پایین ترین کیفیت) یا xlQualityStandard (کیفیت استاندارد)‌ را داشته باشد.
• IgnorePrintAreas اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد تنظیمات Print Area اکسل نادیده گرفته خواهد شد. اگر False باشد تنظیمات Print Area برای ایجاد فایل بکار گرفته خواهد شد.
• From اختیاری؛ صفحه شروع برای تبدیل فایل را تعیین می نماید. اگر نادیده گرفته شود اولین صفحه ملاک قرار گرفته خواهد شد.
• To اختیاری؛ آخرین صفحه ای که باید تبدیل شود را تعیین می کند. اگر نادیده گرفته شود تا آخرین صفحه تبدیل خواهد شد.
• OpenAfterPublish اختیاری؛ مشخص می کند که آیا فایل بعد از تبدیل باز شود یا خیر. می تواند یکی از مقادیر True یا False را داشته باشد.

مثال

ماکروی زیر کاربرگ فعال اکسل را به فرمت پی دی اف تبدیل می کند و با نام sales.pdf در پوشه ای که فایل کاربرگ اکسل در آن قرار دارد ذخیره می نماید. فایل پس از تبدیل باز خواهد شد.

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:="sales.pdf" Quality:=xlQualityStandard OpenAfterPublish:=True 

متد PrintOut شیء Workbook

با کمک این متد در VBA می توانید ماکرویی ایجاد نمایید که کاربرگ اکسل را چاپ نمایید. دستور این متد به صورت زیر می باشد:

expression.PrintOut (From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• From اختیاری؛ صفحه شروع برای پرینت را تعیین می نماید. اگر نادیده گرفته شود اولین صفحه ملاک قرار گرفته خواهد شد.
• To اختیاری؛ آخرین صفحه ای که باید پرینت گرفته شود را تعیین می کند. اگر نادیده گرفته شود تا آخرین صفحه پرینت خواهد شد.
• Copies اختیاری؛ تعداد کپی های یک پرینت را مشخص می نماید. اگر نادیده گرفته شود فقط یک کپی چاپ خواهد شد.
• Preview اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد قبل از چاپ صفحه print preview نمایش داده خواهد شد. در غیر اینصورت شیء بلافاصله چاپ خواهد شد.
• ActivePrinter اختیاری؛ مشخص کننده نام پرینتر فعال می باشد.
• PrintToFile اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد کاربرگ در یک فایل چاپ خواهد شد. اگر True باشد و PrToFileName مشخص نشده باشد نام فایل خروجی از کاربر درخواست خواهد شد.
• Collate اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد برای کپی های بیش از یک عدد، ابتدا تمام صفحات یک کپی بصورت کامل چاپ و سپس کپی بعدی چاپ می شود.
• PrToFileName اختیاری؛ اگر PrintToFile مقدار True داشته باشد، نام فایلی است که می خواهید اطلاعات در آن چاپ شود.
• IgnorePrintAreas اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد تنظیمات Print Area اکسل نادیده گرفته خواهد شد. اگر False باشد تنظیمات Print Area برای چاپ بکار گرفته خواهد شد.

مثال

ماکروی زیر با کمک VBA دستور چاپ کل کاربرگ را ارسال می کند و قبل از چاپ صفحه پیش نمایش چاپ را نشان خواهد داد.

ThisWorkbook.PrintOut Preview:=True

متد PrintPreview شیء Workbook

با کمک این متد در VBA می توانید صفحه پیش نمایش چاپ کاربرگ را نمایش دهید. دستور این متد به صورت زیر می باشد:

expression.PrintPreview (EnableChanges)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• EnableChanges اختیاری؛ می تواند یکی از مقادیر True یا False را داشته باشد. اگر True باشد کاربر می تواند تنظیمات مربوط به حاشیه ها و سایر تنظیمات صفحه (Page Setup) را تغییر نماید. اگر False باشد کاربر نمی تواند تظیمات صفحه را تغییر دهد.

مثال

ماکروی زیر صفحه پیش نمایش چاپ را به کاربر نمایش می دهد. کاربر می تواند تنظیمات صفحه را تغییر دهد.

ThisWorkbook.PrintPreview True

متد Protect شیء Workbook

با کمک این متد در VBA می توانید کاربرگ اکسل را قفل و حالت حفاظت آن را فعال نمایید. دستور این متد به صورت زیر می باشد:

expression.Protect (Password, Structure, Windows)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• Password اختیاری؛ کلمه عبوری است که برای غیرفعال کردن قفل کاربرگ بکار خواهد رفت. Password به بزرگی و کوچکی حروف حساس (case-sensitive) است.
• Structure اختیاری؛ اگر مقدار True داشته باشد حفاظت ساختار کاربرگ را فعال می نماید. مقدار پیش فرض آن False می باشد.
• Windows اختیاری؛ اگر مقدار True داشته باشد حفاظت پنجره های کاربرگ را فعال می نماید. اگر نادیده گرفته شود غیرفعال خواهد بود.

مثال

ماکروی زیر حالت محافظت کاربرگ اکسل فعلی را فعال می کند. کاربر برای غیرفعال کردن حالت محافظت باید رمز عبور (123) را داشته باشد.

ThisWorkbook.Protect Password:="123"

متد RefreshAll شیء Workbook

این متد در VBA محدوده داده های خارجی و گزارشات PivotTable را تجدید یا Refresh می نماید. دستور این متد به صورت زیر می باشد:

expression.RefreshAll

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.

توجه داشته باشید که داده های اشیائی که ویژگی BackgroundQuery دارند و روی True تنظیم شده است، بصورت خودکار در پس زمینه تجدید می شوند.

مثال

ماکروی زیر داده های خارجی و PivotTable سومین کاربرگ اکسل را تجدید می کند.

Workbooks(3).RefreshAll

متد Save شیء Workbook

با کمک این متد در VBA می توانید تغییرات یک کاربرگ اکسل را ذخیره نمایید. دستور این متد به صورت زیر می باشد:

expression.Save

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.

در صورتی که اولین بار است که می خواهید کاربرگ ذخیره شود، از متد SaveAs استفاده کنید تا بتوانید نام و مسیر فایل را نیز مشخص نمایید.

مثال 1

ماکروی زیر تغییرات اعمال شده در کاربرگ فعال اکسل را ذخیره می نماید.

ActiveWorkbook.Save

مثال 2

ماکروی زیر با استفاده از یک حلقه For Each همه کاربرگ های باز اکسل را ذخیره می نماید.

Dim w as variant
For Each w In Application.Workbooks 
    w.Save 
Next w 
Application.Quit

متد SaveAs شیء Workbook

با کمک این متد در VBA می توانید کاربرگ اکسل را در فایل جدیدی ذخیره نمایید. دستور این متد به صورت زیر می باشد:

expression.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• FileName اختیاری؛ نام فایل جدید است. می توانید فقط نام فایل (Book4.xlsx) یا مسیر کامل (C:\Users\asade\OneDrive\Desktop\Book4.xlsx) باشد.
• FileFormat اختیاری؛ نوع فایلی که می خواهید ذخیره کنید را تعیین می کند. لیست کامل مقادیر مجاز این پارامتر را می توانید در این لینک مشاهده نمایید.
• Password اختیاری؛ یک رشته حداکثر 15 نویسه ای (کاراکتری) و حساس به حروف بزرگ و کوچک (case-sensitive) که کلمه عبور بازکردن فایلی که ذخیره می شود می باشد.
• WriteResPassword اختیاری؛ رشته ای است که کلمه عبوری است که دسترسی به اعمال تغییرات در فایل را مشخص می نماید. این کلمه عبور هنگام باز شدن فایل از کاربر درخواست می شود و در صورتی که کاربر آن را وارد نکند فایل بحالت فقط خواندنی (read-only) باز خواهد شد.
• ReadOnlyRecommended اختیاری؛ اگر True باشد کاربر وقتی که فایل را باز کند پیغامی مبنی بر اینکه پیشنهاد می شود که این فایل در حالت فقط خواندنی (read-only) باز شود را نمایش خواهد داد.
• CreateBackup اختیاری؛ اگر True باشد یک فایل پشتیبان تهیه خواهد شد.
• AccessMode اختیاری؛ نحوه دسترسی به کاربرگ را تعیین می نماید. می تواند یکی از مقادیر xlExclusive برای حالت اختصاصی، xlNoChange برای حالت پیش فرض اکسل، xlShared برای حالت اشتراکی، را داشته باشد. اطلاعات بیشتر درباره حالت های مختلف دسترسی در یک فایل اکسل را می توانید در این لینک مشاهده نمایید.
• ConflictResolution اختیاری؛ این پارامتر نحوه برخورد کاربرگ با تعارضات تغییرات حین ذخیره فایل را مشخص می نماید. می تواند یکی از سه مقدار xlLocalSessionChanges (تغییرات کاربران محلی همیشه قبول شوند)، xlOtherSessionChanges (تغییرات کاربران محلی همیشه رد شوند)، xlUserResolution (نمایش کادر محاوره ای به کاربر برای حل تعارض)، داشته باشد.
• AddToMru اختیاری؛ اگر True باشد فایل به لیست فایل های اخیر اضافه خواهد شد.
• TextCodepage اختیاری؛ وقتی که کاربرگ در قالب CSV یا یکی از قالب های متنی ذخیره شود، مشخص کننده کدینگ ذخیره فایل است. بصورت پیش فرض برای همه زبان ها نادیده گرفته می شود و از تنظیمات سیستم در مسیر Control Panel > Region and Language > Location استفاده می نماید.
• TextVisualLayout اختیاری؛ وقتی که کاربرگ در قالب CSV یا یکی از قالب های متنی ذخیره شود، مشخص کننده جهت متن (ltr یا rtl) ذخیره فایل است. بصورت پیش فرض برای همه زبان ها نادیده گرفته می شود و از تنظیمات سیستم در مسیر Control Panel > Region and Language > Location استفاده می نماید.
• Local اختیاری؛ اگر True باشد فایل بدون در نظر گرفتن تنظیمات زبان اکسل ذخیره می شود. اگر False (مقدار پیش فرض) باشد فایل بدون در نظر گرفتن تنظیمات زبان VBA ذخیره می شود.

مثال

ماکروی زیر کاربرگ جدیدی ایجاد می کند و صبر می کند تا کاربر نام فایل جدید را وارد نماید. سپس با استفاده از متد SaveAs کاربرگ را ذخیره می نماید.

Set NewBook = Workbooks.Add 
Do 
    fName = Application.GetSaveAsFilename 
Loop Until fName <> False 
NewBook.SaveAs Filename:=fName

متد SaveCopyAs شیء Workbook

این متد در VBA یک کپی جدید از کاربرگ را ذخیره می نماید. دستور این متد به صورت زیر می باشد:

expression.SaveCopyAs (FileName)

• expression یک متغیر شیء است که اشاره به یک کاربرگ دارد.
• FileName اجباری؛ نام فایل کپی است. می توانید از مسیر کامل فایل نیز استفاده نمایید.

توجه داشته باشید که این متد با متد SaveAs متفاوت می باشد. این متد فقط یک کپی از فایل در مسیر مشخص شده ایجاد می نماید و تغییری در وضعیت فایل فعلی اکسل در حافظه ایجاد نمی کند.

مثال

ماکروی زیر یک کپی از کاربرگ فعال اکسل را در مسیر مشخص شده ذخیره می نماید.

ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"

لیست کامل متدهای شیء Workbook اکسل را می توانید در این صفحه مشاهده نمایید.