S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

V jednom z minulých článků jsme publikovali makro, které umí zkopírovat data z jednoho předem daného souboru do druhého. Co když ale soubor, ze kterého chceme data načíst, zatím neznáme? Řešením je napsat makro, která se nejprve zeptá, který soubor chceme otevřít.

Řekněme, že naším cílem bude do buňky C10 na listu 1 souboru, ve kterém máme makro, zkopírovat hodnotu ze souboru, který teprve vybereme. Přičemž víme, že hodnota se nachází v buňce B5

Co je dobré vědět předem?

  • Ve VBA existuje více možností, jak vyvolat dialogové okno pro otevření souboru. My použijeme metodu Application.FileDialog, což je univerzální možnost pro vyvolání dialogového okna pro práci se soubory. Navíc obsahuje celou řadu možných nastavení.
  • Je rozumné ošetřit základní chybové stavy. Například pokud uživatel v dialogovém okně nezvolí žádný soubor nebo naopak zvolí více než jeden soubor
  • Všimněte si položky .SelectedItems(1), do které se nahraje adresa označeného souboru. Pokud budete chtít vybrat více než jeden soubor, budete pouze měnit číslovku v závorce a můžete adresy načíst do různých proměnných nebo do pole.

Zdrojový kód makra

Zdrojový kód makra je opatřen zkrácenými komentáři. Plné vysvětlení příkazů naleznete v další části článku.

Sub nacti_z_vybraneho_souboru()
    Application.DisplayAlerts = False 'potlačí excelová hlášení (např při zavírání souborů)
    With Application.FileDialog(msoFileDialogFilePicker) 'spustí dialogové okno pro otevření
       .InitialFileName = "C:\Users\Dell\Dropbox\data" 'nastavení úvodní složky procházení
       .Title = "Vyber adresář" 'nastavení názvu okna
       .Filters.Add "Soubory Excelu (xls/xlsx)", "*.xl*", 1 'nastavení filtru pro zobrazení souborů
       .Show
        If .SelectedItems.Count = 0 Then
           MsgBox "Nebyly načteny žádné soubory": Exit Sub 'pokud není vybrán žádný soubor, makro vypíše hlášení a ukončí se
        ElseIf .SelectedItems.Count > 1 Then
           MsgBox "Vyberte pouze jeden soubor!": Exit Sub 'pokud je vybráno více, než jeden soubor, makro vypíše hlášení a ukončí se
        Else
        zdrojovy_soubor = .SelectedItems(1) ' načte adresu souboru do proměnné
        End If
    End With  
   Workbooks.Open (zdrojovy_soubor) ' otevření souboru, který jsme vybrali
   docasna = ActiveWorkbook.Worksheets("List1").Range("B5") ' uložení hodnoty z buňky B5 na listu 1 do proměnné
   ActiveWorkbook.Close  'zavření sešitu
   ThisWorkbook.Activate ' aktivace původního souboru
   Worksheets("List1").Range("C10") = docasna ' uložení hodnoty z proměnné do buňky
   Application.DisplayAlerts = True 'opětovné povolení excelovských hlášené
End Sub

