HotXLS: VBA makrók és külső hivatkozások megőrzése

Vegyünk egy feladatot, amely szinte semmit nem csinál: megnyit egy havi munkafüzetet, beírja a mai dátumot egyetlen cellába, majd visszamenti. Futtassa ezt elég sokszor egy szolgáltatásban, és a panasz előbb-utóbb megérkezik. A makrók eltűntek, vagy a hivatkozott árfolyamok helyén most #REF! áll, az üzemeltetés pedig meg van győződve róla, hogy az Ön kódja törölte őket. Nem törölt semmit. Ami rendszerint történik: egy makróbarát munkafüzet sima .xlsx néven ment ki, az Excel pedig az ECMA-376 tartalomtípus-szabályait követte: az a csomag, amelynek tartalomtípusa nem jelent be VBA-t, nem tölthet be VBA-projektet, függetlenül attól, hogy a bájtok ott ülnek-e benne. A fájl nem romlott el. Olyan állapotba nevezték át, amelyben az Excel köteles figyelmen kívül hagyni egy részét

A makrók és a külső munkafüzet-hivatkozások az a két dolog, amelyet az automatizálás a legmegbízhatóbban elveszít, méghozzá ugyanabból a mögöttes okból. Mindkettő azon a cellarácson kívül él, amelyet a szerkesztő kód valójában megérint, ezért a sorokban és oszlopokban gondolkodó kód úgy ejti el őket, hogy soha nem ad ki törlési parancsot. A HotXLS natív Delphi és C++Builder könyvtár, amely telepített Excel nélkül olvas és ír XLS és XLSX fájlokat, és mindkét vagyontárgyat szándékosan hordozott rakományként kezeli, nem pedig véletlenül átmásolt adatként. Az alábbiakban az következik, mire van szüksége mindkettőnek az Ön mentési útvonalától, és hol érnek véget a garanciák

Miért viselkedik eltérően ez a két vagyontárgy újraíráskor

A VBA-projekt egyetlen átlátszatlan bináris. OOXML-csomagban a vbaProject.bin fájl; régi BIFF-fájlban egy OLE-tároló. Pontosan kétféleképpen lehet elveszíteni: az író soha nem másolja be a kimenetbe, vagy a kimenet olyan fájltípust kap, amely tiltja. Mindkét hiba teljes és néma. A projekt vagy megvan, vagy nincs

A külső hivatkozás ezzel szemben egyáltalán nem blob. Kapcsolatok kis gráfja: egy másik munkafüzetre mutató célútvonal vagy URL, azoknak a munkalapneveknek a listája, amelyeket a cél közzétesz, továbbá az adott munkalapokon utoljára látott értékek nem kötelező gyorsítótára, hogy az Excel mutathasson valamit, amikor a cél nem érhető el. Ennek a három résznek eltérő az élettartama egy újraírás alatt, és egy könyvtár némelyiket hűen megőrizheti, míg másokat csendben elejt. Éppen ez az aszimmetria az, amit érdemes pontosan érteni, mert a cellaszerkesztő kódban semmi nem hozza a felszínre

VBA-projekt átvitele egy XLSX-újraíráson

Az XLSX oldalon a TXLSXWorkbook szó szerint megtartja a makró-rakományt. A VbaProject tulajdonság a nyers vbaProject.bin bájtokat tárolja egy AnsiString értékben, és az üres sztring az a mód, ahogyan a modell közli: nincsenek makrók. Köré három művelet épül: a HasVbaProject megválaszolja, jelen van-e projekt, a ClearVbaProject szándékosan eltávolítja, a LoadVbaProjectFromFile pedig beilleszt egyet, amelyet egy sablonból nyertek ki. Ez az utolsó hívás többet ér, mint amennyinek látszik. Lehetővé teszi, hogy a generált munkafüzetek úgy vegyenek át egy szabványos makróprojektet, hogy nem kell teljes sablonfájlt végigvonszolni a folyamaton

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

    // Az .xlsm kiterjesztés nem kozmetika: ez választja ki
    // a csomagon belüli makróbarát tartalomtípust.
    Book.SaveAs('monthly-report.xlsm');
  finally
    Book.Free;
  end;
end;

A mentési sor az, ahol az egész probléma eldől. A VBA-projektet tartalmazó munkafüzetet makróbarát szemantikával kell kiírni, a HotXLS pedig akkor alkalmazza ezt, ha a célnév .xlsm végződésű. Adjon meg helyette .xlsx nevet, és az Excel visszautasítja a makrókat, hiába vannak fizikailag jelen a bájtok a csomagban, és hiába lennének szabályosan visszafejthetők. A kiterjesztés nem dísz; azt a tartalomtípust választja ki, amely közli az Excellel, hogy VBA-projekt egyáltalán létezhet. Legtöbbször csak át kell vinnie a rakományt. Amikor bele is kell olvasnia – mondjuk hogy modulneveket soroljon fel egy auditjelentéshez –, a ParsedVBAProject elemzett modulmodellt kínál, miközben a VbaProject marad az eredeti, érintetlen bájtsor

