Ohranitev VBA makrov in zunanjih povezav pri prepisovanju delovnega zvezka s kodo Delphi

Predstavljajte si opravilo, ki skoraj ne naredi ničesar: odpre mesečni delovni zvezek, vpiše današnji datum v eno celico in ga shrani nazaj. Če to opravilo pogosto izvajate prek storitve v ozadju, se bo prej ali slej pojavila pritožba. Makri so izginili ali pa povezani devizni tečaji zdaj prikazujejo napako #REF!, ekipa za podporo pa je prepričana, da jih je izbrisala vaša koda. V resnici koda ni izbrisala ničesar. Zgodilo se je to, da je bil delovni zvezek z makri shranjen z navadno končnico .xlsx, Excel pa je preprosto sledil pravilom o vrsti vsebine iz standarda ECMA-376: paket, katerega vrsta vsebine ne deklarira VBA, ne more naložiti projekta VBA, ne glede na to, ali so bajti dejansko prisotni v datoteki. Datoteka ni poškodovana, ampak je bila shranjena v stanju, kjer mora Excel določen del vsebine prezreti.

Makri in zunanje povezave so elementi, ki jih avtomatizacija pogosto izgubi iz enakega osnovnega razloga. Obetajo se izven mreže celic, ki se jih koda za urejanje dejansko dotakne, zato jih koda, ki upravlja le vrstice in stolpce, opusti brez kakršne koli operacije brisanja. HotXLS, izvorna knjižnica za Delphi in C++Builder, ki bere in zapisuje datoteke XLS in XLSX brez nameščenega Excela, obravnava ti dve sredstvi kot koristni vsebini, ki ju je treba prenesti naprej, in ne kot podatke, ki se naključno kopirajo. V nadaljevanju je opisano, kaj vsako od teh sredstev potrebuje od vaše poti shranjevanja in kje se garancije končajo.

Zakaj se ti dve sredstvi med prepisovanjem obnašata različno

Projekt VBA je en sam neprosojen binarni podatkovni blok. V paketu OOXML je to datoteka vbaProject.bin, v starejšem formatu BIFF pa je to shramba OLE. Izgubite ga lahko na natanko dva načina: pisalnik ga nikoli ne kopira v izhodno datoteko ali pa izhodna datoteka dobi vrsto, ki to prepoveduje. Vsaka takšna napaka je popolna in tiha. Projekt je bodisi prisoten bodisi ne.

Zunanja povezava sploh ni binarni blok. Je majhen graf povezav: ciljna pot ali URL, ki kaže na drug delovni zvezek, seznam imen listov, ki jih cilj izpostavlja, in neobvezen predpomnilnik zadnjih znanih vrednosti na teh listih, tako da Excel nekaj prikaže, ko cilj ni dosegljiv. Ti trije deli imajo različne življenjske dobe med prepisovanjem, pri čemer lahko knjižnica nekatere zvesto ohrani, druge pa tiho opusti. To asimetrijo je pomembno podrobno razumeti, saj je koda za urejanje celic sama po sebi ne bo razkrila.

Prenos projekta VBA skozi prepise XLSX

Na strani XLSX razred TXLSXWorkbook dobesedno ohrani vsebino makrov. Lastnost VbaProject vsebuje neobdelane bajte datoteke vbaProject.bin znotraj tipa AnsiString, prazen niz pa pomeni, da makrov ni. Poleg tega so na voljo tri operacije: HasVbaProject vrne podatek o prisotnosti projekta, ClearVbaProject ga namerno odstrani, LoadVbaProjectFromFile pa ga naloži iz predloge. Ta zadnji klic je izjemno uporaben, saj omogoča generiranim delovnim zvezkom prevzem standardnega projekta makrov, ne da bi morali skozi celoten proces vleči celotno datoteko predloge.

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');

    // The .xlsm extension is not cosmetic: it selects the
    // macro-enabled content type inside the package.
    Book.SaveAs('monthly-report.xlsm');
  finally
    Book.Free;
  end;
end;

Ključni del je sama vrstica shranjevanja. Delovni zvezek, ki vsebuje projekt VBA, mora biti zapisan s podporo za makre, HotXLS pa to upošteva, če se ciljno ime datoteke konča s končnico .xlsm. Če uporabite končnico .xlsx, Excel makre zavrne, čeprav so bajti fizično prisotni v paketu in bi se lahko pravilno prebrali. Končnica ni le kozmetični popravek; določa vrsto vsebine, ki Excelu pove, da je projekt VBA dovoljen. Večinoma morate le prenesti to vsebino. Če pa želite pogledati vanjo (na primer za izpis imen modulov za revizijsko poročilo), lastnost ParsedVBAProject izpostavi razčlenjen model modulov, medtem ko VbaProject še naprej hrani izvirne neobdelane bajte.

