3MA481 Propojení Accessu a Excelu David Hach

3MA481

Propojení Accessu a Excelu

David Hach

Popis práce Tato práce se zabývá propojením aplikací MS Office s pomocí kódů ve VBA. V tomto konkrétním příkladě je znázorněn případ komunikace mezi programy MS Access 2007

a MS Excel 2007. Díky možnostem exportu a importu dat mezi těmito dvěma programy

a s pomocí možností jazyka VBA můžeme docílit efektivní a pohotové spolupráce mezi

soubory s velice snadným ovládáním i pro méně zkušeného uživatele. Tento příklad je inspirován reálným využitím spolupráce aplikací v evidenci jedné pražské restaurace.

1

3MA481


David Hach

Zdrojová data Pracovat budeme s těmito soubory: •

• • • • • •

|evidence.accdb|;

|prijmy_2008-11-13.xlsx|;




|prijmy_kt.xlsm|;

|prijmy_vystup-kt.xlsx|.

Pro vysvětlení vzájemných vazeb jednotlivých souborů lze zmínit následující: •

zdrojem primárních dat budou soubory |prijmy_2008-##-##.xlsx|, tyto soubory

obsahují příjmy za dny, konkrétně vždy za sedm dní, (resp. jednu směnu) v každém

uvedeném souboru; tato data je zapotřebí naimportovat do souboru •

|evidence.accdb|;

soubor |evidence.accdb| slouží ke shromáždění dat ze souborů

|prijmy_2008-##-##.xlsx|, ale zároveň poskytuje zdrojová data pro soubor

•

|prijmy_kt.xlsm|;

soubor |prijmy_kt.xlsm| obsahuje kontingenční tabulku, která umožňuje podrobně

analyzovat data ze souboru |evidence.accdb|.

2

3MA481


David Hach

Postup řešení 1.

Nejdříve v aplikacích Access a Excel povolíme všechna makra a v Excelu dále

povolíme všechna datová připojení a automatickou aktualizaci všech propojení sešitu.

Import dat z Excelu do Accessu 2.

Otevřeme soubor |evidence.accdb|.

4.

V editoru VBA z hlavní nabídky vybereme volbu |Insert| a z podnabídky následně

3.

5. 6.

Pomocí klávesové zkratky |levý alt+f11| se přesuneme do editoru VBA. |Module|. Tím dojde k vytvoření nového Modulu s názvem |Module1|.

V podokně |Properties| změníme vlastnost |(Name)| z hodnoty |Module1| na hodnotu |pracovni| a pomocí klávesové zkratky |ctrl+s| Modul uložíme.

Do námi vytvořeného Modulu |pracovni| vložíme nové makro pro import dat

z Excelu ze souborů |prijmy_2008-##-##.xlsx|, a to pro začátek zapsáním textu |Sub Import_Excelu| na nový řádek pod nápis |Option Compare Database|

a odešleme stisknutím klávesy |Enter|. Tím se nám vytvoří prostor pro další tvorbu tohoto makra.

7.

V tomto bodě si deklarujeme proměnné potřebné pro toto makro. Pod nápis |Sub

8.

Nyní stanovíme proměnným jejich obsah. Na další řádek proto zapíšeme |Cesta =

Import_Excelu()| napíšeme |Dim Cesta As String| a pod to |Dim Soubor As String|. CurrentProject.Path| (proměnná |Cesta| tedy bude nabývat aktuální cesty

k souboru |evidence.accdb|, což je pro nás nezbytné z důvodu možného přesouvání souborů). Na další řádek zapíšeme

|Soubor = Forms![soubory_d]![soubor_f].Value| (proměnná |Soubor| bude tedy 9.

nabývat hodnot z pole |[soubor_f]| formuláře |[soubory_d]|).

Nyní již máme stanoven význam proměnných, můžeme tedy přistoupit k napsání

kódu pro samotný import. Na další řádek tedy zapíšeme

|DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "prijmy",

Cesta & "\" & soubor & ".xlsx", True, "prijmy"|. Pro lepší pochopení jednotlivých náležitostí tohoto příkazu nám pomáhá interaktivní nápověda VBA, která se

3

3MA481


David Hach

zobrazuje během psaní kódu. Na vysvětlenou však uvedu jejich význam ve stejném

pořadí jako v kódu: •

• •

|DoCmd.TransferSpreadsheet acImport| - tím říkáme Accessu zhruba toto:

proveď příkaz importu hodnot z listu tabulkového kalkulátoru.

|acSpreadsheetTypeExcel12| - tímto určíme verzi Excelu, ze které chceme

importovat.

•

|"prijmy"| - je jméno tabulky Accessu, do které se má importovat.

•

a pevného textu.

•