Makrók újrahasznosítása régi XLS-munkafüzetekből

A BIFF-homlokzat ugyanezt az eszközkészletet tükrözi egy plusz lépéssel. A HasVBAProject megvizsgál egy betöltött fájlt, a SaveVBAProjectToFile lemezre írja a projekttárolót, a LoadVBAProjectFromFile pedig visszaolvas egyet egy másik munkafüzetbe. A fájlon keresztüli kerülőút egyszerűvé tesz egy gyakori modernizációs feladatot: emelje ki a makrókat egy 2003-as korszakból származó modellből, és ültesse be őket frissen generált XLS-kimenetbe, futásidejű eredeti sablon nélkül

var
  Src, Dst: IXLSWorkbook;   // interfészhivatkozások: nincs kézi 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;

A memóriamodell itt a csapda, és ellentétesen működik az XLSX-osztályéhoz képest. A TXLSWorkbook a referenciaszámlált IXLSWorkbook interfészen keresztül él, tehát soha nem szabadítja fel kézzel; az XLSX-es TXLSXWorkbook viszont sima objektum, amelyet try..finally blokkba kell csomagolnia és fel kell szabadítania. Keverje a két konvenciót egyetlen unitban, és jönnek a dupla felszabadításból eredő összeomlások. Még egy határ, amelyet érdemes tiszteletben tartani: a kinyerést és a beillesztést egyetlen fájlformátumon belül tartsa. A BIFF-projekttároló és az OOXML vbaProject.bin unokatestvérek, nem ugyanaz a konténer, ezért annak a folyamatnak, amelynek mindkét formátumban makrót kell kibocsátania, formátumonként külön makrósablont érdemes tartania

Külső hivatkozások: a térkép túléli, a gyorsítótárazott értékek nem

XLSX-munkafüzetek esetén a HotXLS az ExternalLinks gyűjteményen keresztül teszi elérhetővé a külső hivatkozásokat. Minden TXLSXExternalLink hordoz egy Target értéket, a távoli munkafüzet útvonalát vagy URL-jét, továbbá egy SheetNames listát, amely megnevezi a hivatkozott munkalapokat. Mindkettő sértetlenül túlél egy megnyitás–mentés ciklust, és hivatkozást a nulláról is felépíthet:

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;

A határ egy szinttel mélyebben húzódik, mint a céllista. A HotXLS oda-vissza viszi a hivatkozástérképet, vagyis a célt és a munkalapneveket, de nem elemzi és nem írja újra azokat a gyorsítótárazott cellaértékeket, amelyeket az OOXML a hivatkozás sheetDataSet elemében tart. Ez a gyorsítótár az, aminek köszönhetően az Excel megmutathat egy utoljára ismert számot, amikor a forrásfájl nem érhető el, a generált munkafüzet pedig enélkül kerül kiszállításra. A következmény a címzettet éri, nem Önt. Nyisson meg egy ilyen fájlt ott, ahol a cél elérhetetlen – egy VPN-en kívüli laptopon vagy egy időközben átnevezett megosztáson –, és a hivatkozástól függő képletek #REF! értékre oldódnak fel, vagy megakadnak egy frissítési kérdés mögött. Ebből két szabály következik. Ne ígérje meg, hogy egy generált munkafüzet offline is megjeleníti a külsőleg hivatkozott értékeit. A nem nulla ExternalLinks.Count értéket pedig kiszállítási előfeltételként olvassa, ne funkcióként: minden célnak elérhetőnek kell lennie onnan, ahol a fájlt ténylegesen meg fogják nyitni

Mit őriz meg az XLS-olvasó bájtról bájtra

Azoknál a szerkezeteknél, amelyeket nem modellez, a BIFF-oldal más választ ad: hagyja őket pontosan úgy, ahogyan találta. A kimutatás-gyorsítótárak és kimutatásnézetek (az SX* rekordcsalád), a QueryTable-definíciók, a külső adatkapcsolatok, az egyéni nézetek, a fejlécképek és a témarekordok mind nyers rekordblokkként, elemzetlenül és módosítatlanul jutnak át egy megnyitás–mentés cikluson. Maguk a külső hivatkozások a mögöttes EXTERNSHEET és SupBook rekordokon keresztül járják meg az oda-vissza utat. Az XLS oldalon nincs rájuk típusos létrehozó API, de a meglévő hivatkozás érintetlenül túléli a szerkesztést

