Drowning In Paperwork

Munkalapok tengerében

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.

Popup CommandBar

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.

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.