Ponovna uporaba makrov iz starejšega delovnega zvezka XLS

Fasada BIFF odraža to zbirko orodij z enim dodatnim korakom. Funkcija HasVBAProject preveri naloženo datoteko, SaveVBAProjectToFile zapiše shrambo projekta na disk, LoadVBAProjectFromFile pa jo prebere nazaj v drug delovni zvezek. Ta vmesni korak s shranjevanjem v datoteko poenostavi pogosto opravilo posodabljanja: makre preprosto izvozite iz starega modela iz leta 2003 in jih uvozite v sveže ustvarjeno datoteko XLS, ne da bi med izvajanjem potrebovali prvotno predlogo.

var
  Src, Dst: IXLSWorkbook;   // interface references: no manual 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;

Past se skriva v pomnilniškem modelu, ki deluje ravno obratno kot pri razredu XLSX. TXLSWorkbook se upravlja prek vmesnika IXLSWorkbook s številčenjem referenc, zato ga nikoli ne sproščate ročno. Po drugi strani pa je razred XLSX TXLSXWorkbook navaden objekt, ki ga morate zaviti v blok try..finally in ročno sprostiti. Če ti dve konvenciji pomešate v isti enoti, lahko pride do sesutja zaradi dvojnega sproščanja pomnilnika. Velja tudi naslednje pravilo: izvoz in uvoz izvajajte znotraj istega formata datoteke. Shramba projekta BIFF in OOXML datoteka vbaProject.bin sta sorodni, a ne povsem enaki strukturi, zato mora cevovod, ki oddaja makre v obeh formatih, za vsakega hraniti ločeno predlogo.

Zunanje povezave: struktura preživi, predpomnjene vrednosti ne

Pri delovnih zvezkih XLSX knjižnica HotXLS izpostavlja zunanje povezave prek zbirke ExternalLinks. Vsak objekt TXLSXExternalLink vsebuje lastnost Target (pot ali URL oddaljenega delovnega zvezka) in seznam SheetNames z imeni listov, na katere se nanaša. Oboje se v celoti ohrani skozi cikel odpiranja in shranjevanja, povezavo pa lahko ustvarite tudi povsem na novo:

var
  Link: TXLSXExternalLink;
begin
  Link := Book.ExternalLinks.Add('\\fileserver\finance\fx-rates-2026.xlsx');
  Link.SheetNames.Add('FX');

  if Book.ExternalLinks.Count > 0 then
    Writeln(Format('%d external link(s): delivery requires reachable targets',
      [Book.ExternalLinks.Count]));
end;

Omejitev se nahaja en nivo globlje od seznama ciljev. HotXLS prenaša strukturo povezav (cilj in imena listov), ne pa predpomnjenih vrednosti celic, ki jih OOXML hrani v elementu sheetDataSet zunanje povezave. Ta predpomnilnik omogoča Excelu prikaz zadnje znane vrednosti, ko ciljna datoteka ni na voljo, na novo ustvarjeni delovni zvezek pa tega predpomnilnika nima. Posledice tega občuti prejemnik datoteke. Če odprete datoteko, ko cilj ni dosegljiv (npr. računalnik zunaj omrežja VPN ali preimenovana mrežna mapa), se formule, odvisne od te povezave, pretvorijo v napako #REF! ali pa se odpiranje zaustavi zaradi poziva za posodobitev. Iz tega izhajata dve pravili: ne obljubljajte, da bo generirani delovni zvezek brez povezave prikazoval vrednosti iz zunanjih povezav; vrednost ExternalLinks.Count, ki je večja od nič, pa obravnavajte kot pogoj za uspešno dostavo: vsak cilj mora biti dosegljiv s tistega mesta, kjer bo datoteka dejansko odprta.

Kaj bralnik XLS ohrani bajt za bajtom

Za strukture, ki jih ne modelira, ima format BIFF drugačno rešitev: pusti jih natanko takšne, kot so bile najdene. Predpomnilniki in pogledi vrtilnih tabel (družina zapisov SX*), definicije poizvedovalnih tabel (QueryTable), zunanje podatkovne povezave, pogledi po meri, glave slik in zapisi tem (theme records) se prenesejo skozi cikel odpiranja in shranjevanja kot neobdelani bloki zapisov, ne da bi jih knjižnica razčlenjevala ali spreminjala. Same zunanje reference pa se prenašajo prek zapisov EXTERNSHEET in SupBook. Na strani XLS sicer ni tipiziranega vmesnika API za njihovo ustvarjanje na novo, vendar obstoječa povezava urejanje preživi nedotaknjena.

