Kisebb kitekintés és más területeken való kalandozás után a mai posztban térjünk vissza a VBA-hoz, amelyet legalább alapszinten mindenkinek célszerű megismernie, aki sokat dolgozik Excelben, hiszen nem kell programozónak lenni ahhoz, hogy mondjuk egy állandóan elvégzett Pivot-táblás copy-paste műveletet automatizálni tudjunk.

Szóval a mai kérdés a következő kis tábla köré összpontosul:

Tegyük fel, hogy kirándultunk egy nagyobbat az országban, felkerestünk néhány települést és az ezeken a helyeken megvásárolt dolgokról vezettünk egy kis Excel-táblázatot, de arra már nem figyeltünk, hogy mivel jelöltük az egyes cellákban, hogy mit vásároltunk, tehát szöveg, betű vagy éppen szám egyaránt található a táblában. A kérés annak meghatározása lenne, hogy a termékek egységárát figyelembe véve, pontosan mennyit költöttünk, ha a cellákba beírt érték nem számít, csak az, hogy írtunk-e be valamit vagy sem - értelemszerűen ha írtunk valamit, azt tekintsük egynek.

A feladvány jópár úton megoldható, de VBA-ban sem tartozik a nehéz feladatok közé, ezért aki még csak az érdeklődő fázisban tart, ő mindenképpen próbálja saját maga megoldani.

Alt + F11 billentyűkombinációval ugorjunk át a VBA-editorba és kezdjünk is neki Szamitas() nevű szubrutinunk összedobásának, amelyet most már biztosan a változók deklarálásával kezdünk. Tekintve, hogy tartományról van szó és ciklusra lesz szükségünk, célszerű a tartománynak is létrehozni egy változót (én ezt most rng néven Range típussal meg is teszem) illetve kell egy másik változó, amelyben a kalkulációnk eredményét tároljuk a ciklus során (én ezt a megszokott i néven Integer típussal alkottam meg). Szóval megvannak a változóink, az rng névre hallgatót fel is tudjuk tölteni táblázatunk paramétereivel.

Hangsúlyozom, egyszerű feladványról van szó, tehát aki érdeklődik VBA-ismeretek iránt, az mindenképpen kapcsolódjon be és saját maga fejezze be a megoldást - a saját verziójában.