|Cesta & "\" & Soubor & ".xlsx"| - je zřetězení hodnot našich proměnných |"True"| - je potvrzení, že oblast Excelu, kterou chceme importovat je pojmenována.

|"prijmy"| - je název pojmenované oblasti Excelu.

10.

Na další rádek ještě doplníme |MsgBox "Data byla úspěšně importována", , "Průběh

11.

Teď provedeme přiřazení tohoto kódu k události stisknutí u tlačítka |Importovat|

importu"|, což nás informuje v případě hladkého průběhu importu.

na formuláři |soubory_d|. To učiníme tak, že v editoru jazyka VBA otevřeme okno

pro psaní kódu pro formulář |soubory_d| (ve VBA pojmenováno Form_soubory_d).

12.

Z levé vyklápěcí nabídky v záhlaví tohoto okna vybereme volbu |importovat| (což je interní název tlačítka |Importovat|) a do míst, kam se nám automaticky umístí kurzor zapíšeme název námi vytvořeného prvního makra, tzn., |Import_Excelu|. Tím se tlačítko stane funkční.

4

3MA481


David Hach

13.

Teď postupně prostřednictvím formuláře |soubory_d| naimportujem data ze

14.

Máme-li všechna tato data naimportována, pokusíme se znovu naimportovat data

souborů |prijmy_2008-##-##.xlsx|.

např. ze souboru |prijmy_2008-12-04.xlsx|. Díky tomu dojde ke zobrazení

systémového dialogu Accessu. Vybereme na něm možnost |Ano|, data se naimportují znovu, předchozí data jsou přepsána.

15.

Bod 14 zopakujeme s tou výjimkou, že na systémovém dialogu Accessu vybereme

16.

Chceme zajistit, aby se tato chyba VBA již neopakovala, do kódu makra

|Ne|. V tu chvíli dojde k chybovému dialogu VBA. Na tomto dialogu zvolíme |End|.

|Import_Excelu| za řádek s textem

|Soubory = Forms![soubory_d]![soubor_f].Value| proto doplníme ještě |On Error

GoTo Zprava|. Na konec makra před řádek |End Sub| pak doplníme |Exit Sub| a na

5

3MA481


David Hach

další řádek potom

|Zprava: MsgBox "Některá z Dat, která se pokoušíte importovat, v tabulce Příjmy

již jsou. Importována proto nebyla žádná data. Chcete-li i přesto tato data znovu

naimportovat, akci opakujte a v systémovém dialogu zvolte volbu Ano.", , "Průběh|. Tím dojde k tomu, že místo chybového hlášení VBA dojde k zobrazení hlášení

definovaného námi, což je pro běžného uživatele rozhodně srozumitelnější. Pomocí klávesové zkratky |ctrl+s| Modul uložíme. Soubor |evidence.accdb| můžeme uzavřít.

Otevření kontingenční tabulky v Excelu 17.

18.

Otevřeme soubor |prijmy_kt.xlsm|.

Přejdeme na list |pomoc| do buňky |A3| a zapíšeme do ní vzorec

|="[prijmy_vystup-kt.xlsx]prijmy_d'!"&"R1C1"&":"&"R"&POČET2('[prijmy_vystup-

kt.xlsx]prijmy_d'!$A:$A)&"C10"|. Důležitá je především vnořená fce |POČET2|, díky které zjistíme počet neprázdných buněk ve sloupci |A| listu |prijmy_d| souboru

|prijmy_vystup-kt.xlsx|. Výsledek tohoto vzorce bude sloužit pro nastavení rozsahu zdrojových dat kontingenční tabulky |kt_prij|. Na výsledek tohoto vzorce se bude 19. 20. 21. 22. 23. 24. 25.

odkazovat proměnná |Zdroj| (viz bod 23 a 24).

Pomocí klávesové zkratky |levý alt+f11| se přesuneme do editoru VBA.

V editoru VBA z hlavní nabídky vybereme volbu |Insert| a z podnabídky následně |Module|. Tím dojde k vytvoření nového Modulu s názvem |Module1|.

V podokně |Properties| změníme vlastnost |(Name)| z hodnoty |Module1| na hodnotu |pracovni| a pomocí klávesové zkratky |ctrl+s| soubor uložíme.

Do modulu |pracovni| zapíšeme |Sub Prij_Prech| a odešleme stisknutím klávesy

|Enter|. Tím se nám vytvoří prostor pro další tvorbu tohoto makra.

Nejdříve si deklarujeme proměnné. Do makra proto zapíšeme |Dim Cesta As String| a na další řádek |Dim Zdroj As String|.

Nyní nadefinujeme obsah těmto proměnným. Na další řádek proto zapíšeme |Cesta = ActiveWorkbook.Path| a na další řádek

