حفظ ماکروهای VBA و پیوندهای خارجی هنگام بازنویسی کتاب کار توسط کد دلفی

وظیفه‌ای را در نظر بگیرید که تقریباً هیچ کاری انجام نمی‌دهد: یک کتاب کار ماهانه را باز می‌کند، تاریخ امروز را در یک سلول می‌نویسد و آن را ذخیره می‌نماید. این کار را به دفعات کافی از طریق یک سرویس اجرا کنید تا در نهایت یک شکایت دریافت کنید: ماکروها ناپدید شده‌اند یا نرخ‌های ارز پیوند داده شده اکنون با خطای #REF! خوانده می‌شوند و تیم عملیاتی متقاعد شده است که کد شما آن‌ها را حذف کرده است. کد شما چیزی را حذف نکرده است. آنچه معمولاً اتفاق افتاده این است که یک کتاب کار فعال‌شده با ماکرو تحت نام ساده .xlsx خارج شده و اکسل از قوانین نوع محتوای ECMA-376 پیروی کرده است: بسته‌ای که نوع محتوای آن ماکروهای VBA را اعلام نکند، نمی‌تواند پروژه VBA را بارگذاری کند، بدون توجه به اینکه بایت‌های آن در همان‌جا قرار دارند. فایل خراب نشده است، بلکه به حالتی تغییر نام یافته که اکسل ملزم به نادیده گرفتن بخشی از آن است

ماکروها و پیوندهای کتاب کار خارجی دو موردی هستند که اتوماسیون با بیشترین احتمال آن‌ها را از دست می‌دهد، به همان دلیل اساسی. هر دو خارج از شبکه سلولی قرار دارند که کد ویرایشگر واقعاً لمس می‌کند، بنابراین منطق کدی که بر اساس سطرها و ستون‌ها کار می‌کند، آن‌ها را بدون صدور دستور حذف از دست خواهد داد. HotXLS یک کتابخانه بومی دلفی و C++Builder است که بدون نیاز به نصب اکسل، فایل‌های XLS و XLSX را می‌خواند و می‌نویسد، و با هر دو دارایی به عنوان داده‌های حامل رفتار می‌کند که عمداً آن‌ها را حمل می‌نماید نه داده‌هایی که به طور تصادفی کپی می‌کند. در ادامه آنچه هر کدام از مسیر ذخیره شما نیاز دارند و جایی که ضمانت‌ها متوقف می‌شوند آمده است

چرا این دو دارایی در بازنویسی رفتار متفاوتی دارند

چرا این دو دارایی در بازنویسی رفتار متفاوتی دارند

یک پروژه VBA یک باینری مبهم است. در یک بسته OOXML، این فایل vbaProject.bin است؛ در یک فایل قدیمی BIFF، یک رسانه ذخیره‌سازی OLE است. دقیقاً دو راه برای از دست دادن آن وجود دارد: نویسنده هرگز آن را در خروجی کپی نمی‌کند، یا خروجی نوع فایلی را دریافت می‌کند که آن را ممنوع می‌سازد. هر یک از این حالت‌های شکست کامل و بی‌صدا هستند. پروژه یا وجود دارد یا وجود ندارد

یک پیوند خارجی اصلاً یک باینری (blob) نیست. بلکه یک نمودار کوچک از روابط است: یک مسیر یا URL مقصد که به کتاب کار دیگری اشاره می‌کند، لیست نام برگه‌هایی که آن مقصد ارائه می‌دهد و یک کش اختیاری از آخرین مقادیری که در آن برگه‌ها دیده شده است تا اکسل بتواند زمانی که مقصد آفلاین است چیزی نشان دهد. این سه بخش در طول بازنویسی طول عمرهای متفاوتی دارند و یک کتابخانه می‌تواند برخی را با دقت حفظ کند در حالی که برخی دیگر را بی‌صدا حذف نماید. این عدم تقارن بخشی است که ارزش دارد در مورد آن دقیق شویم، زیرا هیچ چیز در کد ویرایش سلول آن را نمایان نخواهد کرد

حمل یک پروژه VBA از طریق بازنویسی XLSX