Szóval most jön a ciklusunk, amelyet én általában simán szövegként gondolok végig (egy-két írónak volt hasonló módon megírt oktatási anyaga), azaz most minden egyes cellát nézzen meg a ciklusunk a tartományban (For Each cell in rng) és ha a cella (If Cell.Value) nem üres (cell.Value <> "") akkor (Then) ezt a cellát tegye az aktív cellánkká (cell.Activate). Majd ezután a fentebb deklarált i változó legyen egyenlő a korábban benne lévő értékkel, hozzáadva a most aktív cellánk oszlopának második sorában szereplő értéket (i = i + Cells(2, ActiveCell.Column) - persze itt is simán elkerülhetjük a konstans sorszám használatát, ha akarjuk. Ezután zárjuk le az If feltételvizsgálatot és ugorjunk a következő cellára a tartományban.

Innen már csak az van hátra, hogy kiírassuk az eredményt a képernyőre egy MsgBox segítségével, illetve kiürítsük i változónkat és bezárjuk a szubrutint:

És ezután már tuningolhatjuk, ahogy akarjuk, innen könnyen be tudjuk állítani akár azt is, hogy ha szám van, akkor ne csak egyszer számolja az egységárat, hanem a számmal szorozza meg, de akár kitölthetjük az üres cellákat is, gombhoz is hozzárendelhetjük, de akárhogy is teszünk, a fentinek meg is van szépen az eredménye:

Szövegként a kód:

Sub Szamitas()
Dim rng As Range
Dim i As Integer
Set rng = Range("B3:D8")

For Each cell In rng
If cell.Value <> "" Then
cell.Activate
i = i + Cells(2, ActiveCell.Column)
End If
Next
MsgBox i
i = 0
End Sub

A mai posztban egy-két olyan Outlook 2013-at érintő kérdésre fogom megadni a választ, amelyek az elmúlt hetek során merültek fel és amelyek egyike sem ér egy külön posztot (hiszen nem beszélünk komplex problémákról), de talán ha egy helyen összegyűjtve kerülnek bemutatásra, akkor valakinek segítség lehet még a jövőben valamelyik javaslat.

1. Van-e mód arra, hogy Outlookban egy mailbox esetében ne az olvasatlan levelek számát, hanem az összes levelet mutassuk ki a folder neve mögött zárójelben?

Első lépésként kattintsunk az adott mailboxunk nevén jobb gombbal, itt válasszuk ki a Properties menüpontot, majd a felugró ablak General fülén állítsuk át a rádiógombot arra, amit szeretnénk.

És kész is vagyunk:

2. Ha sokszor rögzítünk feladatokat a Task Listünkre, ráadásul ezt emlékeztető beállításával tesszük, akkor felmerülhet az igény a default reminder time átállítására. Hogy tudjuk ezt elérni?

Ezt és még egyéb, taskokhoz kapcsolódó beállítást is meg tudunk tenni, ha az Outlook Optionsben a Tasks menüre kattintunk.

Színeket, default reminder időpontot tudunk babrálgatni itt, sőt még status riportot is kérhetünk, ha megoldunk egy hozzánk rendelt feladatot.

3. Frissítések, lefagyások, hideg újraindítások után fordulhat elő a következő hibaüzenet:

Ilyenkor legegyszerűbben úgy tudjuk mégis indulásra késztetni levelezőnket, ha a Start menüből közvetlenül is indítható Command prompt segítségével a /resetnavpane paraméterrel indítjuk az Outlookot:

4. Ez akár következhet az előző pontban említett /resetnavpane paraméterrel történő indítás miatt is, de a lényeg, hogy Compact navigációs nézet helyett az Outlook alsó részén egymás mellett ikonok helyett szöveges nézetként jelennek meg a Mail, Calendar, People, Tasks és a három pont opciók:

Ennek orvoslásához kattintsunk a három pontra, majd az itt megjelenő kis menüből válasszuk a Navigation Options menüpontot:

Az így felugró Navigation Optionsnél pedig válasszuk ki a Compact Navigation checkboxot és OK!

A napokban merült fel egy olyan kérdés a WORD szövegszerkesztőjével kapcsolatban, hogy van-e esetleg arra mód, hogy egy újabb esetleg régebbi verziójú beágyazott fájlt megnyissunk a megfelelő verziójú szövegszerkesztőnk nélkül, rengeteg beágyazott, beszúrt fájlt kinyerjünk egy mozdulattal vagy egyáltalán, egy WORD dokumentumunk tartalma kinyerhető-e anélkül, hogy lenne ilyen típusú szövegszerkesztőnk.

A válasz igen, ehhez mindössze annyit kell csak tudnunk, hogy az Office XML alapú formátumai, köztük a docx is, valójában tömörített állományok, amely anyagokhoz szimplán hozzáférhetünk, ha átnevezzük zipre az adott fájlunk kiterjesztését.

Szóval a példában adott egy WORD dokumentum, ami tartalmaz egy kis szöveget, egy beszúrt képet és egy beágyazott dokumentumot:

Fogjuk ezt a fájlt és nevezzük át a kiterjesztését .zip-re. Ha ezt megpróbáljuk megtenni, kapunk egy figyelmeztetést, hogy esetleg használhatatlanná válik így a fájlunk, de ezen lépjünk túl egyszerűen:

Ezután csomagoljuk ki ezt a tömörített állományt és már látjuk is, hogy miből áll a .docx fájlunk valójában:

Ha itt a könyvtárstruktúrában belépünk a word almappába, akkor a következő kép tárul elénk:

A legfontosabbak közül az első, amiket a mostani kérdéskör megválaszolásához ismernünk kell, az embeddings mappa, ugyanis itt találjuk meg a WORD fájlunkba beágyazott dokumentumokat:

A media mappában találjuk a beszúrt képeket, videókat, zenéket stb.:

Magáról a fájlban lévő szövegünkről pedig a document.xml fájl fog segíteni nekünk kideríteni dolgokat:

Ha ezt megnyitjuk mondjuk Notepadben, akkor eléggé kaotikus formában, de meg tudjuk nézni, hogy mit írtunk anno a dokumentumba:

A jobb olvasási élmény érdekében természetesen használhatunk XML editorokat is, amelyek segítenek jóval átláthatóbbá tenni ezt az xml fájlt. A többi könyvtár alapvetően nem létfontosságú számunkra, úgyhogy csak röviden érinteném őket:

a _rels könyvtárban található .rels fájl elérési útvonalakat (kapcsolatokat, innen a neve) tartalmaz a metadata (adatok az adatainkról) információkhoz, köztük például a document.xml fájlhoz, ami maga a dokumentumunk tartalma.

A docProps könyvtárban tehát metadata információ, tulajdonságokra vonatkozó adat található, az alapesetben itt lévő két fájl közül az app.xml magáról a WORD applikációról tárol adatokat (verziószám stb.), a core.xml pedig a dokumentumról, úgy mint a szerző nevét, a létrehozás dátumát stb.

A word könyvtár tartalmazza tehát a dokumentum tartalmát, ahogy arról már volt szó, a [Content_Types].xml pedig abban segít, hogy meghatározhassuk dokumentumunk minden egyedi jellemzőjét, azaz itt kerül listázására az összes olyan fontos jellemző, amely szükséges az applikációnak ahhoz, hogy megfelelően be tudja tölteni a dokumentumunkat.

Harvey Balls - olyan kör alakú információhordozó szimbólumok, amelyek főként minőségi összehasonlításban, eredmények vizuális megjelenítésében, projektek állapotának kimutatásában terjedtek el az 1970-es évektől kezdődően. Nevüket egy Harvey Poppel nevű tanácsadóról kapták, tehát tévedés az a sokak által tényként kezelt hiedelem, hogy közük van a smiley arcocska megálmodójához, Harvey Ballhoz.

Eddig az a pár hivatalos infó, amit tudnunk kell a Harvey Ballsról, a következőkben inkább jöjjön annak bemutatása, hogyan tudunk ilyen kis információhordozó gömböket könnyedén készíteni Powerpointban.

Első lépésként kezdjük a Ribbonunk Insert füle alatt található Illustrations szekció Shapes funkciója alól egy Ovális forma beszúrásával, figyelve arra, hogy miközben megrajzoljuk a kis körünket, tartsuk nyomva a Shift billentyűt, így tökéletes kört kaphatunk:

Mielőtt rátérünk a következő lépésre, célszerű a Ribbonunk Drawing Tools - Format füle alatt megnézni, hogy milyen méretű az első körünk, ugyanis a másodikat is ilyen méretűre kell majd elkészítenünk.

Szóval ismét a formák beszúrásához megyünk és ezúttal egy körszeletet szúrunk be akkora méretben pontosan, mint az első körünk:

Általánosságban tapasztalatom szerint a Harvey Balls színei a szürke és fekete különféle árnyalatai, így színezzük át mindkét körünket a következők szerint:

- Az első körünk legyen világosszürke kitöltéssel, de jó erős körvonallal. Ezt az első körre kattintva megjelenő Drawing Tools - Format fül alatt tudjuk elérni a Shape Fill illetve Shape Outline funkciók segítségével.

- A második körünk legyen jóval erősebb szürke kitöltéssel, de körvonalak nélkül. Ezt a körre kattintva tudjuk elérni a fentebb már említett Shape Fill és Shape Outline funkciók segítségével.

Ezután fogjuk meg a körszeletünket és szépen illesszük bele az első körünkbe. Ha ez megvan, akkor a két kört együtt kijelölve és jobb gombbal kattintva elérjük a Group funkciót, amivel egyesíteni tudjuk a két kört és máris elkészült az első gömbünk:

Mit kell most tennünk? Készítsünk ebből a gömbből még párat legalább:

Ha ez megvan, akkor bármelyik kis labdánkban ha belekattintunk a körszeletbe, akkor tudjuk méretezni a szeletet a kis sárga nyilak segítségével és ezzel bármikor olyan állást tudunk beállítani, amilyet akarunk:

Röviden tehát ennyi lenne, máris elkészültek a "Harvey-labdák", prezentálhatjuk a projektjeinket vagy amit csak akarunk.

Ha feltenném azt az egyszerű, ám bizonyos helyzetekben igencsak fontossá váló kérdést, hogy egér használata nélkül ki hogyan jelölne ki különböző cellákat Excelben, vajon hányféle megoldási javaslat érkezne? Ez majd talán hozzászólásokban kiderül, de én most néhány lehetőséget bemutatnék afféle könnyed hétvégi írás keretein belül.

Azt minden Excel-felhasználó valószínűleg álmából felébresztve is azonnal rávágja, hogy a CTRL lenyomása majd nyomva tartása után a bal egérgombbal bőszen kattintgatva bármennyi különálló cellát ki tudunk jelölni:

Ezt valamennyire megkönnyíthetjük azzal, ha a kijelölésünk elején lenyomjuk a SHIFT+F8 kombinációt, amellyel élesítjük az ADD TO SELECTION parancsot és innentől kezdve a CTRL-t nem is kell nyomnunk, csak kattintgatnunk a különböző celláinkra, tartományainkra. Ezt a SHIFT+F8 újbóli lenyomásával tudjuk kikapcsolni.

De ez még mindig igényli az egerünk használatát, szóval hogy tudnánk a kattintgatást eliminálni? Egyszerűen annyit kell tennünk, hogy miután az előbbieknek megfelelően lenyomtuk a SHIFT+F8-at, szépen átballagunk a következő kijelölendő cellánkra, azon állva nyomunk egy F8-at, majd egy SHIFT+F8-at és megyünk a következőre megint.

Azt meg már valószínűleg szintén sokan ismerik, hogy ha csak azon celláinkat akarjuk kijelölni, amelyek tartalmaznak értéket, akkor egyszerűen a Ribbonunk Home füle alatt található Find&Select utasításcsoportból válasszuk ki a Go To Special parancsot és az itteni lehetőségekből mondjuk válasszuk ki az összes konstans számértéket, mint kijelölendő cellákat:

És ha még mindig szeretnénk ragaszkodni a billentyűkombinációkhoz, akkor a kijelölt különböző celláink szummáját is meg tudjuk adni mindenféle kattintgatás nélkül, hiszen elég csak azon cellákat a fenti SHIFT+F8 és F8 módszerrel kijelölni, amelyek felett összegezni kívánt számaink vannak:

Ezután pedig elég egy ALT+= kombinációt nyomnunk, hogy megszülessenek a végeredményeink minden kis táblánál:

Ahogy fentebb szó volt róla, hogy az F8 segítségével tudjuk bekapcsolni az EXTEND SELECTIOn nevű parancsot, de nem ez az egyetlen ilyen, kijelölést segítő parancscsoport, az END lenyomásával például az END metódust tudjuk bekapcsolni, amellyel nem-üres celláink között tudunk ugrálni END majd SHIFT és nyilak használatával.

Mindenféle fontos és kevésbé fontos elfoglaltságok okán sajnos az elmúlt napokban kissé háttérbe szorult a blog, de semmiképpen sem végleg, úgyhogy aki esetleg egy-egy felvetett problémája megoldására vár, az se csüggedjen, ismét újraindult a mókuskerék. A mai posztban egy olyan olvasói kérdést fogok körbejárni, amely egyaránt felmerülhet magánéleti kérdések kapcsán, de bármilyen vállalati környezetben is - és természetesen teljesül rá az Excel-problémák legnagyobb részében kimondható tézis: biztos, hogy van még legalább egy másik megoldás is.

A probléma teljes egészében az alapvetően elég egyszerű formula, a RANDBETWEEN körül forog, amelyről közismert, hogy az első és második paramétereként megadott (elsőként az alsó határt, másodikként a felsőt kell megadnunk) számok között fog véletlenszerűen számokat kisorsolni nekünk.

Önmagában tehát ez a kis függvény tökéletesen használható (lenne - egy kis csavarral) véletlenszerű mintaválasztástól kezdve zeneszámok sorrendjének összeállításán keresztül akár lottószámok sorsolására is, azzal a sajnálatos kitétellel, hogy a függvény minden szívfájdalom nélkül duplikációkat is hozni fog nekünk - a tartomány méretétől függően kisebb-nagyobb valószínűséggel.

Ennek a problémának a legegyszerűbb és leggyakrabban használt kiküszöbölési módja a RAND függvény bevetése és egy segédoszlop alkalmazása, hiszen a RAND 0 és 1 között fog nekünk egy véletlen számot generálni, amelynél a duplikáció kialakulása szinte lehetetlen. Ha valakit érdekel ez a megoldási forma, az jelezze és akkor kitérek rá, de most inkább egy olyan irányt vázolok fel, amely lehetővé teszi, hogy egyetlen tömbfüggvény segítségével, segédoszlopok nélkül eliminálhassuk a duplikációk lehetőségét RANDBETWEEN esetén.

Folytassuk a fentebb elkezdett lottószám sorsolási példánkat, ahol tökéletesen láthatjuk, hogy az első szám kisorsolására milyen módon használtuk a RANDBETWEEN-t, de ha ezt szimplán lehúznánk, az nem garantálná, hogy nem lesz azonosság.

Ennek kiküszöbölésére a második szám kisorsolásánál már az egyszerű formula helyett egy tömbfüggvényt fogunk használni, amely egy LARGE függvényben fog felépülni. A LARGE függvény az első paramétereként megadott tömbben adja meg a második paramétereként megadott sorszámnak megfelelő nagyságú számot, azaz például

=LARGE(A1:A5,2)

megadja az A1:A5 tömb 2. legnagyobb elemét.

Ha ezt legképezzük mostani problémánkra, akkor az első kérdés az, hogy mi is a tömbünk? A tömbünknek egy olyan tartományt kell most leképeznünk, amely tartalmazza elvileg az összes számunkat 1 és 90 között, kivéve azt, amelyiket már kisorsoltuk az első számként (vagyis tartalmazza azt is, csak valahogy jelezzük a függvény számára, hogy az már nem jelölt). Ezt úgy fogjuk elérni, hogy bevetjük a ROW függvényt, ami megadja nekünk a paramétereként megadott cella sorszámát, tömb esetén értelemszerűen a tömbben szereplő cellák sorszámát. Ezen logika mentén tehát

=ROW($1:$90) egy 90 sorszámból álló tömböt fog nekünk létrehozni referenciaként.

Na most nézzük meg, hogy ebben a tömbben előfordul-e a korábban kisorsolt számunk-számaink közül valamelyik. A célra igencsak jó barátunkat, a COUNTIF formulát fogjuk felhasználni, ami az első paramétereként megadott tartományban megnézi, hogy a második paraméternek megfelelő értékek előfordulnak-e. A mi esetünkben tehát

=COUNTIF($F$2:F2,ROW($1:$90))

nem fog mást tenni, mint létrehoz egy egytől kilencvenig terjedő tömböt, amelyben minden értéknél 0-t fogunk látni, kivéve azt, amelyik az F2 cellában szerepel, ott ugyanis egy egyesünk lesz, hiszen teljesül a feltétel. Annak érdekében azonban, hogy eredeti 90-es tömbünket a továbbiakban megfelelően fel tudjuk használni, ezeket a nullásokat és egyeseket FALSE és TRUE értékekre kell konvertálnunk, méghozzá úgy, hogy a nulláink TRUE-ként jelenjenek meg, az egyeseink pedig FALSE-ként, hiszen így érjük el, hogy legyen egy 90-es tömbünk, amelyben egytől kilencvenig látjuk az összes fel nem használt számot és nullaként mutatjuk majd ki a felhasználtakat.

A NOT függvény pontosan ezt teszi, szimplán kiadja majd megfordítja a TRUE és FALSE értékeket, tehát

=NOT(COUNTIF($F$2:F2,ROW($1:$90)))

formula egy olyan kilencvenes tömböt fog nekünk létrehozni, amelyben 89 TRUE és egyetlen FALSE érték lesz, méghozzá pont azon a sorszámon, ahol az F2-ben szereplő számunk szerepel.

Arról pedig már korábban a SUMPRODUCT függvény kapcsán volt szó, hogy például a * karaktert mi mindenre használhatjuk formuláinkban, mi jelen esetünkben egy olyan tömböt fogunk létrehozni a segítségével, amely egytől kilencvenig tartalmazza azon számainkat, amelyeket a RANDBETWEEN még nem sorsolt ki és nullával (FALSE) fogja azon sorszámokat megjeleníteni a tömbben, amelyeket már kiválasztottunk.

=ROW($1:$90)*NOT(COUNTIF($F$2:F2,ROW($1:$90)))

Értelemszerűen végig CTRL+SHIFT+ENTER kombinációval operálunk, hiszen itt tömbökről és tömbfüggvényekről beszélünk. Szóval a fenti függvénnyel elkészült LARGE függvényünk első, tömb paramétere.

A LARGE második paramétere pedig a nagyság sorrendjét határozza meg, ahogy fentebb már volt róla szó, tehát most fogjuk a létrehozott és a már kiválasztott számok sorszámán nullát szerepeltető tömbünkből kiválasztani a következő számot, ami semmiképpen nem lesz majd duplikáció.

Egyszerűen nem kell mást tennünk, mint beágyazni ide egy RANDBETWEENT, amelynek alsó paramétere értelemszerűen az egyes lesz, de a felsőt már az alapján fogjuk meghatározni, hogy eddig hány számot sorsoltunk ki, hiszen az eredetileg kilencvenes tömbünkben először csak egy, később már több nulla is lesz, azaz akkor már nem 90 lehetséges számról, hanem 89-ről, 88-ról és így tovább, beszélünk.

=RANDBETWEEN(1,(90+1)-ROW(F2))

Szóval az alsó érték az egyes, a felső pedig nem más, mint a maximális felső határunk, hozzáadva egyet a fejléc miatt és lecsökkentve az aktuális sorszám értékével, tehát F3 cellában

90 + 1 (a fejléc miatt) - 2 (ez F2 sorszáma) = 89 (és tényleg eddig egyetlen számot sorsoltunk ki)

F4-ben

90 + 1 - 3 = 88

Ezzel el is értünk a végére, így nézünk ki elvileg:

={LARGE(ROW($1:$90)*NOT(COUNTIF($F$2:F2,ROW($1:$90))),RANDBETWEEN(1,(90+1)-ROW(F2)))}

Ne felejtsük továbbra sem, hogy ez egy tömbfüggvény, tehát CTRL + SHIFT + ENTER-t kell nyomnunk és utána húzhatjuk majd csak le.

Kihasználva a hétvégét, tovább folytatom elmaradásom feldolgozását, már ami az olvasói kérdéseket illeti és ezúttal egy nagyobb fába vágom a fejszémet, mert az Excel-kérdések egyikének megválaszolása nem is egy posztot fel felölelni, hiszen én több részre osztottam a megoldást - a kérdés önállóan is kezelhető első része egyébként a kedves írónak nem okozott gondot, de hátha másnak is szüksége lehet ilyesmire, így kezdjük ezzel a kifejtést.

Szóval a kérdés ezen része olyasmi, amelyre nem VBA-s megoldást már kínáltam korábbi posztokban (például a command promptból indítható dir parancs vagy a böngészős Select All Content segítségével), de most azt nézzük meg, hogy makróval hogy tudjuk kilistázni egy adott könyvtárunk tartalmát. Célunk tehát ennek a kis táblának az automatikus kitöltése:

Ahogy mindig, most is hangsúlyozni kell, hogy ez csak egy a lehetséges megoldások közül, szóval továbbra is nyitott vagyok más javaslatokra/ötletekre.

Rövid VBA-kódunk magját és legfontosabb elemét a FileSystemObject objektum adja, amely objektumalapú lehetőséget ad számunkra, hogy hozzáférjünk a számítógépünk fájlrendszeréhez. Egyszerűen csak létre kell hoznunk egy FileSystemObject objektumot VBA-ban a Scripting.FileSystemObject szintaxis segítségével, aztán már törölhetünk, beolvashatunk vagy akár létre is hozhatunk fájlokat. Hogy értsük a szintaxist, érdemes megjegyezni, hogy a FileSystemObject a Microsoft Scripting Runtime Libraryben található dll-ként.

Visszatérve a mostani kérdéskörhöz, lépjünk be a Visual Basic Editorba, majd hozzunk létre négy változót, hármat objektumként, egyet pedig integer típussal:

FSO a fentebb már említett FileSystemObject objektum, amelyet azért hozunk mindjárt létre, hogy hozzáférjünk a fájlrendszerhez, a Folder és File változók értelemszerűen könyvtár és fájl objektumok tárolására szolgálnak majd, i változónkra pedig a ciklushoz lesz majd szükségünk.

Ha megvannak a változók, akkor állítsuk be, hozzuk létre a FileSystemObject objektumot:

Set FSO = CreateObject("Scripting.FileSystemObject")

Majd a Folder objektumot is állítsuk be arra a könyvtárra, ahol listázni akarunk, ehhez a FileSystemObject objektum GetFolder metódusát használjuk, amely mindig objektumként adja vissza nekünk azt a foldert, amely a megadott elérési úton található:

Set Folder = FSO.GetFolder("c:\")

Ezzel konkrétan már a folderben vagyunk, most jön a ciklusunk, amely egyesével végigmegy a folderben lévő fájlokon és azok nevét egymást követő cellákba írja be.

Miután van fejlécünk, kezdjük alapból az i = 1 értékkel, de mint látni fogjuk ez lehetne 0 de akár 2 is, attól függően hova akarjuk tenni az értéklistát.

A ciklushoz a már jópár alkalommal bemutatott és megismert For Each...Next utasítást fogjuk felhasználni, azaz ha magyarra átfordítva fogalmazzuk meg a kódunkat, akkor minden egyes fájlunk esetén (For Each File) a fentebb definiált folderünk összes fájlja között (in Folder.files), fájlonként haladva az első oszlopunk második cellája (Cells(i + 1, 2)) legyen egyenlő a fájl nevével (File.Name), a második oszlopunk második cellája (Cells(i + 1,2)) pedig legyen egyenlő a fájl elérési útjával (File.Path). Ha ez megvan, akkor ugorjon a következő sorra, azaz i értékét növeljük meg eggyel (i = i + 1).

i = 1
For Each File In Folder.files
Cells(i + 1, 1) = File.Name
Cells(i + 1, 2) = File.Path
i = i + 1
Next File

Vegyük észre, hogy File objektumunknak minden tulajdonságához hozzáférünk, így a nevéhez, elérési útjához is, de ezen analógia mentén férünk hozzá a létrehozási dátumához, utolsó megnyitási időpontjához vagy akár méretéhez is.

Ha ez megvan, akkor meg is van a kódunk, innentől kezdve pedig gombhoz rendelhetjük, vagy ahogy majd én is tenni fogom, egy nagyobb kód első részeként fogjuk felhasználni.

Kódunk szövegként:

Sub Listazo()
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("c:\")
i = 1
For Each File In Folder.files
Cells(i + 1, 1) = File.Name
Cells(i + 1, 2) = File.Path
i = i + 1
Next File
End Sub

Egyik kedves Olvasóm igencsak fogós fejtörővel örvendeztetett meg a napokban és csak egy igazi örömöt okozó, kiadós elmélkedés után született meg a megoldás, ami természetesen, ahogy más Excel-problémáknál, úgy valószínűleg most sem az egyetlen lehetséges megoldás, de ez egy tényleg jó kis feladvány, szóval szokás szerint nyitott vagyok más megoldási módszerekre is.

Maga a kérdés több témakört ölel fel, de a probléma középpontjában a dátumok és az Excel dátumkezelése áll, hiszen alapvetően közismert, hogy a táblázatkezelő az 1900-as és az 1904-es dátumrendszert tudja használni, azaz az egyiknél 1900. január elseje jelenti az egyes sorszámot, a másiknál pedig 1904. január elseje.

Amiért ez igen fájó pont az az, hogy bár formátum és bevitel szempontjából az Excel képes kezelni 1900 előtti dátumokat is, kalkulációkat már nem tudunk velük végezni:

Persze ez nem egy mindennap felmerülő igény, az tény, de ettől függetlenül mi van, ha régi történelmi személyiségek pontos életkorát szeretnénk napban megadni születésük és haláluk időpontja alapján? Vagy teljesen más okból lenne erre szükségünk?

Anno itt írtam az egyik rejtélyes Excel-formuláról, a DATEDIF-ról, amely önmagában most sem tud segíteni nekünk, hiszen ha 1900 előtti dátumokat adunk be paraméterként, akkor ez a függvény is egy vaskos #VALUE! hibaüzenettel válaszol, de ha kicsit kreatívak vagyunk, akkor mégis el tudjuk érni, hogy megkapjuk a kívánt értéket.

Adott tehát egy egyszerű kis feladat:

Alapból itt most fejből is meg tudjuk mondani az eredményt, de a cél az, hogy bármely dátum és bármennyi dátum esetén működjön ez a logika. Összesen három függvényt fogunk felhasználni a különbség meghatározásához, a fentebb már említett DATEDIF formula mellett egy LEN és egy RIGHT is kelleni fog majd - plusz az, hogy a dátumok egységes formátumban legyenek (ezen függvényekről hosszabban nem írok már, elég sok posztban bemutattam őket, úgyhogy valószínűleg minden Olvasó előtt ismertek).

Az én példámban szereplő formátum esetén első lépésként vegyük le az utolsó négy karaktert a dátumról:

=LEFT(K3,LEN(K3)-4)

Mit is teszünk? A K3 cellában szereplő értékből levágjuk az érték karakterszáma-4 értéket, azaz konkrétan az évet (hiszen ez pont négy karakter az értékünk végén):

Majd újra hozzátesszük az évet, de nem ugyanúgy, hanem a dátumaink tartományát figyelembe véve hozzáadunk még több száz évet:

=LEFT(K3,LEN(K3)-4)&RIGHT(K3,4)+400

Az előbb levágott, most már év nélküli részhez hozzáadjuk teljes eredeti dátumunk utolsó négy karakterét, tehát az évet (RIGHT(K3,4)), megnövelve 400-zal:

Ezt teljesen ugyanezzel a módszerrel megismételjük a másik dátumra is:

=LEFT(L3,LEN(L3)-4)&RIGHT(L3,4)+400

Ezután pedig szimplán csak beágyazzuk a két, most már jelentősen jövőbeni dátumot egy DATEDIF függvénybe és meg is van a különbségünk:

=DATEDIF(LEFT(K3,LEN(K3)-4)&RIGHT(K3,4)+400,LEFT(L3,LEN(L3)-4)&RIGHT(L3,4)+400,"d")

Mint tudjuk, DATEDIF függvényünk első két paramétere a két dátum, amelynek különbségét meg akarjuk határozni, harmadik paramétere pedig az az indikátor, amellyel jelezzük, hogy napban, hónapban, évben stb. szeretnénk látni a különbséget.

Ez biztosan nem a legszebb megoldás, de elvileg olyan makró nélküli eredmény, amire megbízhatóan támaszkodhatunk - ennek ellenére további ötleteket, javaslatokat szívesen fogadok.

Update: az évszámhoz kizárólag 400-at illetve többszörösét adhatjuk hozzá a szökőévek okán. Köszönet az észrevételért.

Sajnos egyéb elfoglaltságok miatt kissé lemaradtam különböző témák kifejtésével és olvasói kérdések megválaszolásával, de hogy kicsit felzárkózzak az elmaradások sorozatában, most egy, valószínűleg mások számára is hasznos Excel-kérdés megoldását mutatom be, ami önmagában csak kreativitást és a HYPERLINK formula megfelelő használatát igényli.

Kedves Olvasómnak van egy olyan könyvtára, amelyben sok ezer képet tárol és ezekre szeretne linkeket beszúrni egymás alatti cellákba, értelemszerűen nem manuálisan, tehát a kérdése, hogy létezik-e valamilyen formában Autofill funkció linkekre is?

Így néz ki a problémára készített példatáblám:

Alapvetően feltételezzük, hogy a képek valamilyen formában szekvenciálisan sorakoznak a könyvtárunkban, másrészt azt is feltételezzük, hogy egyetlen specifikus könyvtárról van szó, de ahogy mindjárt látni fogjuk, ezek nem kizáró feltételek, a feladat a lenti minta mentén bármilyen formában megoldható.

A képeink nevét több módszerrel, rendkívül gyorsan ki is nyerhetjük az adott folderből, például ha az elérési utat bemásoljuk mondjuk Chrome böngészőnkbe és ott nyitjuk meg a foldert, majd ha megnyílt, CTRL+A kombinációval kijelöljük az egész tartalmat, majd CTRL+C és CTRL+V kombináció után bemásoljuk ezt Excelbe. A módszer hátránya, hogy mindent másol, méretet, dátumot stb.

De emellett használhatjuk azt a módszert is akár, hogy parancssorból (cmd) a dir > filename.txt utasítás segítségével kreálunk egy Excellel szintén könnyen kezelhető TXT fájlt, mappánk tartalmával.

Ezzel az is érthetővé vált, hogy honnan tölthetjük be nagyon egyszerűen képeink listáját a "Kép neve" oszlopba, ahonnan pedig szintén több utunk van a linkjeink gyors kitöltésére.

Simán csak összefűzhetjük a képeket tartalmazó könyvtár nevét a kép nevével és linkké alakíthatjuk, de a kettő lépést könnyen egyesíthetjük is és a következő kis függvénnyel kapásból el is érhetjük célunkat:

=HYPERLINK($F$1&"\"&B2&".jpg","Fenykepek\"&B2&".jpg")

A fenti függvényt értelmezve láthatjuk, hogy abszolútként meghivatkozott F1 cellánk tartalmazza az alapkönyvtárat, ehhez fűzzük hozzá a kép nevét és formátumát (de a név és a formátum akár egyben is szerepelhet a "Kép neve" oszlopunkban), majd a HYPERLINK függvény második lehetséges paramétereként megadható, Friendly Name paraméterben pedig elnevezzük linkünket, jelen esetben a Fenykepek\ mappanév után írt képnévvel és formátummal. Innentől kezdve pedig szabad a vásár, kész is a feladat.

A mai posztban kivételesen nem a megszokott módon fogok problémákra megoldásokat kínálni, hanem két érdekes és esetenként akár bugként is felfogható Excel-viselkedést mutatok be - mindkettővel együtt lehet élni és valószínűleg a többség nem is fog találkozni velük, de azért jobb tudni, hogy az Excel 2013-as verziója sem tökéletes (a 2016-os csomagban ezeket még nem teszteltem).

Az első ilyen érdekes kérdés bemutatását a következő táblázattal kezdjük, amely két város havi fix kiadásait mutatja be:

Mi történik, ha mondjuk Január oszlopban mindkét sort kijelölve egyesítem a cellákat a Ribbon Home füle alatt található Alignment szekció Merge & Center parancsával?

Ez az elvárt viselkedés, azt kaptuk, amit szerettünk volna, ha szummában összeadjuk a kiadásainkat, akkor a kalkuláció is helyes értéket mutat. Mi történik azonban, ha a Ribbonunk Home füle alatt található Format Painter segítségével ezt a Merged Cell formátumot átmásoljuk Februártól kezdve a többi hónapra is?

Még mindig azt kaptuk, amit vártunk - vagy mégsem? Amit látunk, az elvileg az, amit szerettünk volna elérni, de ha például összeadjuk Április-Május-Június hónapok értékeit, láthatjuk, hogy 3000 helyett 4500 lesz az eredmény:

A lenti állapotjelzőn is 4500-at mutat az automatikus kalkuláció, ahogy a SUM függvény is ezt az eredményt adja, de ha egyesével adjuk össze a cellákat, akkor azt a 3000-es értéket kapjuk, amire vártunk. Tehát a konkluzió az, hogy Format Painter segítségével semmiképpen ne másoljunk cellaegyesítést, mert az csak a formátumot viszi, a háttérben ugyanúgy ott tárolja annak a cellának az értékét, amelyről azt hisszük, hogy elveszett.

A másik mai érdekes kérdésem, amire a választ kerestem sokáig, nem más, mint a WEEKNUM függvényben megbúvó rejtély. A függvény önmagában két paraméterből áll, az első paramétereként megadott dátumról megmondja, hogy az az év hányadik hetében található, a második, nem kötelező paraméterben pedig megadhatjuk, hogy melyik nap kezdődik egy hét, ez alapesetben 1, vasárnap. Ennek tükrében nézzük meg ezt a kis táblát:

Mi a furcsa benne? Ha december 27-én kezdődött az 53. hét, akkor január elsején miért első hétről beszélünk, annak még nem az 53. hétnek kellene lennie? A megoldást a Microsoft hivatalos leírása vagy egyáltalán, a formula második paraméterének segítsége adja meg:

Mik a lehetőségeink? Az Excel 1,2,11,12,13,13,15,16,17 és 21 return_type lehetőségét adja meg nekünk, System 1 és System 2 (bár ez csak a 21-es return type-nél létezik) opcióval, ahogy láthatjuk is a legördülő kis segítségben. Ez lenne ezen opciók leírása:

Maga az első két oszlop elég egyértelmű értékeket tartalmaz, viszont a harmadik, a System talán magyarázatra szorul. A Microsoft leírása szerint System 1 az a hétszámozási rendszer, amelyben az a hét az év első hete, amelyben január elseje megtalálható - ergó a példámban ezért vált 53 után egyre az Excel. A System 2 pedig az ISO8601:2000-es hétszámozási standardnek megfelelő számozás, ahol az év első csütörtökjét tartalmazó hét számít az első hétnek. Példánkat eszerint 21-es return typera átírva:

Ezzel a magyarázatot meg is leltük a furcsaságra, de azért a poszt végén említsük meg, hogy valójában a világon nem csak ez a kettő hétszámozási rendszer létezik, hanem van még két másik is. System 1 és System 2 opciót már tisztáztuk, de System 1-nek két változata van, az egyikben január elsején kezdődik az első hét és a második a következő hétfőn folytatódik, a másikban január elsején kezdődik szintén az első hét, de a második már vasárnap folytatódik. Ezen a három verzión kívül van a legegyszerűbb, amikor január elsején elindul az első hét és január 8-án folytatódik a második, napoktól függetlenül.