Open sign on door

Alapvető fájlműveletek – 1. rész: fájlok megnyitása

Első témáimat amolyan alapozásnak szánom. A napi munka során elég gyakran előfordul, hogy nem csak egy Excel táblával dolgozunk, hanem meg kell nyitnunk egy-két-sok másikat, azokkal különféle feladatokat elvégeznünk (adatok másolása egyikből a másikba, linkek frissítése egyikről a másikra, adatok összehasonlítása, stb.), majd a megfelelő táblákat mentéssel vagy anélkül bezárnunk. Egy-egy nyitás, zárás vagy mentés automatizálásával önmagában nem lehet túl sok időt megspórolni, de ha ezt gyakran kell elvégezni, sok fájllal, netalán egy összetett automatizált folyamat egy lépéseként, akkor megkerülhetetlen, hogy az ember ismerje ezeket a műveleteket.

Az első körben nézzük meg, hogy saját számítógépről vagy irodai hálózatról hogyan lehet mindezeket elvégezni. A téma természetesen tovább bonyolítható, ha az adatokat különböző internetes oldalakról szeretnénk beszerezni, de kezdjük most az alapokkal és hagyjuk ezt meg ez egy későbbi bejegyzés témájának!

Lássuk először a legegyszerűbb esetet:

Egy darab fájl megnyitása, előre meghatározott fájlnévvel

Ezt a workbooks gyűjteményen alkalmazott open metódussal tudjuk elérni. A metódusnál sokféle paramétert lehet megadni. A legfontosabb a fájl neve, illetve  elérési útvonala, amit a FileName paraméterrel tudunk definiálni, itt mindig a teljes útvonalat meg kell adni:

Workbooks.Open FileName:="C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx"

vagy ha nincs más paraméter:

Workbooks.Open ("C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx")

Hasznos lehet még, ha itt beállítunk olyan paramétereket, amire a program amúgy rákérdezne (megszakítva ezzel a program futását): frissítse-e a megnyitandó fájlban szereplő hivatkozásokat (UpdateLinks), csak olvasható módban nyissa-e meg a fájlt (ReadOnly) vagy figyelmen kívül hagyja a fájlra beállított read-only ajánlást (IgnoreReadOnlyRecommended). Például:

Workbooks.Open _
    FileName:="C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx", _
    UpdateLinks:=False, _
    ReadOnly:=False, _
    IgnoreReadOnlyRecommended:=True

vagy ugyanez másképp:

Workbooks.Open _
    ("C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx", False, False,,,,True)
Off

(akiknek a fenti paramétermegadási módok nem tiszták)

A paramétereket kétféleképpen adhatjuk meg:

  • Deklarálhatjuk explicit módon – ilyenkor a metódus (jelen esetben Open) után zárójel nélkül kell megadni a paraméter nevét (pl.: ReadOnly) és := -vel elválasztva a paraméter értékét (pl.: False)
  • Deklarálhatjuk implicit módon – ilyenkor nem kell megadni a paraméter nevét, csak zárójelben, a megfelelő sorrendben felsorolni az értékeket. A fenti példában azért van 4 db vessző egymás után, mert ott kihagytunk 3 paramétert. Hogy milyen paraméterek milyen sorrendben követik egymást, abban a VB Editor segít, amikor elkezdjük begépelni a kódot:

Workbooks.Open parameters

On

A fenti módszernek egy elegánsabb módja, ha a fájl nevét és elérési útvonalát nem a metóduson belül definiáljuk, hanem előre egy konstans vagy változó formájában, például:

Const fajl As String ="C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx"

vagy

Dim fajl As String
    fajl = "C:/Users/Felhasznalonev/Documents/Fajlnev.xlsx"

és megnyitáskor már csak a konstansra vagy a változóra hivatkozunk:

Workbooks.Open FileName:=fajl

És ezzel el is érkeztünk a következő esethez:

Egy darab fájl megnyitása, változ(tathat)ó fájlnévvel

– vagyis amikor nem tudjuk vagy nem akarjuk a programon belül definiálni a fájl nevét és elérési útvonalát. Ilyenkor egy változót lefoglalunk a fájlnévnek, majd később a programban a megfelelő helyen hozzárendeljük az értéket. Az érték hozzárendelése többféleképpen történhet.

Az egyik módszer, hogy a megnyitni kívánt fájl elérési útvonala szövegként az Excel táblánk valamelyik cellájában szerepel. Például, ha ez az A1 cella:

Link A1 cellában

Dim fajl As String
fajl = Range("A1")
Workbooks.Open FileName:=fajl

A másik módszer, hogy megnyitunk egy böngészőablakot, amiben a felhasználó maga választhatja ki, hogy melyik fájlt nyissa meg. A fájlok megnyitására szolgáló böngészőablakot az Application.GetOpenFilename metódussal hívhatjuk elő. Íme egy példa:

Dim fajl As Variant

fajl = Application.GetOpenFilename _
    (FileFilter:="Excel fájlok, *.xls; *.xlsx")

If fajl = False Then
    'Cancel gombot nyomták meg
    Exit Sub
End If

Workbooks.Open Filename:=fajl

Nézzük részleteiben, mit tartalmaz a fenti kis kódrészlet. Fontos tudni, hogy a GetOpenFilename metódus nem nyitja meg a fájlt, csak az elérési útvonalat tárolja el. Ha ténylegesen meg akarjuk nyitni a fájlt, akkor a korábban leírt Workbooks.Open metódust kell alkalmazni.

A GetOpenFilename után zárójelben szerepelnek az opcionális paraméterek, ezekből én egyet alkalmaztam. A FileFilter paraméter határozza meg, hogy a böngészőablakban milyen kiterjesztésű fájlok leszűrésére legyen lehetőség. Ennek szintaktikája úgy néz ki, hogy

  • idézőjelek között
  • vesszővel elválasztva kell megadni
  • fájlszűrő sztring párokat, ahol
  • a párok első tagja egy tetszőleges leírás, a második tagja a kiterjesztés(ek),
  • az egy típushoz tartozó kiterjesztéseket pontosvesszővel kell elválasztani,
  • a kiterjesztéseket pedig *.xxx vagy *.xx* formátumban kell megadni.

Vizuális típusú olvasóink kedvéért:

GetOpenFileName FileFilter parameter explained

Az eredmény:

GetOpenFilename FileFilter parameter

A keresőablak megnyitása után három eset lehetséges:

  1. A felhasználó nem választ ki egy fájlt sem, de az Open gombot nyomja meg. Az Excel ezt az eseményt figyelmen kívül hagyja, nem történik semmi, így a programunkban sem kell külön kezelni.
  2. A felhasználó a Cancel gombot nyomja meg. Ez esetben a GetOpenFilename metódus False értéket eredményez. Ha ez után lefuttatnánk a Workbooks.Open metódust, az hibát eredményezne, így ez az esemény külön kezelést igényel. Erre szolgál az If-Then elágazás: ha a felhasználó Cancel-t nyom, és a fajl változóm értéke False (If fajl = False Then), akkor lépjünk ki az eljárásból (Exit Sub).
  3. A felhasználó kiválaszt egy fájlt és az Open gombot nyomja meg. Ebben az esetben lefut az End If utáni programszakasz.

A fentiekből következően a GetOpenFilename metódus többféle adattípust eredményezhet: ha kiválasztok egy fájlt, akkor String típusú adatom lesz, ha Cancel-t nyomok, akkor Boolean típusú. Emiatt a “fajl” nevű változómat csak Variant-ként definiálhatom.

Variációk: több fájl megnyitása

Mi a helyzet, ha egy lépésben több fájlt szeretnék megnyitni? Természetesen ennek semmi akadálya, a fent részletezett megoldások mindegyike tovább alakítható ilyen irányba.

A Workbooks.Open metódus tetszőleges számú ismétlésével tetszőleges számú fájlt tudunk megnyitni:

Workbooks.Open FileName:="C:UsersFelhasznalonevDocumentsFajlnev1.xlsx"
Workbooks.Open FileName:="C:UsersFelhasznalonevDocumentsFajlnev2.xlsx"
Workbooks.Open FileName:="C:UsersFelhasznalonevDocumentsFajlnev3.xlsx"

Ezt a megoldást nem is szeretném tovább ragozni.

Amennyiben a megnyitni kívánt fájlok elérési útvonala egy Excel táblában található, egy ciklus segítségével egyenként beolvashatjuk az útvonalakat, majd megnyithatjuk őket:

Sub Tobb_fajl_nyitasa()

Dim cella As Object
Dim cimek As Range
Dim fajl As Variant

    Set cimek = Range("A1").CurrentRegion

    For Each cella In cimek
        fajl = cella
        Workbooks.Open Filename:=fajl
    Next cella

End Sub