Jak makro pracuje?

  • Nejprve příkazem Application.DisplayAlerts = False potlačíme výstražná hlášení excelu. To by nám komplikovalo běh makra v okamžiku, kdy makro zavírá soubor a excel se standardně ptá, zda chceme soubor opravdu zavřít.
  • Příkazem With Application.Filedialog….End With se vyvolá dialogové okno pro výběr souboru s následujícími parametry
    • InitialFileName = adresář, jež bude v okně nastaven jako výchozí
    • Title = název dialogového okna
    • Filters.Add = zařídí, že se v okně objeví pouze typy souborů, které se nám hodí. V prvních uvozovkách je uveden text, který uživatel uvidí, v druhých je samotná definice filtru. Číslo na posledním místě určuje pořadí filtru, pokud jich nastavíme více.
    • Show = zařídí samotné zobrazení okna
    • Mezi příkazy If ..End If jsou ošetřeny chybové stavy. pokud není vybrán žádný soubor, uživateli se vypíše hlášení a makro je ukončeno. Podobně se postupuje, pokud uživatel vybere více, než jeden soubor. v případě, že je vše v pořádku, načte se adresa souboru do proměnné zdrojovy_soubor
  • Příkaz Workbooks.Open(zdrojovy_soubor) otevře vybraný soubor
  • Dále se příkazem docasna = ActiveWorkbook.Worksheets(“List1”).range(“B5”) načte do proměnné hodnotu z buňky B5 z listu 1 aktovního souboru, tedy toho, který jsme zvolili. 
  • Příkaz ActiveWorkbook.Close pak sešit zavře
  • Příkaz ThisWorkbook.Activate aktivuje zpět tento soubor( soubor, ve kterém je toto makro). Tento příkaz není nutný, pokud pracujete pouze s jedním dalším souborem, neboť po jeho uzavření se aktivuje tento soubor automaticky.
  • Worksheets(“List1”).Range(“C10”) = docasna načte z proměnné hodnotu do buňky C10
  • Po té se příkazem Application.DisplayAlerts = true opět povolý excelová hlášení a makro se ukončí

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

  1. Dobrý den,

    skvěle vysvětleno, funguje, děkuji 🙂
    Chtěla bych se ještě zeptat – jak je potřeba makro upravit, když chceme udělat to samé, ale načíst najednou více souborů, které se potom mají propsat do jednoho souboru hezky pod sebe? Čili jeden soubor = jeden řádek s kopírovanými hodnotami.

    Předem moc děkuji za radu,
    Pohodový den,

  2. Dobrý den,

    moc děkuji, podívám se na to, ale bojím se, že mi to problém nevyřeší. Soubory, ze kterých se to bude brát jsou takové výpočetní šablony (podobné formuláři), které potřebujeme seskupit (např. 20 šablon do jedné přehledné tabulky), takže to makro by si to tahalo vždy z konkrétní buňky, která je ve všech šablonách stejná.

    Podívám se na odkaz od Vás.
    Děkuji za pomoc a ochotu 🙂
    Hezký den,

  3. To by neměl být problém. V Power Query se dají snadno odebírat řádky a sloupce, takže si ze stránky vytáhnete přesně to, co potřebujete.

  4. Dobrý den,
    jde zařídit, aby otevíraný soubor nebyl vidět?
    Dalibor.

  5. Dobrý den,
    lze kopírovat různý rozsah dat ze zdrojového souboru do různého rozsahu dat do cílového souboru? Protože takto napsáno se musí rozsahem zdrojová a cílová data shodovat.
    Velice děkuji!

  6. Dobrý den,
    moc děkuji za super rady a tipy… Měla bych ale jeden dotaz – výše uvádíte způsob jak “potlačit excelová hlášení”.
    Můžete mi prosím poradit jak takto potlačit nebo spíš potvrdit dotaz na aktualizování sešitu, který makrem otvírám a z něhož si poté určitá data beru… – jde o to, že kvůli jednomu potvrzení se mi sekne samostatně fungující makro 🙁
    Děkuji Zuzka

  7. Dobrý den,

    hledal bych makro, které by dokázalo otevřít načíst data z csv souboru s proměnným názvem.

    Jde mi o to, že vždy každý den si stáhnu data v csv akutální pro jeden den do stejného umístění, ale pro nahrání do excelu bych si chtěl zvolit jaký den chci otevřít. šlo by?

    Děkuji moc!

    Petr

  8. Dobrý den
    Je to téměř dokonalé, jen bych potřebova kopírovat rozasah buněk z jednoho XLS souboru (například A1 až F20) do tohoto souboru s makkrem.
    Poradíte?
    Děkuji.
    Pavel

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář