Automatický import dat o firmě z registru ARES pomocí makra s XML dotazem

Tvoříte v Excelu šablonu faktury nebo objednávky? Potřebujete mít v excelovské databázi aktuální informace o vašich obchodních partnerech? Pak tyto informace nemusíte zadávat manuálně. Zadáte IČO a zbylé údaje se samy stáhnou z internetu. A jak na to? Pojďme krok za krokem.

(pokud Vám stačí jednoduché jednorázové zjištění informací dle IČ, podívejte se na tento návod)

Připravíme si fomulář v Excelu

Připravíme si v Excelu formulář, do kterého chceme údaje automaticky načíst. Formulář bude obsahovat informace: IČO, Název firmy, adresa popř. jiné další. Může vypadat např. takto

obr1

Najdeme registr ARES na internetu

Na adrese http://wwwinfo.mfcr.cz/ares/ares_es.html.cz je provozováno rozhraní do registru. My hledáme možnost, jak dotazy na firmy posílat z Excelu.

To lze řešit pomocí makra, ve kterém použijeme tento odkaz: http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_bas.cgi?ico=27074358, kde poslední parametr je ičo. Více o dalších parametrech a možnostech dotazování najdeme na http://wwwinfo.mfcr.cz/ares/ares_xml_basic.html.cz.

Jak bude makro pracovat

Než ťukneme do klávesnice, měli bychom si říci, jak má makro zhruba fungovat. Řekněme, že chceme, aby byl proces následující:

  • Do buňky C2 zadáme IČO
  • Klikneme na tlačítko, které makro spustí
  • Makro natáhne data do pomocného listu, který se předtím vytvoří
  • Do buněk C4 C6 C8 a C10 se zkopírují údaje z pomocného listu
  • Pomocný list se smaže
  • Na závěr se zobrazí hlášení, že je vše hotovo.

V makru, kde budeme pracovat s pomocnými listy a pak je odstraňovat, je dobré využít možnosti potlačit obnovování obrazovky během běhu makra a také potlačení chybových hlášek. To bude mít za efekt zrychlení celého makra a nebude docházet k „problikávání“ listů apod. během běhu makra. Na konci makra je pak nutné oba potlačené prvky opět obnovit. Jak na to je uvedeno níže přímo v kódu makra.

Zapíšeme kód makra

Spustíme editor maker (ikona Visual Basic) ,která je na záložce vývojář. Po načtené okna vybereme v levé části okna položku „This Workbook“, neboť chceme makro použít v rámci jednoho souboru.

Následující kód makra si můžete zkopírovat přímo do editoru, jednotlivé kroky makra jsou popsány komentáři přímo v makru:

Chcete-li se podívat, jaké další údaje se z ARESU stahují, stačí, pokud odmažete poslední příkaz makra a pomocný list tak po importu nezmizí. Pak se budete moci podívat, jaká data se stáhla a v jakých buňkách je najdete pro další zpracování.

Dokončíme formulář a makro spustíme

Pokud jsme makro do editoru vložili, vraťme se zpět do listu z formulářem, kde ještě přidáme tlačítko, které makro vyvolá. To provedeme takto

  • Jdeme na záložku Vývojář -> Vložit -> ovládací prvky formuláře -> Tlačítko.
  • tlačítko „nakreslíme“ v prostoru listu excelu myší do požadovaných rozměrů
  • Ihned po té se nám zobrazí okno s možností přiřadit tomuto tlačítku makro. Vybereme makro s názvem : ThisWorkbook.ares.
  • Tlačítko vhodně pojmenujeme
  • Vepíšeme do buňky C2 nějaké ičo, u kterého chceme doplnit zbylé údaje
  • A je to! Zde je výsledek:

obr2

Pokud je to pro vás lepší, můžete si stáhnout hotový soubor ares.xlsm, který již obsahuje funkční formulář.

ares.xlsx

15 comments on “Automatický import dat o firmě z registru ARES pomocí makra s XML dotazem
  1. xlnc napsal:

    J2: POZOR! Hrozí zablokování Vaší IP adresy! Prosím čtěte http://wwwinfo.mfcr.cz/ares/ares_xml_standard.html.cz#max

  2. xlnc napsal:

    Nabízím náznak alternativního řešení:

    Sub VypisARES()

    ‚Tools/References, Microsoft XML, v6.0

    Dim strURL As String
    Dim strFirma As String
    Dim strMesto As String
    Dim strUlice As String
    Dim strCisloDomovni As String
    Dim strPSC As String

    ‚http://wwwinfo.mfcr.cz/ares/ares_xml_standard.html
    ‚http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=27074358

    ‚Asseco Central Europe, a.s.
    ‚202724222
    ‚203
    ‚Hlavní město Praha
    ‚Praha
    ‚Michle
    ‚Praha 4
    ‚Budějovická
    ‚778
    ‚3a
    ‚14000

    ‚URL adresa
    strURL = „http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=27074358“

    Dim oXml As MSXML2.DOMDocument60
    Set oXml = New MSXML2.DOMDocument60

    With oXml

    ‚načtení XML
    .async = False
    .Load strURL

    ‚načtení hodnot konkrétních nodů/tagů
    strFirma = .getElementsByTagName(„are:Obchodni_firma“).Item(0).Text
    strMesto = .getElementsByTagName(„dtt:Nazev_obce“).Item(0).Text
    strUlice = .getElementsByTagName(„dtt:Nazev_ulice“).Item(0).Text
    strCisloDomovni = _
    .getElementsByTagName(„dtt:Cislo_domovni“).Item(0).Text
    strPSC = .getElementsByTagName(„dtt:PSC“).Item(0).Text

    End With

    ‚odstranění z paměti
    Set oXml = Nothing

    End Sub

  3. JP napsal:

    Mám takový pocit, že se mi podařilo zablokovat přístup na infomfcr. Co se s tím dá dělat ?

  4. Marek David napsal:

    Dobrý den,

    nevíte, jak by takovýto skript vypadal v OpenOffice?

  5. Jiří Beran napsal:

    S opravdu velkou dávkou štěstí by mohl pomoci tenhle konvertor (nebo nějaký podobný):
    http://www.business-spreadsheets.com/vba2oo.asp

  6. Marek David napsal:

    Děkuji za odpověď. Ten jsem již zkoušel, ale bez úspěchu bohužel.

  7. Petr W napsal:

    Dobrý den. Funguje to.
    Pro mou potřebu bych to ale potřeboval upravit tak, aby to doplnilo najednou adresy u více subjektů.
    Například, kdybych měl ve sloupci A nasekaná IČa jednotlivých subjektů by to mohlo doplnit sloupce B,C,D,E adresou.

    Nemá někdo nápad?
    Díky

  8. Jiří Beran napsal:

    Dobrý den, prostě byste makro volal vícekrát a ukládal makrem za sebou.
    Je je možné, jak upozorňuje jeden z diskutujících tady, že po mnohonásobném volání vám zdroj omezí přístup. Nemám s tím ale žádnou vlastní zkušenost…

  9. Marek David napsal:

    A mohl by sem někdo nahrát fungující makro v ODF tedy? Mně stránka s konvertorem nefunguje. Předem děkuji.

  10. Jakub napsal:

    Dobrý den,

    díky moc za skvělý kod. Bylo by možné jej upravit tak, aby se hledalo nejen mezi aktivními subjekty, ale zobrazovaly se i výsledky vyhledávání pro zaniklé subjekty?

    Děkuji.

    Jakub

  11. Jakub napsal:

    Už asi vím 🙂

    ActiveWorkbook.XmlImport ActiveWorkbook.XmlImport URL:=“http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_std.cgi?ico=“ & Sheets(1).Range(„C2“).Value & „&aktivni=false“, ImportMap:=Nothing, Overwrite:=True, Destination:=Range(„$A$1“)

  12. David napsal:

    Dobrý den, děkuji za tento návod. Jen by mě zajímalo jestli je možné nějak zrychlit toto makro. Docela dlouho trvá než se vygenerují požadovaná data.

  13. David napsal:

    Ještě prosím o informaci jak zjistit range (např. bych chtěl přidat DIČ)

  14. Ales napsal:

    Davide, prip. ostatni, mate nekdo hotovy XLS soubor, ktery na zaklade ICO ve sloupci automaticky doplni dalsi udaje? Velmi by mi to pomohlo. Diky Ales Email na me: ales.agenda@seznam.cz

  15. nika napsal:

    a co takhle ještě číslo popisné a evidenční?

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

FB CZ

TOPlist