Böngészőablak segítségével is tudunk több fájlt megnyitni. Az egyik megoldás, hogy egy ciklusba ágyazzuk a fent ismertetett GetOpenFilename metódust, hogy az újabb és újabb fájlokat töltsön be mindaddig, amíg a felhasználó meg nem szakítja a betöltést. Amennyiben a betöltendő fájlok azonos könyvtárban vannak, lesz ennél célszerűbb megoldás (lásd később), de amennyiben a fájlokat különböző könyvtárakból töltjük be, ez a megoldás a legkézenfekvőbb. Egy példa:

Sub Tobb_fajl_nyitasa()

Dim fajl As Variant
Dim cancel as Boolean
    cancel = False
    
    Do

        fajl = Application.GetOpenFilename _
            (FileFilter:="Excel fájlok, *.xls; *.xlsx")

        If fajl = False Then
            'Cancel gombot nyomták meg
            cancel = True
        Else
            'Kiválasztott fájl megnyitása
            Workbooks.Open Filename:=fajl
        End If

    Loop Until cancel = True

End Sub

A másik megoldás, hogy a böngészőablakban engedélyezzük a többszörös kiválasztást (ez csak egy könyvtáron belül lehetséges). Ebben az esetben a GetOpenFilename egy Array (tömb) típusú adatot eredményez, ahol a tömb elemei a kijelölt fájlok. A tömb elemein végiglépdelve tudjuk az egyes fájlokat megnyitni. Íme a példa:

Sub Tobb_fajl_nyitasa()

Dim fajl As Variant
Dim fajlok as Variant

    fajl = Application.GetOpenFilename _
        (FileFilter:="Excel fájlok, *.xls; *.xlsx")

    If IsArray(fajlok) = False Then    'amennyiben nem tömb típusú a változónk,

        If fajlok = False Then         'vizsgáljuk meg, hogy van-e egyáltalán fájl kijelölve
            'Cancel gombot nyomták meg
             Exit Sub
        End If
    End If

    For Each fajl In fajlok
        Workbooks.Open Filename:=fajl
    Next fajl

End Sub

Ennyit mára, a következő részben a fájlok mentésével és bezárásával folytatjuk. Addig is várjuk kérdéseiket, észrevételeiket a fenti témával kapcsolatban!