در سمت XLSX، کلاس TXLSXWorkbook محتوای ماکرو را کلمه به کلمه نگه می‌دارد. ویژگی VbaProject بایت‌های خام vbaProject.bin را در یک AnsiString نگه می‌دارد و یک رشته خالی نشان‌دهنده نبود ماکرو در مدل است. در کنار آن سه عملیات وجود دارد: HasVbaProject مشخص می‌کند که آیا پروژه‌ای وجود دارد یا خیر، ClearVbaProject آن را عمداً حذف می‌کند و LoadVbaProjectFromFile پروژه‌ای را که از یک قالب استخراج شده است تزریق می‌نماید. فراخوانی آخر ارزش بیشتری از ظاهرش دارد. این ویژگی به کتاب‌های کاری تولید شده اجازه می‌دهد تا یک پروژه ماکروی استاندارد را بدون کشیدن کل فایل قالب در طول خط لوله دریافت کنند

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 'Refreshed ' + DateTimeToStr(Now);

    Book.LoadVbaProjectFromFile('macros\vbaProject.bin');
    if not Book.HasVbaProject then
      raise Exception.Create('VBA payload failed to load');

    // پسوند .xlsm تزیینی نیست: نوع محتوای فعال‌شده با ماکرو
    // را در داخل بسته انتخاب می‌کند.
    Book.SaveAs('monthly-report.xlsm');
  finally
    Book.Free;
  end;
end;

خط ذخیره جایی است که کل مشکل در آن می‌چرخد. یک کتاب کار حاوی پروژه VBA باید با معناشناسی فعال‌شده با ماکرو نوشته شود و HotXLS زمانی که نام مقصد به .xlsm ختم گردد، آن‌ها را اعمال می‌کند. اگر به جای آن پسوند .xlsx بدهید، اکسل ماکروها را رد می‌کند، حتی اگر بایت‌های آن به طور فیزیکی در بسته وجود داشته باشند و به خوبی غیرسریال‌سازی شوند. پسوند تزیینی نیست؛ بلکه نوع محتوایی را انتخاب می‌کند که به اکسل می‌گوید یک پروژه VBA مجاز به وجود داشتن است. بیشتر اوقات فقط نیاز به انتقال داده‌های اصلی دارید. هنگامی که نیاز به خواندن آن دارید، مثلاً برای فهرست کردن نام ماژول‌ها برای یک گزارش حسابرسی، ویژگی ParsedVBAProject یک مدل ماژول تجزیه‌شده را نشان می‌دهد در حالی که VbaProject بایت‌های دست‌نخورده اصلی باقی می‌ماند

استفاده مجدد از ماکروها از کتاب‌های کاری قدیمی XLS

رابط BIFF آن مجموعه ابزار را با یک مرحله اضافی منعکس می‌کند. متد HasVBAProject یک فایل بارگذاری‌شده را بررسی می‌کند، SaveVBAProjectToFile حافظه پروژه را روی دیسک می‌نویسد و LoadVBAProjectFromFile آن را در کتاب کار دیگری بازخوانی می‌نماید. مسیر غیرمستقیم از طریق یک فایل، یک کار رایج مدرن‌سازی را ساده می‌کند: ماکروها را از یک مدل مربوط به دوران سال 2003 خارج کرده و آن‌ها را در خروجی جدید XLS قرار دهید، بدون اینکه در زمان اجرا به قالب اصلی نیاز باشد

var
  Src, Dst: IXLSWorkbook;   // مراجع اینترفیس: بدون نیاز به Free دستی
begin
  Src := TXLSWorkbook.Create;
  if Src.Open('legacy-model.xls') <= 0 then
    raise Exception.Create('Cannot open legacy model');
  if Src.HasVBAProject then
    Src.SaveVBAProjectToFile('extracted-vba.bin');

  Dst := TXLSWorkbook.Create;
  Dst.Sheets.Add.Name := 'Report2026';
  Dst.LoadVBAProjectFromFile('extracted-vba.bin');
  Dst.SaveAs('report-with-macros.xls');
end;

مدل حافظه در اینجا تله است و برعکس کلاس XLSX عمل می‌کند. TXLSWorkbook از طریق اینترفیس شمارش‌گر مرجع IXLSWorkbook نگه داشته می‌شود، بنابراین شما هرگز آن را به طور دستی آزاد نمی‌کنید؛ اما کلاس XLSX TXLSXWorkbook یک شیء ساده است که باید آن را در try..finally قرار دهید و آزاد کنید. ترکیب این دو روش در یک یونیت منجر به کرش‌های ناشی از آزادسازی دوگانه (double-free) می‌شود. مرز دیگری که ارزش احترام گذاشتن دارد: استخراج و تزریق را در یک قالب فایل واحد نگه دارید. حافظه پروژه BIFF و فایل vbaProject.bin در OOXML هم‌خانواده هستند اما یک کانتینر نیستند، و خط لوله‌ای که باید ماکروها را در هر دو قالب منتشر کند، باید یک قالب ماکروی جداگانه برای هر کدام نگه دارد

پیوندهای خارجی: نقشه باقی می‌ماند، مقادیر کش‌شده خیر

برای کتاب‌های کاری XLSX، کامپوننت HotXLS پیوندهای خارجی را از طریق مجموعه ExternalLinks در دسترس قرار می‌دهد. هر TXLSXExternalLink دارای یک Target (مسیر یا URL کتاب کار راه دور) به همراه لیستی از SheetNames است که نام برگه‌های ارجاع‌داده‌شده را مشخص می‌کند. هر دو در یک چرخه باز کردن و ذخیره سالم می‌مانند و همچنین می‌توانید یک پیوند را از ابتدا بسازید:

مرز کار در یک سطح عمیق‌تر از لیست مقصد قرار دارد. HotXLS نقشه پیوند (یعنی مقصد و نام برگه‌ها) را منتقل می‌کند، اما مقادیر سلول‌های کش‌شده را که OOXML در عنصر sheetDataSet پیوند نگه می‌دارد، تجزیه یا بازنویسی نمی‌کند. آن کش چیزی است که به اکسل اجازه می‌دهد یک شماره آخرین بار دیده شده را زمانی که فایل منبع آفلاین است نشان دهد، و یک کتاب کار تولید شده فاقد آن ارسال می‌شود. نتیجه متوجه گیرنده خواهد بود و نه شما. اگر چنین فایلی را در جایی باز کنید که مقصد غیرقابل دسترس است (یک لپ‌تاپ خارج از VPN یا اشتراکی که نام آن تغییر کرده)، فرمول‌هایی که به پیوند وابسته هستند با خطای #REF! حل می‌شوند یا در پشت پیام به‌روزرسانی متوقف می‌گردند. بنابراین دو قانون از این موضوع حاصل می‌شود: قول ندهید که کتاب کار تولید شده مقادیر پیوند داده شده خارجی را به صورت آفلاین نمایش خواهد داد. و مقدار غیرصفر ExternalLinks.Count را به عنوان یک پیش‌شرط تحویل بخوانید و نه به عنوان یک ویژگی: هر مقصد باید از هر جایی که فایل در آن باز می‌شود، قابل دسترس باشد

آنچه خواننده XLS بایت به بایت حفظ می‌کند

برای ساختارهایی که مدل‌سازی نمی‌کند، سمت BIFF پاسخ متفاوتی دارد: آن‌ها را دقیقاً همان‌طور که یافت شده‌اند رها کنید. کش‌های پیوت (Pivot caches) و نماهای پیوت (خانواده رکوردهای SX*)، تعاریف QueryTable، اتصالات داده‌های خارجی، نماهای سفارشی، تصاویر هدر و رکوردهای تم همگی از یک چرخه باز کردن و ذخیره‌سازی به عنوان بلوک‌های رکورد خام، بدون تجزیه و تغییر عبور می‌کنند. مراجع خارجی خود از طریق رکوردهای زیربنایی EXTERNSHEET و SupBook منتقل می‌شوند. هیچ API ایجاد تایپ‌شده‌ای برای آن‌ها در سمت XLS وجود ندارد، اما یک پیوند موجود بدون تغییر در ویرایش باقی می‌ماند