|Zdroj = Sheets("pomoc").Range("A3").Value|.

Protože nechceme, aby nám tzv. blikala obrazovka při běhu makra, na další řádek zapíšeme |Application.ScreenUpdating = False|.

6

3MA481 26. 27.


David Hach

Později se nám bude hodit, aby byla okna jednotlivých souborů Excelu na hlavním panelu Windows skryta pod jedno, to zajistíme zápisem

|Application.ShowWindowsInTaskbar = False| na další řádek. Nyní na další řádky zapíšeme |Sheets("kt").Select|

|ActiveSheet.PivotTables("kt_prij").ChangePivotCache

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "'" & Cesta & "\" & Zdroj, Version:=xlPivotTableVersion12)|.

Pozor, v rámci příkazu neřádkujeme! Chceme-li řádek zalomit uprostřed příkazu,

můžeme využít znaky | _| (mezera a podtržítko) a odeslat stiskem klávesy |Enter|. Tento příkaz nám umožní automaticky nastavit oblast zdrojových dat pro 28. 29. 30. 31. 32.

kontingenční tabulku |kt_prij|. Na další řádek zapíšeme

|ActiveSheet.PivotTables("kt_prij").PivotCache.Refresh|, což kontingenční tabulku aktualizuje.

Na další řádek zapíšeme

|ActiveSheet.PivotTables("kt_prij").PivotFields("datum").ShowDetail = False|, díky

čemuž se sbalí podrobnosti kontingenční tabulky.

Na další řádek zapíšeme |Application.ScreenUpdating = True|, což obnoví problikávání obrazovky.

Na konec tohoto makra zapíšeme ještě |Range("C8").Select|, což umožní

přemístění do výchozího bodu kontingenční tabulky.

Abychom si makro mohli vyzkoušet, musíme si otevřít soubor |prijmy_vystupkt.xlsx| a do jeho jediného listu do buňky |A3| zapíšeme hodnotu |14/11/08|.

Vrátíme se do souboru |prijmy_kt.xlsm| a makro spustíme. Soubor |prijmy_vystup33.

kt.xlsx| musí zůstat otevřen.

Protože chceme, aby se soubor |prijmy_vystup-kt.xlsx| otevíral automaticky,

vytvoříme si pro jeho otevření makro do souboru |prijmy_kt.xlsm|. Na další řádek

za text |End Sub| předchozího makra zapíšeme |Sub Prij_Vyst_Otev|, čímž nazveme 34.

další makro a odešleme stisknutím klávesy |Enter|.

Dále deklarujeme potřebnou proměnnou |Cesta| a to zápisem |Dim Cesta As String|.

7

3MA481


David Hach

35.

Na dalším řádku stanovíme obsah proměnné, kterým je opět |Cesta =

36.

Protože opět nechceme, aby nám tzv. blikala obrazovka při běhu makra, na další

37. 38.

ActiveWorkbook.Path|.

řádek zapíšeme |Application.ScreenUpdating = False|

Na dalším řádku už zapíšeme příkaz k otevření příslušného sešitu

|Workbooks.Open Filename:=Cesta & "\" & "prijmy_vystup-kt.xlsx"|.

Sešit |prijmy_vystup-kt.xlsx| chceme mít sice otevřen, chceme ho ale nechat na

pozadí, na další řádek zapíšeme proto příkaz

|Workbooks("prijmy_kt.xlsm").Activate|, čímž opět aktivujeme sešit s kontingenční 39. 40.

tabulkou.

Na závěr tohoto makra opět povolíme problikávání obrazovky, a to zápisem |Application.ScreenUpdating = True| na další řádek.

Budeme chtít, aby se tato dvě makra, tedy makro |Prij_Vyst_Otev| a makro

|Prij_Prech| provedla automaticky při otevření sešitu |prijmy_kt.xlsm|. To zajistíme tak, že v editoru jazyka VBA otevřeme okno pro psaní kódu pro samotný sešit.

41.

Z levé vyklápěcí nabídky v záhlaví tohoto okna vybereme volbu |Workbook| a do

míst, kam se nám automaticky umístí kurzor zapíšeme názvy obou maker, tzn. na

první řádek |Prij_Vyst_Otev| a na druhý řádek |Prij_Prech|. Pořadí tohoto zápisu je zcela zásadní! Tím se tato dvě makra spustí automaticky po otevření sešitu

|prijmy_kt.xlsm|. Nyní ještě přejdeme do listu |pomoc| souboru |prijmy_kt.xlsm|. Pomocí klávesové zkratky |ctrl+s| soubor uložíme. Zavřeme všechny soubory Excelu a spustíme soubor |prijmy_kt.xlsm|, abychom si makra vyzkoušeli. Po

