A mai blogbejegyzésben bemutatott kis eszköz hathatós segítséget nyújthat azoknak, akik sok munkalappal rendelkező fájlokkal dolgoznak.
Példánkban adott egy munkafüzet, amiben húsz munkalapunk van. Legtöbbet nem neveztük át, hanem meghagytuk az eredeti nevét (“Sheet1”, “Sheet2”, stb.) viszont hármat kiemeltünk és új elnevezést kaptak. Makró segítségével meg fogunk jeleníteni egy listát (pontosabban Command Bar-t), amin felsoroljuk a kívánt munkalapokat (igény szerint az összeset, vagy egy szűrt listát). Egy elemre kattintva pedig aktiváljuk a kiválasztott munkalapot.
A bemutatott példa egy lehetséges felhasználási módot mutat be, de kis módosítással más műveletet is hozzárendelhetünk, például a kiválasztott munkalap nevét kiírhatjuk egy cellába, vagy elmenthetjük egy változóba. Sőt, az eszköz erőssége abban rejlik, hogy egy másik, lezárt munkafüzet munkalapjait is ki tudjuk listázni anélkül, hogy az adott munkafüzetet megnyitnánk.
Munkalapok kilistázása
Programunk lelke egy olyan eljárás (függvény), ami egy gyűjteménybe (Collection) gyűjti a munkalapok neveit, mindezt az ADO, vagyis az ActiveX Data Objects Library segítségével. Ahhoz, hogy mindez működjön, ellenőrizzük, hogy be vannak-e állítva a referenciák (Tools –> References) az ADO (“Microsoft ActiveX Data Objects x.x Library”) és az ADOX (“Microsoft ADO Ext. x.x for DLL and Security”) objektum könyvtárakhoz.
Az eljárás az ADO modellnek azt a tulajdonságát használja ki, hogy az Excel fájlt adatbázisként, annak egyes munkalapjait pedig adatbázis táblaként tudja értelmezni. Vagyis nem csinálunk mást, mint kilistázzuk az adatbázis (t.i. Excel tábla) tábláinak (t.i. munkalapok) nevét.
ADO kapcsolat létrehozása
Első lépésként hozzuk létre a kapcsolatot az adatbázissal, és mentsük el változóba a szükséges objektumokat. Fontos, hogy az eljárás végén zárjuk le a kapcsolatot és ürítsük ki az objektum változókat:
Function GetSheetsNames(ByVal WbName As String) As Collection '***************** Deklarációk **************** Dim objConn As ADODB.Connection Dim objCat As ADOX.Catalog Dim sConnString As String '************************************************* ' ADO kapcsolat létrehozása sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & WbName & ";" & _ "Extended Properties='Excel 12.0;HDR=YES'" Set objConn = New ADODB.Connection objConn.Open sConnString Set objCat = New ADOX.Catalog Set objCat.ActiveConnection = objConn ' Munkalapok betöltése a gyűjteménybe ' [... lásd később ...] ' kapcsolat bontása objConn.Close ' objektum változók ürítése Set objCat = Nothing Set objConn = Nothing End Function
Az sConnString változónk tartalmazza az ún. Connection String-et: ennek paraméterei és lehetséges értékei attól függenek, hogy milyen típusú adatbázishoz szeretnénk kapcsolódni.
Excelhez kétféle OLE DB szolgáltatóval (Provider) tudunk kapcsolódni:
- Jet (Provider=Microsoft.Jet.OLEDB.4.0) – ez az Excel 97-2003 verzióinál használható, kizárólag 32 bites környezetben;
- Access Database Engine, röviden ACE (Provider=Microsoft.ACE.OLEDB.12.0) – ez bármelyik Excel verziónál használható.
A forrásunk (Data Source) jelen esetben egy Excel fájl: a WbName változó az Excel fájl teljes elérési útvonalát, nevét és kiterjesztését kell, hogy tartalmazza.
Az Extended Properties tulajdonság értéke esetünkben attól függ, hogy milyen verziójú és típusú (kiterjesztésű) Excel fájlhoz szeretnénk kapcsolódni, értékei lehetnek:
- Excel 8.0 – Excel 97-2003 verzióknál;
- Excel 12.0 – Bármelyik Excel verziónál (kiterjesztéstől függetlenül);
- Excel 12.0 Xml – Excel 2007 és későbbi verzióknál, .xlsx kiterjesztésnél;
- Excel 12.0 Macro – Excel 2007 és későbbi verzióknál, .xlsm kiterjesztésnél.
A HDR tulajdonság értékének esetünkben (amikor a munkalapokat akarjuk megszámolni) nincs relevanciája, de a HDR=YES érték azt jelenti, hogy az adatbázisunk első sora a fejléc; a HDR=NO érték azt jelenti, hogy az adatbázisunknak nincs fejléc sora.
Az adatbázis kapcsolat megteremtéséhez először létrehozunk egy új Connection (t.i. adatbázis kapcsolat) objektumot (Set objConn = New ADODB.Connection), majd meghívjuk a .Open metódust, argumentumként pedig megadjuk a Connection String-et ( objConn.Open sConnString).
A kapcsolat felállása után létrehozunk egy új Catalog (t.i. adatbázis instancia) objektumot. Ennek egy tulajdonsága az ActiveConnection, ami az adatbázis kapcsolat paramétereit definiálja, és ez nem más, mint az objConn nevű Connection objektumunk (Set objCat.ActiveConnection = objConn).
Munkalap nevek betöltése gyűjteménybe
A következő lépésben egy For … Each ciklussal végigmegyünk az adatbázis tábláin, majd azok nevét – minimális átalakítás után – hozzáadjuk a gyűjteményhez. Itt van lehetőségünk szűrők alkalmazására, vagyis eldönthetjük, hogy milyen feltételek teljesülése esetén adjunk hozzá a gyűjteményhez.
'***************** Deklarációk **************** Dim tbl As ADOX.Table Dim sSheet As String Dim tmpColl As New Collection '************************************************* ' Munkalapok betöltése a gyűjteménybe For Each tbl In objCat.Tables sSheet = tbl.Name ' ----- Szűrők alkalmazása ----- 'Ha nem tartalmazza a "sheet" szót, hozzáadjuk a gyűjteményhez If InStr(1, sSheet, "sheet", vbTextCompare) = 0 Then 'Felesleges karakterek törlése On Error GoTo 0 On Error Resume Next sSheet = Application.Substitute(sSheet, "'", "") sSheet = Left(sSheet, InStr(1, sSheet, "$", 1) - 1) On Error GoTo 0 'Gyűjteményhez adás On Error Resume Next tmpColl.Add sSheet, sSheet On Error GoTo 0 End If Next tbl Set GetSheetsNames = tmpColl ' objektum változók ürítése Set tmpColl = New Collection
Az ADOX könyvtár objektum hierarchiájában a Catalog objektum egy magas szintű objektum, ez tartalmazza az adatbázis objektumokat, többek között a táblákat (Tables). A hierarchiában eggyel lejjebb, a Table objektum tartalmazza az adott adatbázis tábla objektumait, mint például az oszlopait (Columns), indexeket (Indexes), kulcsokat (Keys). A Table objektum tulajdonságai közé tartozik a tábla neve (Name), ami Excel munkafüzetek esetében a munkalap neve lesz.
A For … Each ciklussal tehát egyenként végigmegyünk a munkafüzet lapjain, vesszük annak nevét (sSheet = tbl.Name), megvizsgáljuk, megfelel-e az általunk meghatározott feltételeknek. Amennyiben igen, némi tisztítást kell rajta végezni, mielőtt továbblépnénk. A Table objektum nevét Excel munkalap esetében ilyen formában kapjuk meg: ‘Sheet1’$. Ahhoz, hogy a munkalap nevét “tisztán” adhassuk hozzá a gyűjteményünkhöz, eltávolítjuk az aposztrófokat és dollárjelet (és ami esetlegesen a dollárjel után szerepel). Végül a megtisztított elnevezést hozzáadjuk a tmpColl nevű gyűjteményünkhöz.
CommandBar megjelenítése
Az előző bekezdésekben bemutatott eljárás csak egy Collection (gyűjtemény) típusú változóba mentette a megjelenítendő munkalapok nevét, lássuk, hogyan tudjuk azokat egy CommandBar gombjaiként megjeleníteni.
CommandBar objektum létrehozása és megjelenítése
Első lépésként a szükséges változók deklarálása és betöltése után egy üres CommandBar objektumot tudunk létrehozni. Ezt tudjuk majd feltölteni a kívánt elnevezésű és funkcionalitású gombokkal, majd a megfelelő paranccsal megjeleníteni.
Sub SelectSheetName(ByVal Target_Wb As Workbook) '***************** Deklarációk **************** Dim CmdBar As CommandBar Dim ShtColl As Collection Dim WbFullName As String Dim WbName As String '************************************************* WbFullName = Target_Wb.FullName WbName = Target_Wb.Name ' Munkalap elnevezések gyűjteménye Set ShtColl = GetSheetsNames(WbFullName) ' CommandBar törlése On Error Resume Next Application.CommandBars("Register").Delete On Error GoTo 0 Set CmdBar = Application.CommandBars.Add("Register", msoBarPopup) ' CommandBar gombok létrehozása ' [... lásd később ...] ' CommandBar megjelenítése CmdBar.ShowPopup ' objektum változók ürítése Set ShtColl = New Collection Set CmdBar = Nothing End Sub
A Set ShtColl = GetSheetsNames(WbFullName) sorral hívjuk meg a korábbiakban bemutatott függvényt, aminek outputja a munkalap elnevezések gyűjteménye, ezt töltjük be az ShtColl nevű változóba.
A kódban “Register” néven szerepeltetjük a CommandBar objektumunkat, de bármilyen más nevet adhatunk neki. Először az Application.CommandBars(“Register”).Delete paranccsal töröljük az esetlegesen ilyen néven már létező CommandBar objektumot (különben később hibába futhatunk). Ezután CmdBar nevű objektumként, .Add metódussal létrehozunk egy új CommandBar típusú objektumot. Létrehozáskor két paramétert adunk meg, az egyik a CommandBar neve (Name:=”Register”), a másik a elhelyezkedése (Position:=msoBarPopup). Ez utóbbi érték jelöli, hogy a CommandBar a kurzornál felbukkanó ún. Shortcut menu-ként fog megjelenni.
CommandBar gombok létrehozása
Előző lépésben létrehoztuk az üres CommandBar objektumot, most töltsük fel azt gombokkal. Az egyes gombokat a [CommandBar].Controls.Add metódussal tudjuk hozzáadni.
'***************** Deklarációk **************** Dim CmdBarBtn As CommandBarButton Dim i As Integer '************************************************* For i = 1 To ShtColl.Count Set CmdBarBtn = CmdBar.Controls.Add CmdBarBtn.Caption = ShtColl(i) CmdBarBtn.Parameter = ShtColl(i) 'csak String lehet CmdBarBtn.Style = msoButtonCaption CmdBarBtn.OnAction = "'GoToSheet""" & WbName & """'" Next i ' objektum változók ürítése Set CmdBarBtn = Nothing
A For … Next ciklussal annyi gombot adunk hozzá, ahány eleme a munkalap elnevezéseket tartalmazó gyűjteményünknek van. Minden egyes gombnál beállítjuk a következő tulajonságokat:
- Caption – a gomb felirata, ami esetünkben az adott munkalap neve lesz;
- Parameter – paraméterként számot vagy szöveget adhatunk meg (minden esetben String típusú lesz), ezt fogjuk használni arra, hogy adott gomb megnyomásakor az adott munkalap nevét átadjuk a gomb megnyomásakor elinduló eljárásnak (későbbiekben: OnAction eljárás);
- Style – a gomb stílusát jelöli, az msoButtonCaption érték azt jelenti, hogy tisztán szöveges feliratot tartalmaz, sortörés nélkül
- OnAction – ez a paraméter tartalmazza, hogy milyen eljárás induljon el a gomb megnyomásakor.
Az OnAction paraméterben szereplő érték megér egy hosszabb megjegyzést. Az eljárás nevét idézőjelek között egy String-ként kell megadni, pl.: .OnAction = “EljarasNeve”. Az OnAction paraméter értékeként csak szöveget athatunk meg, így alapvetően nem támogatott az, hogy argumentumként változót adjunk át a meghívott eljárásnak, vagyis ha a meghívandó eljárásunk egy objektum típusú változót igényelne, például:
Sub EljarasNeve(byVal wb As Workbook)
akkor az OnAction paraméterben ehhez nem tudnánk átadni argumentumként egy Workbook típusú változót. Változó átadás kizárólag akkor lehetséges, ha a változó értéke szövegesen értelmezhető, vagyis numerikus vagy string változóról van szó. Ez esetben sem a változót adjuk át az eljárásnak, hanem a változó értékét. Ha a meghívandó eljárásunk így nézne ki:
Sub EljarasNeve(byVal WbName As String)
akkor az OnAction paraméter értékét megadhatjuk úgy hogy .OnAction = “‘EljarasNeve””” & WbName & “””‘”. Ha például a WbName változóban szereplő név Teszt.xls, akkor az OnAction paraméter értéke nem az lesz, hogy EljarasNeve(WbName), vagyis ahogy normál esetben egy eljárást meghívnánk, hanem ‘EljarasNeve”Teszt.xls”‘. Figyeljünk az idézőjelek és aposztrófok helyes használatára:
- String változó átadása esetén: [idézőjel][aposztróf]EljarasNeve[idézőjel][idézőjel][idézőjel] & VáltozóNeve & [idézőjel][idézőjel][idézőjel][aposztróf][idézőjel]
- numerikus változó esetén: [idézőjel][aposztróf]EljarasNeve[idézőjel] & VáltozóNeve & [idézőjel][aposztróf][idézőjel]
OnAction eljárás
A CommandBar gomb megnyomásához hozzárendelt eljárás nem csinál mást, mint az argumentumként átadott munkafüzetben a gomb paraméterében szereplő munkalapra átváltja a nézetet. Az eljárás így néz ki:
Sub GoToSheet(ByVal WbName As String) '***************** Deklarációk **************** Dim CmdBarCtrl As CommandBarControl Dim ShtName As String Dim Wb As Workbook '************************************************* Set CmdBarCtrl = CommandBars.ActionControl If CmdBarCtrl Is Nothing Then Exit Sub ShtName = CmdBarCtrl.Parameter Set Wb = Workbooks(WbName) Wb.Sheets(ShtName).Activate ' objektum változók ürítése Set CmdBarCtrl = Nothing Set Wb = Nothing End Sub
A CmdBarCtrl nevű, CommandBarControl típusú objektum a CommandBar egy gombját jelenti. Hogy pontosan melyiket, azt a CommandBars.ActionControl tulajdonság mondja meg, hiszen ennek értéke az a gomb, aminek a megnyomásával a rutint elindították. Ha megvan a gomb, akkor annak Parameter tulajdonságának az értéke (CmdBarCtrl.Parameter) megadja a munkalap nevét. A [Workbook].[Sheet].Activate metódussal pedig aktiváljuk a megadott munkalapot.
Makró hozzárendelése eseményhez
Példánkban a makró indítását hozzárendeljük a duplakattintás eseményhez, vagyis bármelyik munkalap bármelyik celláján duplán kattintva meg fog jelenni a munkalapok listája. Ehhez a ThisWorkbook modul SheetBeforeDoubleClick eseményéből hívjuk meg a SelectSheetName eljárást, paraméterként megadva a jelenlegi munkafüzetünket:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Call SelectSheetName(Sh.Parent) End Sub
Az esemény Target változója azt a cellát jelenti, amire duplán kattintottunk – ezt jelenleg nem használjuk, de ennek segítségével lehet például szűrni, hogy csak bizonyos cellákra kattintva jelenjen meg a munkalapok listája.
Az Sh változó azt a munkalap objektumot tartalmazza, amelyiken duplán kattintottunk. Példánkban nekünk a munkafüzet objektumra van szükségünk, ami a munkalap szülő objektuma, erre a .Parent metódussal (jelen példánkban Sh.Parent) tudunk hivatkozni.