حفظ بایت به بایت یک تضمین واقعی با لبه تیز است. از آنجا که هیچ چیز یک ساختار حفظ شده را نمی‌خواند، ویرایش‌های شما نمی‌توانند آن را خراب کنند. به همین دلیل، هیچ چیز آن را به‌روزرسانی نیز نمی‌کند. اگر سطرهایی را در ناحیه‌ای وارد کنید که کش پیوت یا جدول پرس‌وجو حفظ‌شده به آن اشاره می‌کند، ساختار مختصات اصلی خود را حفظ می‌کند در حالی که داده‌های زیر آن جابجا می‌شوند. فایل همچنان XML یا BIFF معتبر است؛ اما معنا بی سر و صدا از هماهنگی خارج شده است و هیچ خطایی برای اطلاع به شما رخ نخواهد داد. چیدمان قابل دفاع این است که ویرایش‌های تولید شده را روی برگه‌هایی نگه دارید که فاقد ساختارهای حفظ شده هستند، که این همانِ قاعده‌ای است که از برگه‌های قفل‌شده و پیکربندی‌شده برای چاپ در مقاله ما درباره محافظت از برگه و تنظیمات صفحه محافظت می‌کند

تایید فایلی که در واقع نوشتید

هر دو حالت شکست در زمان نوشتن بی‌صدا هستند، بنابراین تاییدیه مهم با باز کردن مجدد خروجی انجام می‌شود تا اعتماد به کدی که آن را تولید کرده است. سه بررسی تقریباً همه چیز را پوشش می‌دهند: فایل را دوباره باز کنید و تایید کنید که HasVbaProject در هر زمان که انتظار ماکرو می‌رفت همچنان true برمی‌گرداند، که این موضوع ریزش داده‌های اصلی و پسوند اشتباه را در یک آزمایش واحد ثبت می‌کند. مقدار ExternalLinks.Count را بخوانید و آن را با تعداد قبل از بازنویسی مقایسه کنید. سپس فایل را یک بار در اکسل با ماکروهای غیرفعال باز کنید، زیرا اعتبارسنجی نوع محتوای اکسل سخت‌گیرانه‌تر از هر کتابخانه‌ای است، و اکسل برنامه‌ای است که مشتریان شما فایل را با آن قضاوت خواهند کرد

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

چند سوال به دفعات کافی مطرح می‌شوند که مستقیماً به آن‌ها پاسخ داده شود. HotXLS هرگز ماکروهایی را که حفظ می‌کند اجرا نمی‌کند: هیچ محیط اجرای VBA در کتابخانه وجود ندارد، بلکه فقط مکانیزمی برای ذخیره، کپی، استخراج و تزریق پروژه به عنوان داده وجود دارد. روی یک سرور، این یک ویژگی امنیتی ارزشمند است، زیرا یک ماکروی مخرب که از خط لوله عبور می‌کند تا زمانی که یک اکسل دسکتاپ فایل را باز نکند و کاربر محتوا را فعال ننمایند، غیرفعال باقی می‌ماند. تبدیل یک فایل .xlsm به .xlsx و نگه‌داشتن ماکروها امکان‌پذیر نیست و این قانون خود فرمت است و نه محدودیت کتابخانه: نوع محتوای .xlsx یک کتاب کار بدون ماکرو را اعلام می‌کند، بنابراین تنها خروجی‌های صادقانه ماندن در حالت .xlsm یا فراخوانی ClearVbaProject و ارسال فایلی است که واقعاً فاقد آن است. تغییر نام بی‌صدا تنها انتخابی است که هیچ‌کس را راضی نمی‌کند. و هنگامی که سلول‌های پیوند داده شده پس از بازنویسی خطای #REF! را نشان می‌دهند، علت آن نبود کش مقداری است که در بالا بحث شد: فایل جدید مقصد را حمل می‌کند اما نه شماره‌های کش‌شده را، بنابراین اکسل باید منبع را در زمان باز کردن حل کند و یک مسیر غیرقابل دسترس یا نسبی به محیط آن را ناکام می‌گذارد. یا تضمین کنید که مقصد قابل دسترس است یا مقادیر محاسبه‌شده را قبل از تحویل در سلول‌ها بنویسید و وابستگی را کاملاً حذف کنید

ویرایش کتاب‌های کاری دیگران بیشتر کار حفظ چیزهایی است که شما ننوشته‌اید و کاملاً درک نمی‌کنید. امکانات رفت و برگشت VBA و پیوند خارجی که در اینجا توضیح داده شد، همراه با کامپوننت HotXLS برای دلفی و C++Builder در کنار ویژگی‌های حسابرسی ارائه می‌شود که به شما امکان می‌دهد محتوای تحت نظارت را در لحظه ورود فایل تشخیص دهید