opětovném otevření makra automaticky proběhnou, což poznáme mj. dle toho, že kurzor je teď na listu |kt| v buňce |C8|.

8

3MA481


David Hach

42.

Oba soubory Excelu můžeme zavřít.

43.

Nyní si opět otevřeme soubor |evidence.accdb|, pomocí klávesové zkratky |levý

Export dat z Accessu do Excelu

44. 45.

alt+f11| se přesuneme do editoru VBA a zapíšeme si do našeho modulu |pracovni| další makro, konkrétně s názvem |Export|.

I v tomto makru si zadeklarujeme proměnnou a definujeme její obsah, tj.: |Dim Cesta As String|

|Cesta = CurrentProject.Path|

Dále zapíšeme příkaz |DoCmd.OutputTo acOutputQuery, "prijmy_d",

acFormatXLSX, Cesta & "\prijmy_vystup-kt.xlsx", False|, který vyexportuje data

z tabulky |prijmy| do souboru |prijmy_vystup-kt.xlsx|. Poslední parametr |False|

46. 47.

tohoto příkazu zajistí, aby se soubor |prijmy_vystup-kt.xlsx| neotevřel.

Za toto makro si napíšeme další makro s názvem |Prijmy_Kt_zobraz|. V tomto novém makru si deklarujeme dvě proměnné, konkrétně:

|Dim Excel As Object|

|Dim Cesta As String|.

48.

Proměnná |Cesta| bude mít tentokrát trochu odlišnou náplň, a to

49.

Na další řádek zapíšeme |Set Excel = CreateObject("Excel.Application")|, což

50.

|Cesta = CurrentProject.Path & "\prijmy_kt.xlsm"|.

proměnnou |Excel| nastaví jako tvůrce objektu aplikace Excel.

Na další řádky si zapíšeme následující zápis:

|With Excel|

|.Visible = True|

|.Workbooks.Open (Cesta)| 51.

|End With|

Tato dvě vytvořená makra přiřadíme nyní k události stisknutí u tlačítka |Přehled|

(které je vnitřně pojmenováno jako |prehled|) formuláře |prijmy| souboru

9

3MA481


David Hach

|evidence.accdb|, a to tím. To učiníme tak, že v editoru jazyka VBA otevřeme okno

pro psaní kódu pro formulář |prijmy| a analogicky podle bodu 12 zapíšeme názvy 52. 53.

obou maker v pořadí |Export|, |Prijmy_Kt_Zobraz|.

Stiskem tlačítka |Přechod| si makra vyzkoušíme.

Po přechodu do sešitu |prijmy_kt.xlsx| bychom ještě chtěli, aby bylo možné pomocí tlačítka |Databáze| přejít zpět do Accessu a Excel zavřít. Napíšeme si proto

poslední makro s názvem |Konec| do modulu |pracovni| Excelu. Toto makro bude

obsahovat následující příkazy: • •

54.

|Application.ShowWindowsInTaskbar = True|, který umožní opět zobrazit všechna okna na hlavním panelu Windows;

•

|Application.Quit|, který ukončí aplikaci;

•

dialogu s dotazem na uložení sešitu Excelu (sešit nebude uložen);

|Application.DisplayAlerts = False|, který zamezí zobrazení systémového |Application.ActivateMicrosoftApp xlMicrosoftAccess|, který opět aktivuje aplikaci Access s otevřeným souborem |evidence.accdb|

Makro |Konec| přiřadíme tlačítku |Databáze|. To zajistíme tak, že v editoru jazyka

VBA otevřeme okno pro psaní kódu pro list |kt|. Z levé vyklápěcí nabídky

vybereme volbu |databaze|, což je interní název tlačítka |Databáze|. Do míst, kam se přemístí kurzor, zapíšeme |Konec| a pomocí klávesové zkratky |ctrl+s| soubor

55.

uložíme.

Uložíme všechny soubory a zavřeme je. Poté otevřeme soubor |evidence.accdb|

a funkcionalitu si vyzkoušíme. Na závěr je nutné podotknout, že celou aplikaci vždy otevřeme pouze a jenom spuštěním souboru |evidence.accdb|, ostatní soubory se

56.

buď nespouštějí nebo se spouštějí pomocí tlačítek přímo v aplikaci!

To je vše.

10

3MA481


David Hach

Použité informační zdroje 1.

WALKENBACH, John. Microsoft Excel 2003 : programování ve VBA. 1. vyd. Brno :

2.

HELD, Bernd. Access VBA : velká kniha řešení. 1. vyd. Brno : Computer Press, 2006.

Computer Press, 2006. 867 s. ISBN 80-251-0911-9. 639 s. ISBN 80-251-1112-1.

11

3MA481 Propojení Accessu a Excelu David Hach

Recommend Documents