A bájtról bájtra való megőrzés valódi garancia, éles peremmel. Mivel semmi nem olvassa a megőrzött szerkezetet, az Ön szerkesztései nem tudják megrontani. Ugyanebből az okból semmi nem is frissíti. Szúrjon be sorokat egy olyan tartományon keresztül, amelyre egy megőrzött kimutatás-gyorsítótár vagy lekérdezési tábla mutat, és a szerkezet megtartja az eredeti koordinátáit, miközben az alatta lévő adat elmozdul. A fájl továbbra is érvényes XML vagy BIFF; a jelentés csendben kicsúszott a helyéről, és semmilyen hiba nem jelzi. A védhető elrendezés az, hogy a generált szerkesztéseket olyan munkalapokon tartja, amelyeken nincsenek megőrzött szerkezetek – ez ugyanaz a fegyelem, amely a zárolt és nyomtatásra beállított munkalapokat védi a munkalapvédelemről és az oldalbeállításról szóló cikkünkben

A ténylegesen kiírt fájl ellenőrzése

Mindkét hibamód néma írási időben, ezért az az állítás számít, amelyet a kimenet újranyitásával tesz, nem pedig az, hogy megbízik az azt előállító kódban. Három ellenőrzés csaknem mindent lefed. Nyissa meg újra a fájlt, és győződjön meg róla, hogy a HasVbaProject továbbra is igazat ad vissza mindenütt, ahol makrókat vártak – ez egyetlen teszttel elkapja az elejtett rakományt és a rossz kiterjesztést is. Olvassa ki az ExternalLinks.Count értéket, és vesse össze az újraírás előtti darabszámmal. Utána nyissa meg a fájlt egyszer az Excelben, letiltott makrókkal, mert az Excel tartalomtípus-ellenőrzése szigorúbb bármelyik könyvtárénál, és az Excel az a program, amely alapján az ügyfelei megítélik a fájlt

Ehhez semmi nem igényel teljes elemzést a bemeneti oldalon. Amikor a munkafüzetek tömegével érkeznek, és csak azt kell eldöntenie, melyik hordoz szabályozott tartalmat, a munkalapok listázásáról és a könnyűsúlyú munkafüzet-vizsgálatról szóló cikkünkben leírt könnyed szondázással a makrót hordozó és a hivatkozásokat tartalmazó fájlokat szigorúbb folyamatba irányíthatja, még mielőtt az első újraírás elindulna

Néhány kérdés elég gyakran felmerül ahhoz, hogy közvetlenül megválaszoljuk. A HotXLS soha nem futtatja azokat a makrókat, amelyeket megőriz: a könyvtárban nincs VBA-futtatókörnyezet, csak az a gépezet, amely a projektet adatként tárolja, másolja, kinyeri és beilleszti. Kiszolgálón ez kimondásra érdemes biztonsági tulajdonság, mivel egy ellenséges makró a folyamaton áthaladva tétlen marad addig, amíg egy asztali Excel meg nem nyitja a fájlt, és a felhasználó nem engedélyezi a tartalmat. Egy .xlsm fájlt .xlsx formátumba konvertálni a makrók megtartásával nem lehetséges, és ez a formátum szabálya, nem könyvtári korlát: az .xlsx tartalomtípus makrómentes munkafüzetet jelent be, tehát az egyetlen becsületes kimenet az, ha marad az .xlsm, vagy meghívja a ClearVbaProject műveletet, és olyan fájlt szállít, amelyben tényleg nincs makró. A néma átnevezés az az egyetlen választás, amely senkinek nem jó. Amikor pedig a hivatkozott cellák #REF! értéket mutatnak egy újraírás után, az ok a fentebb tárgyalt hiányzó értékgyorsítótár: az új fájl a célt hordozza, a gyorsítótárazott számokat nem, ezért az Excelnek megnyitáskor kell feloldania a forrást, amit egy elérhetetlen vagy környezetfüggő útvonal meghiúsít. Vagy garantálja a cél elérhetőségét, vagy kiszállítás előtt kiszámolt értékeket ír a cellákba, és teljesen megszünteti a függőséget

Mások munkafüzeteinek szerkesztése nagyrészt abból áll, hogy megőrzünk olyasmit, amit nem mi írtunk, és nem is értünk teljesen. Az itt leírt VBA- és külsőhivatkozás-átviteli szolgáltatások a Delphihez és C++Builderhez készült HotXLS Delphi Component részeként érkeznek, azokkal az auditálási tulajdonságokkal együtt, amelyekkel a szabályozott tartalmat abban a pillanatban észlelheti, amint egy fájl megérkezik