Ohranjanje podatkov bajt za bajtom je zanesljivo jamstvo, ki pa ima svojo past. Ker knjižnica ne bere shranjene strukture, je vaši popravki ne morejo poškodovati. Hkrati pa je prav zaradi tega nič ne posodablja. Če vstavite vrstice skozi območje, na katerega kaže ohranjeni predpomnilnik vrtilne tabele ali poizvedba, bo struktura obdržala prvotne koordinate, podatki pod njo pa se bodo zamaknili. Datoteka bo še vedno veljavna (XML ali BIFF), vendar bodo podatki tiho postali neusklajeni, pri čemer se ne bo sprožilo nobeno opozorilo. Rešitev je v sami zasnovi postavitve: generirane podatke vpisujte na delovne liste, ki ne vsebujejo teh struktur. Gre za enak pristop, s katerim ščitimo zaklenjene in tiskalno nastavljene liste, kot je opisano v članku o zaščiti delovnih listov in nastavitvi strani.

Preverjanje datoteke, ki ste jo dejansko zapisali

Oba načina odpovedi sta med pisanjem tiha, zato je edino pravo potrdilo ponovno odpiranje izhodne datoteke in ne slepo zaupanje kodi, ki jo je ustvarila. Tri preverjanja pokrivajo skupaj skoraj vse. Ponovno odprite datoteko in potrdite, da HasVbaProject še vedno vrne true, če so bili makri pričakovani; to v enem koraku preveri tako prenos vsebine kot pravilnost končnice. Preverite ExternalLinks.Count in ga primerjajte s številom pred prepisovanjem. Na koncu datoteko odprite še v Excelu z onemogočenimi makri: Excelovo preverjanje vrst vsebine je namreč strožje od katere koli knjižnice, poleg tega pa je Excel program, s katerim bodo datoteko ocenjevale vaše stranke.

Nič od tega ne zahteva celotnega razčlenjevanja ob uvozu. Ko delovni zvezki prihajajo v velikem številu in želite le ugotoviti, kateri vsebujejo upravljano vsebino, vam preproste metode iz članka o seznamu listov in hitrem pregledu omogočajo, da datoteke z makri in povezavami preusmerite v strožji cevovod še pred začetkom prepisovanja.

Nekaj vprašanj se pojavlja dovolj pogosto, da nanje odgovorimo neposredno. Knjižnica HotXLS nikoli ne izvaja makrov, ki jih ohrani: v njej ni okolja za izvajanje VBA, temveč le mehanizem za shranjevanje, kopiranje, izvoz in uvoz projekta kot podatkov. Na strežniku je to pomembna varnostna lastnost, saj morebitna zlonamerna koda med potovanjem skozi cevovod ostane neaktivna, dokler uporabnik datoteke ne odpre v namiznem Excelu in ročno omogoči vsebine. Pretvorba iz .xlsm v .xlsx ob ohranitvi makrov ni mogoča, kar je pravilo formata in ne omejitev knjižnice: vrsta vsebine .xlsx označuje delovni zvezek brez makrov, zato je edina pravilna možnost ohranitev končnice .xlsm ali klic ClearVbaProject, s čimer pošljete datoteko brez makrov. Tiho preimenovanje ne bo zadovoljilo nikogar. Če pa povezane celice po prepisovanju prikazujejo #REF!, je vzrok manjkajoči predpomnilnik vrednosti: nova datoteka vsebuje cilj, ne pa tudi predpomnjenih številk, zato mora Excel ob odpiranju razrešiti vir, kar pa zaradi nedosegljive ali relativne poti spodleti. Zagotovite, da je cilj dosegljiv, ali pa pred dostavo v celice zapišite izračunane vrednosti in povsem odpravite odvisnost.

Urejanje delovnih zvezkov drugih avtorjev je večinoma delo ohranjanja elementov, ki jih niste napisali in jih morda ne razumete v celoti. Zmožnosti prenašanja projektov VBA in zunanjih povezav so del knjižnice HotXLS Component za Delphi in C++Builder, skupaj z revizijskimi lastnostmi, ki vam omogočajo zaznavanje upravljane vsebine takoj, ko datoteka prispe.