12 thoughts on “Alapvető fájlműveletek – 1. rész: fájlok megnyitása

  1. Üdvözletem!

    Segítséget szeretnék kérni.
    Meg szeretnék nyitni makróval egy másik Excel fájlt, de olyat amit előzőleg jelszóval titkosítottam.
    Van olyan paraméter a másik Excel fájl megnyitásához, amibe megadom a jelszót és így automatikusan, kézi jelszómegadás nélkül megnyitja a titkosított Excel fájlt a makróm?

  2. Köszönöm, megtaláltam egy angol nyelvű oldalon és működik:

    Workbooks.Open Filename:=”e:\gyuri_mappa\teszt.xlsx”, Password:=”jelszavam”

  3. Kedves György,

    Üdvözlöm a blogon és örülök, hogy hamar meglett a megoldás!

    A teljesség kedvéért azért leírom, hogy a “Password” paraméter arra az esetre vonatkozik, amikor a fájl megynyitását titkosítottuk jelszóval (titkosításkor “Password to open” opciót választottuk). Amennyiben úgy állítottuk be a védelmet, hogy a munkafüzet jelszó nélkül megnyitható legyen olvasásra, de íráshoz már jelszó megadása szükséges (“Password to modify” opció), ez esetben a “WriteResPassword” paraméterhez kell a jelszót megadni.
    Amennyiben két külön jelszóval mind a megnyitást, mind az írási jogot levédtük, a makróban mindkét jelszót (paramétert) feltüntethetjük.

    Illetve ennek kapcsán megjegyezném, hogy a makróból a jelszó könnyen kiolvasható, így a makrót tartalmazó fájlt és/vagy a vba projektet mindenképp érdemes egy előzőktől független jelszóval levédeni – amennyiben más is hozzáférhet a makrót tartalmazó fájlhoz.

  4. Kedves Ádám!

    Köszönöm a részletes leírást.
    Nekem az Excel megismerése a hobbim, nagyon szeretem, ezért is fogadom örömmel és olvasom figyelmesen a leírást. Saját tapasztalatom szerint a jelszavas védelmek az Excelben csak egy jelképes gátat szabnak a hozzáféréshez. Egy jelszóval védett VBA projektet 3 perc alatt sikerült felnyitnom, pedig magamat közepes szintű Excel felhasználónak tartom. Talán jobb ötletnek gondolok egy olyan védelmet, amelyben a fájl megnyitásához szükséges jelszót egy változóba teszem, a változónak pedig olyan karaktersorozatot választok ami jelszónak néz ki, így megtévesztö lesz, első ránézésre szinte biztosan. Ezt az ötletet holnap próbálom ki.

  5. Kedves György!

    Kreatív megoldás a jelszó elrejtésére amit leírt – némi ötleteléssel (pl. több változó kombinálása, illetve futás közbeni módosítása) egész hatásosan meg lehet nehezíteni a jelszó visszakeresését – csak legyen elég hosszú kód ahhoz, hogy elbújjanak benne ezek a “huncutságok”.

    Amit az Excel jelszavas védelméről írt, az legtöbb esetben – munkalap védelme, írásvédelem, VBA Projet védelme – sajnos igaz: azt szokták mondani, hogy nem rossz szándékú felhasználó véletlen beavatkozásától véd leginkább. Ez alól szerencsére kivétel a fájl megnyitását védő jelszó – de csak a 2010 és későbbi Excel verzióknál. Itt már komoly, 128 bites titkosítást alkalmaz a Microsoft, így a korábbi Excel verzióknál, illetve a munkalap védelemnél könnyen alkalmazható brute force kódfeltörés már nem hoz eredményt (feltéve hogy elég erős a jelszó), és egyéb módszerek (pl a .xlsx fájl mögötti xml-ből tartalom kinyerése) sem használható. Ez utóbbi ellen még védelmet jelent az .xlsx vagy .xlsm kiterjesztés helyett a bináris .xlsb kiterjesztés használata.

  6. Kedves Ádám!

    Pontosan így gondoltam én is ahogy leírta.
    A jelszavam csak számokból áll és az alábbi módon rejtettem a változóba:
    f4BWa5SeA1wWr56pl2 = (Range(“K33”) + 5) * 2

    Amennyiben szeretné, privát üzenetben szívesen leírom a saját módszeremet a jelszóval védett VBA projekt megnyitására (feltörésére), de gondolom Önnek nem fogok tudni sok újat mutatni 🙂

    Nagyon köszönöm, hogy érdemben foglalkozott az ügyes-bajos dolgaimmal 🙂

  7. Kedves Ádám!

    Már készítettem makrókat, de azok elég egyszerűek voltak.

    Egy olyan makrót kellene írnom, mely egy adott mappából nyit meg fájlokat, majd ezek tartlamát bemásolja egy összesítő táblába.

    A probléma az, hogy bármi lehet a fájlok neve, a paraméterezésre nem nagyon tudok megoldást.

    Ha sikerül a random fájlnevet meghatározni, akkor a beolvasást már el tudom végeztetni. Még a paraméterezést sem tudom teljesen hogyan valósítom meg (még nem csináltam ilyet), de minden futás után növelhetném eggyel a sorok számát. Ez lenne az ötlet.

    A random nevekre milyen megoldást lehetne alkalmazni?

    Üdvözlettel:

    Tibor

    1. Kedves Tibor!
      Üdvözlöm a blogon.

      Ha jól értem a probléma felvetést, két témára lehet bontani:

      • hogyan lehet automatikusan egy egyedi fájlnevet adni a mentendő fájlnak
      • hogyan lehet ezeket az egyedi elnevezésű fájlokat egyszerűen visszaolvasni

      Az egyedi elnevezéshez jó lehet az ön által említett sorszámozás, bár itt az okozhat nehézséget, hogy ellenőrizni kell, hogy hol tart a számozás a könyvtárban.
      Ezért én ilyen esetekben valamiféle időbélyegzőt használok, egy fix fájlnév után illesztve (pl. adatok_20190212.xlsx vagy akár másodperc szintre is le lehet menni: adatok_20190212142347.xlsx). Ezek a fájlnevek például így állíthatók elő:

      Const prefix as String = "adatok_"
      dim fajlNev as String
      fajlNev = prefix & Format(Date, "yyyymmdd") & ".xlsx" 'ha csak év-hónap-nap
      fajlNev = prefix & Format(Now, "yyyymmddhhmmss") & ".xlsx" 'ha óra-perc-másodperc is

      Visszaolvasásnál hasonló logikát követhetünk: végig kell lépdelni adott könyvtár összes fájlján, és ha az elnevezés megfelel a mintának, akkor beolvashatjuk.
      A végig lépdelésre egy megoldási lehetőség:

      Public Sub LoopThroughFilesInDirectory()
       Const InputDirectory As String = "c:\Konyvtar\Alkonyvtar\"
       Dim FileName As String
       FileName = Dir(InputDirectory & "adatok_*")
       Do While Len(FileName) > 0
        'ide jöhetnek egyéb szűrők
        'utána pedig a beolvasás kódja
        FileName = Dir 'ezzel az utasítással lépünk tovább a következő fájlra
       Loop
      End Sub

      A kódban még lehet további szűréseket végrehajtani, ha nem az összes “adat_” kezdetű fájlt akarjuk beolvasni:

      • akár a “FileName =” kezdetű sorban, a fájl nevével lehet játszani, pl: “adat_201902*” –> ezzel a februári időbélyegzős fájlokat lehet beolvasni
      • akár a Do-Loop cikluson belül lehet a fájlnevet további feltételvizsgálatoknak alávetni
      • de akár elnevezéstől függetlenül is lehet vizsgálni a fájl időbélyegzőjét (fájl utolsó módosításának időpontját). Erre egy példa alkalmazás:

        Dim TimeStamp As Date
        TimeStamp = FileDateTime(InputDirectory & FileName)
        If TimeStamp >= DateValue("2019/1/1") Then 'szűrés 2019-es fájlokra
         'fájl beolvasása
        End If
      • Ha esetleg nem jól értettem a problémát, és a fentiek nem jelentenek megoldást, kérem, pontosítsunk.

  8. A fájlok nevét már ki tudom már listázni egy macro segítségével, már csak be kellene paramétereznem, hogy a listából vegye a neveket.

    1. A blogbejegyzésben van egy rész, ami azt írja le, hogy egy Excel táblában szereplő listán hogy lehet végigmenni. Ebben a “Set cimek = Range(“A1″).CurrentRegion” sort kell megfelelően definiálni (melyik lap, melyik celláiban van a lista). Ha nincs fejléce a listának, akkor a “[Kezdocella].Currentregion” kijelölés jó lehet, ez kezeli azt is, hogy a lista változó hosszúságú.

  9. Kedves Ádám,

    sajnos egy problémába futottam bele a makró készítése során. Olyan feladatom lenne hogy egy Excelben lefuttatott makró nyisson meg egy másik Excelt (első) majd még egy Excelt (második) és a másodikből vegye ki a megadott cella tartalmát majd azt tegye be az első Excelbe majd utána mind a 2 db excelt mentse el és zárja be. Csak a FŐ Excel maradjon megnyitva amiből a makrót futtattam.

    Tudna Ön nekem ebben segíteni?

    1. Kedves Erik,
      Megpróbálok 🙂
      Először is engedjen meg egy fogalomtisztázást – leginkább azért, mert ez a megkülönböztetés máskor fontos lehet:
      Bár van lehetőség (újabb) Excel, pontosabban Excel applikáció vagy Excel instancia megnyitására, a leírás alapján inkább újabb munkafüzetek megnyitására lesz szükség. Új Excel applikáció nyitás akkor lehet szükséges, ha a programot nem Excel alatt futtatjuk, hanem például Word vagy Access.

      Tehát. Ha megvan a két munkafüzet elérési útvonala, akkor érdemes úgy megnyitni őket, hogy egy-egy változót dedikálunk nekik:

      Dim wb1 As Workbook
      Set wb1 = Workbooks.Open("C:\munkafuzet1\eleresi\utvonala1.xlsx")
      Dim wb2 As Workbook
      Set wb2 = Workbooks.Open("C:\munkafuzet2\eleresi\utvonala2.xlsx")
      

      Ezek után erre a két változóra hivatkozva el tudjuk végezni a műveletet, akár többféleképpen. Például (a munkalap és cella hivatkozásokat megfelelően behelyettesítve):
      Cella másolás (Copy-Paste)
      Ez esetben a formázást és/vagy képleteket is másol

      wb2.Worksheets("Sheet1").Range("A1").Copy Destination:= wb1.Worksheets("Sheet1").Range("A1")
      

      Csak érték másolása

      wb1.Worksheets("Sheet1").Range("A1").Value = wb2.Worksheets("Sheet1").Range("A1").Value
      

      Cellában lévő képlet másolása
      Formázás nélkül

      wb1.Worksheets("Sheet1").Range("A1").Formula = wb2.Worksheets("Sheet1").Range("A1").Formula
      

      Ezek után mentjük és bezárjuk a fájlokat:

      wb1.Close SaveChanges:=True
      wb2.Close SaveChanges:=True
      

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

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