Egy pénzügyi területen tevékenykedő szakember gyakran szembesülhet azzal, hogy saját kimutatásainak frissítéséhez rendszeresen adatokat kell beszereznie valamilyen webes felületről: legyen az egy tőzsdeindex, részvényárfolyam, vagy banki-, jegybanki devizaárfolyam. Amennyiben az igényelt adat eleve egy Excel fájlként van a weblapra feltöltve, akkor a megnyitás automatizálása nem bonyolultabb művelet, mintha a fájl a merevlemezünkön lenne. Ennél egy lépéssel bonyolultabb, ha a kívánt adat (tábla) a weblap HTML kódjába van beágyazva – ennek kinyeréséről lesz szó a továbbiakban.
Példánkban egy, a pénzügyi területen dolgozók körében talán leggyakoribb tevékenység automatizálását tekintjük át: egy időszak napi Euró árfolyamainak letöltését a Magyar Nemzeti Bank holnapjáról.
Előkészítés
Első lépésként a böngészővel nyissuk meg azt az oldalt, ahonnan a táblát importálni szeretnénk. A példánkban az MNB árfolyamlekérdező oldaláról indulunk (http://www.mnb.hu/arfolyam-lekerdezes). Az oldalon válasszuk ki az Eurót, adjuk meg a lekérdezendő intervallum kezdetét (a példánkban 2016.01.01.), az intervallum vége dátumának pedig egy kellően távoli dátumot ahhoz, hogy nyugdíjas korunkig ne kelljen változtatni a lekérdezésen (pl. 2100.12.31).
Az “Árfolyam információk lekérése” gomb megnyomásával kapunk egy táblát, ami 2016. év elejétől a mai napig tartalmazza a napi Euró árfolyamokat.
Kattintsunk jobb egér gombbal a táblán bárhová. A megjelenő lehetőségek közül válasszuk ki az “Exportálás a Microsoft Excel programba” műveletet.
Néhány pillanattal később egy új Excel munkafüzetben megnyílik az árfolyamtábla. Sok esetben néhány további beállítással, de makrók használata nélkül ennyi is elég lehet. Az Excelben az adattábla bármelyik cellájára kattintva jobb egérgombbal előhívható a “Data Range Properties…” párbeszédablak (vagy a Ribbonról a Data > Connections > Properties útvonalon). Itt többek között beállítható, hogy a munkafüzet megnyitásakor, és/vagy bizonyos időközönként frissítse az Excel a táblát.
Milyen esetekben lehet mégis szükség makrókkal való kiegészítésre?
Egyrészt, ha az Excel biztonsági beállításainál (Trust Center) nincs engedélyezve a külső kapcsolatok automatikus, kérdés nélküli frissítése, akkor az adattáblára vonatkozóan hiába állítjuk be, hogy a munkafüzet megnyitásakor automatikusan frissítse az adatokat, az nem fog megtörténni, a frissítés kézi indításakor is rákérdez a program, hogy biztosan akarjuk-e a frissítést. Ezt a kézi frissítést lehet egy rövid szkripttel automatizálni, hogy a frissítés a fájl megnyitásakor automatikusan lefusson.
Másik probléma, amibe belefuthatunk, hogy a táblában szereplő adatok formátuma nem megfelelő: a példánkban a dátumok szövegként szerepelnek, amit az Excel nem tud dátumként értelmezni. Makrók segítségével a formázási problémák pillanatok alatt orvosolhatóak.
Automatikus frissítés a munkafüzet megnyitásakor
Ahhoz, hogy egy utasítássor automatikusan lefusson a munkafüzet megnyitásakor, a ThisWorkbook munkafüzet modulban a Workbook_Open() eljárásba írjuk az utasításainkat. A lenti példában feltételezzük, hogy a munkafüzetünk csak egy munkalapot tartalmaz, annak A1 cellájában kezdődik az importált tábla, és csak ezt az egy importált táblát tartalmazza a munkalap:
Private Sub Workbook_Open() Application.EnableEvents = False Activesheet.QueryTables(1).Refresh BackgroundQuery:=False Frissites Application.EnableEvents = True End Sub
Az Activesheet.QueryTables(1).Refresh metódussal utasítjuk az Excelt, hogy az aktív munkalapon szereplő 1-es indexű QueryTable-t (külső adatforrásra mutató táblát) frissítse. A BackgroundQuery tulajdonsággal állíthatjuk be, hogy a frissítési művelet befejezéséig függessze fel a további műveletek végrehajtását (False érték) vagy a háttérben futtassa (True érték). Nekünk jelen esetben a False értékre van szükségünk, hogy a további (például formázási) műveletekkel megvárjuk, amíg a frissítés befejeződik.
Dátumok átalakítása
Az eljárással végiglépdelünk a dátum oszlop egyes celláin, ha szöveges formátumban van a dátum (vagyis a hónap betűvel van kiírva), akkor azt átalakítjuk olyan adattá, amit az Excel is dátumként ismer fel, majd meg is formázzuk a kívánt dátumformátumra.
Dim Hiba As Boolean Public Sub Frissites() Dim i As Long Dim Datumszoveg As String Application.EnableEvents = False For i = 4 To Range("A1").CurrentRegion.Rows.Count Hiba = False Datumszoveg = Cells(i, 1) Cells(i, 1) = DatumAtalakitas(Datumszoveg) With Cells(i, 1) If Hiba = True Then .Value2 = Datumszoveg Else On Error Resume Next .NumberFormat = "yyyy.mm.dd" .NumberFormat = "éééé.hh.nn" On Error GoTo 0 End If End With Next i Application.EnableEvents = True End Sub
Látható, hogy az eljárás “lelke” a DatumAtalakitas nevű függvény, ez végzi el ugyanis a cella átalakítását. Lássuk a függvényt:
Function DatumAtalakitas(Datumszoveg As String) As Date Dim ev As Integer Dim honapszoveg As String Dim honapszam As Integer Dim nap As Integer ev = Left(Datumszoveg, 4) honapszoveg = Mid(Datumszoveg, InStr(1, Datumszoveg, " ") + 1, _ InStr(7, Datumszoveg, " ") - InStr(1, Datumszoveg, " ") - 1) Select Case honapszoveg Case "január" honapszam = 1 Case "február" honapszam = 2 Case "március" honapszam = 3 Case "április" honapszam = 4 Case "május" honapszam = 5 Case "június" honapszam = 6 Case "július" honapszam = 7 Case "augusztus" honapszam = 8 Case "szeptember" honapszam = 9 Case "október" honapszam = 10 Case "november" honapszam = 11 Case "december" honapszam = 12 Case Else honapszam = 0 End Select If Not honapszam = 0 Then nap = Mid(Datumszoveg, InStr(7, Datumszoveg, " ") + 1, _ InStr(7, Datumszoveg, ".") - InStr(7, Datumszoveg, " ") - 1) DatumAtalakitas = DateSerial(ev, honapszam, nap) Else Hiba = True End If End Function
Az eljárásban szövegfüggvényekkel azonosítjuk a dátum összetevőit (év, hónap, nap); a legérdekesebb talán a hónap azonosítása. A Mid és InStr függvények egybeágyazásával a két szóköz közötti karaktersorozatot emeli ki, ami nem más mint a hónap neve. Ezután egy Select Case utasítással rendeljük a magyar hónapnevekhez az adott hónap sorszámát.
A függvényben elhelyeztünk egy “Hiba” elnevezésű változót. Ennek feladata, hogy kiszűrje azokat az eseteket, amikor valami hiba csúszik a rendszerbe és az adott cellában nem szerepel egyik hónap neve sem (vagy elgépelés miatt, vagy azért, mert már eleve dátumformátumban van a cella). Jelen példánkban ilyen jellegű hiba esetén a cellát változatlanul hagyjuk.
Frissítés manuálisan
Ha már nyitva van a munkafüzetünk, az Excel lehetőséget ad arra is, hogy kézzel frissítsük az adattáblánkat (az opció előhívható a jobb egérgomb menüjéből, vagy a Ribbonról a Data > Connections útvonalon). Még egy rövid szkriptre szükségünk van ahhoz, hogy a fentiekben bemutatott makró az adattábla kézi frissítésekor is lefusson. Ehhez a Sheet1 munkalap modul Worksheet_Change eseményéhez írjuk a következőket:
Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Frissites Application.EnableEvents = True End Sub
Látható, hogy az összes eljárásban az eljárás elején kikapcsoljuk az események figyelését (Application.EnableEvents = False) az eljárás végén pedig vissza. Erre azért van szükségünk, hogy ne kerüljenek az eljárások végtelen ciklusba: az adattábla frissítésével elindítjuk a “Frissites” nevű eljárást, amit az Excel esemény figyelője a munkalap változásaként értelmez, ezért lefut a Worksheet_Change eljárás, ami újra elindítja a “Frissites” nevű eljárást, ami miatt újra lefut a Worksheet_Change eljárás… és így tovább.