Своечасовы запуск макраса

Вельмі распаўсюджаны выпадак на практыцы: вам трэба запусціць адзін або некалькі вашых макрасаў у пэўны час або з пэўнай частатой. Напрыклад, у вас ёсць вялікая і цяжкая справаздача, якая абнаўляецца паўгадзіны, і вы хочаце запусціць абнаўленне за паўгадзіны да прыходу на працу раніцай. Ці ў вас ёсць макрас, які павінен аўтаматычна рассылаць электронныя лісты супрацоўнікам з пэўнай частатой. Ці, калі вы працуеце са зводнай табліцай, вы хочаце, каб яна абнаўлялася на хаду кожныя 10 секунд, і гэтак далей.

Давайце паглядзім, што Excel і Windows маюць магчымасць рэалізаваць гэта.

Запуск макраса з зададзенай частатой

Самы просты спосаб зрабіць гэта - выкарыстоўваць убудаваны метад VBA Дадатак.OnTimeЯкі запускае ўказаны макрас у зададзены час. Давайце разбярэмся ў гэтым на практычным прыкладзе.

Адкрыйце рэдактар ​​Visual Basic з дапамогай аднайменнай кнопкі на ўкладцы распрацоўшчык (Распрацоўшчык) або спалучэнне клавіш Alt+F11, устаўце новы модуль праз меню Устаўка – модуль і скапіруйце туды наступны код:

Dim TimeToRun 'глабальная зменная, дзе захоўваецца час наступнага запуску 'гэта асноўны макрас Sub MyMacro() Application.Calculate 'пералічыць кнігу Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill ячэйка A1 са выпадковым колерам :) Выклік NextRun 'запусціце макрас NextRun, каб усталяваць час наступнага выканання End Sub 'гэты макрас усталёўвае час для наступнага запуску асноўнага макраса Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'дабавіць 3 секунды да бягучага часу Application.OnTime TimeToRun, "MyMacro" 'запланаваць наступны запуск End Sub 'макрас для запуску паслядоўнасці паўтору Sub Start() Выклік NextRun End Sub 'макрас для спынення паслядоўнасці паўтору Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Давайце разбяромся, што тут да чаго.

Па-першае, нам патрэбна зменная, якая будзе захоўваць час наступнага запуску нашага макраса - я назваў гэта TimeToRun. Звярніце ўвагу, што змесціва гэтай зменнай павінна быць даступна для ўсіх нашых наступных макрасаў, таму нам трэба зрабіць глабальнай, г.зн. абвясціць у самым пачатку модуля перад першым Sub.

Далей ідзе наш галоўны макрас MyMacro, які будзе выконваць асноўную задачу – пералічваць кн., выкарыстоўваючы метад Прымяненне.Вылічыць. Каб было больш зразумела, я дадаў на ліст у ячэйку A1 формулу =TDATE(), якая адлюстроўвае дату і час – пры пераразліку яе змесціва абнаўляецца прама на вачах (проста ўключыць адлюстраванне секунд у ячэйцы фармат). Для дадатковага задавальнення я таксама дадаў у макрас каманду для запаўнення ячэйкі A1 выпадкова абраным колерам (код колеру з'яўляецца цэлым лікам у дыяпазоне 0..56, які генеруецца функцыяй Rnd і акругляе да цэлага ліку Int).

Macro NextRun дадае да папярэдняга значэння TimeToRun Яшчэ 3 секунды, а затым плануе наступны запуск асноўнага макраса MyMacro для гэтага новага часу. Вядома, на практыцы вы можаце выкарыстоўваць любыя іншыя патрэбныя вам інтэрвалы часу, усталяваўшы аргументы функцыі Значэнне часу у фармаце чч:хх:сс.

І, нарэшце, проста для зручнасці было дададзена больш макрасаў запуску паслядоўнасці. Галоўная і яго завяршэнне Заканчэнне. Апошні выкарыстоўвае чацвёрты аргумент метаду, каб разарваць паслядоўнасць. Своечасова роўным фальшывы.

Усяго, калі запусціць макрас Галоўная, тады ўся гэтая карусель закруціцца, і мы ўбачым на аркушы такую ​​карцінку:

Вы можаце спыніць паслядоўнасць, запусціўшы, адпаведна, макрас Заканчэнне. Для зручнасці вы можаце прызначыць спалучэнні клавіш для абодвух макрасаў з дапамогай каманды Макрасы – Параметры таб распрацоўшчык (Распрацоўшчык — Макрасы — Параметры).

Запуск макраса па раскладзе

Вядома, усё апісанае вышэй магчыма толькі ў тым выпадку, калі ў вас запушчаны Microsoft Excel і ў ім адкрыты наш файл. Зараз давайце паглядзім на больш складаны выпадак: вам трэба запускаць Excel па зададзеным раскладзе, напрыклад, кожны дзень у 5:00, адкрываць у ім вялікі і складаны справаздачу і абнаўляць у ім усе злучэнні і запыты, каб ён быць гатовым да таго часу, як мы прыедзем на працу 🙂

У такой сітуацыі лепш выкарыстоўваць Планавальнік Windows – спецыяльна ўбудаваная ў любую версію Windows праграма, якая можа выконваць вызначаныя дзеянні па раскладзе. Фактычна, вы ўжо выкарыстоўваеце яго, самі таго не ведаючы, таму што ваш ПК рэгулярна правярае наяўнасць абнаўленняў, спампоўвае новыя антывірусныя базы, сінхранізуе воблачныя папкі і г. д. Усё гэта праца Планавальніка. Такім чынам, наша задача - дадаць да існуючых задач яшчэ адну, якая будзе запускаць Excel і адкрываць у ім паказаны файл. І мы павесім наш макрас на падзею Працоўны сшытак_Адкрыць гэты файл – і праблема вырашана.

Адразу папярэджваю, што для працы з Планавальнікам могуць спатрэбіцца пашыраныя правы карыстальніка, таму, калі вы не можаце знайсці апісаныя ніжэй каманды і функцыі на працоўным кампутары ў офісе, звярніцеся па дапамогу да ІТ-спецыялістаў.

Запуск планавальніка

Такім чынам, давайце запусцім планавальнік. Каб зрабіць гэта, вы можаце:

• Пстрыкніце кнопку правай кнопкай мышы дома і абярыце кіраванне кампутарам (Камп'ютарнае кіраванне)
• Выберыце на панэлі кіравання: Адміністраванне – планавальнік задач (Панэль кіравання — Інструменты адміністравання — Планіроўшчык задач)
• Выберыце ў галоўным меню Пуск – Аксэсуары – Сістэмныя інструменты – Планавальнік задач
• Націсніце спалучэнне клавіш Выйграць+R, увядзіце taskschd.msc і націсніце Уводзіць

На экране павінна з'явіцца наступнае акно (у мяне ёсць англійская версія, але вы таксама можаце мець версію):

Стварыце заданне

Каб стварыць новую задачу з дапамогай простага пакрокавага майстра, націсніце на спасылку Стварыце простае заданне (Стварыць базавую задачу) на правай панэлі.

На першым кроку майстра увядзіце назву і апісанне ствараемай задачы:

Націсніце на кнопку наступны (Далей) і на наступным этапе выбіраем трыгер - частату запуску або падзея, якое будзе запускаць нашу задачу (напрыклад, уключэнне кампутара):

Калі вы абралі Daily (штодня), то на наступным этапе вам трэба будзе выбраць пэўны час, дату пачатку паслядоўнасці і крок (кожны 2-й дзень, 5-ы дзень і г.д.):

Наступны крок - выбраць дзеянне - Запусціце праграму (Запуск праграмы):

І, нарэшце, самае цікавае, што менавіта трэба адкрыць:

У Праграму або скрыпт (Праграма/скрыпт) неабходна ўвесці шлях да Microsoft Excel як праграмы, гэта значыць непасрэдна да выкананага файла Excel. На розных камп'ютарах з рознымі версіямі Windows і Office гэты файл можа знаходзіцца ў розных папках, таму вось некалькі спосабаў даведацца пра яго месцазнаходжанне:

• Пстрыкніце правай кнопкай мышы значок (ярлык), каб запусціць Excel на працоўным стале або на панэлі задач і абярыце каманду матэрыялы (Уласцівасці), а затым у якое адкрылася акне скапіруйце шлях з радка Мэта:

                       

• Адкрыйце любую кнігу Excel, затым адкрыйце Дыспетчар задач (Дыспетчар задач) штурхаючы Ctrl+Alt+ад і пстрыкнуўшы правай кнопкай мышы на радку Microsoft Excel, абярыце каманду матэрыялы (Уласцівасці). У якое адкрылася акне вы можаце скапіяваць шлях, не забываючы дадаць да яго зваротную касую рысу і EXCEL.EXE у канцы:

               

• Адкрыйце Excel, адкрыйце рэдактар ​​Visual Basic з дапамогай спалучэння клавіш Alt+F11, адкрытая панэль Неадкладны спалучэнне Ctrl+G, увядзіце ў яго каманду:

  ? Application.Path

  … і націсніце Уводзіць

  

  Скапіруйце атрыманы шлях, не забываючы дадаць да яго зваротную касую рысу і EXCEL.EXE у канцы.

У Дадаць аргументы (неабавязкова) (Дадайце аргументы (неабавязкова)) вам трэба ўставіць поўны шлях да кнігі з макрасам, які мы хочам адкрыць.

Калі ўсё ўведзена, націсніце наступны , А затым Заканчэнне (Заканчэнне). У агульны спіс неабходна дадаць задачу:

Кіраваць створанай задачай зручна з дапамогай кнопак справа. Тут вы можаце праверыць заданне, неадкладна запусціўшы яго (бег)не чакаючы пазначанага часу. Вы можаце часова адключыць задачу (Адключыць)каб ён спыніўся на пэўны перыяд часу, напрыклад, у адпачынак. Ну, вы заўсёды можаце змяніць параметры (даты, час, імя файла) праз кнопку матэрыялы (Уласцівасці).

Дадайце макрас, каб адкрыць файл

Цяпер засталося павесіць у нашай кнізе запуск патрэбнага нам макраса на падзею адкрыцця файла. Для гэтага адкрыйце кнігу і перайдзіце ў рэдактар ​​Visual Basic з дапамогай спалучэння клавіш Alt+F11 або кнопкі Візуальны Бейсик таб распрацоўшчык (Распрацоўшчык). У якое адкрылася акне ў левым верхнім куце трэба знайсці на дрэве наш файл і падвойным пстрычкай адкрыць модуль гэтая кніга (Гэты сшытак).

Калі вы не бачыце гэтага акна ў рэдактары Visual Basic, то вы можаце адкрыць яго праз меню View - Project Explorer.

У якое адкрылася акне модуля дадайце апрацоўшчык падзеі адкрыцця кнігі, выбраўшы яго з выпадальных спісаў уверсе рабочы сшытак и адкрыты, адпаведна:

На экране павінен з'явіцца шаблон працэдуры. Працоўны сшытак_Адкрыць, дзе паміж радкоў Прыватны суб и End Sub і вам трэба ўставіць тыя каманды VBA, якія павінны аўтаматычна выконвацца пры адкрыцці гэтай кнігі Excel, калі планавальнік адкрывае яе ў адпаведнасці з раскладам. Вось некалькі карысных опцый для разгону:

• ThisWorkbook.RefreshAll – Абнаўляе ўсе знешнія запыты даных, запыты Power Query і зводныя табліцы. Самы універсальны варыянт. Толькі не забудзьцеся дазволіць падключэнне да знешніх даных па змаўчанні і абнаўляць спасылкі праз Файл – Параметры – Цэнтр даверу – Параметры цэнтра даверу – Знешняе змесціва, інакш пры адкрыцці кнігі з'явіцца стандартнае папярэджанне і Excel, нічога не абнаўляючы, будзе чакаць вашага блаславення ў выглядзе націску на кнопку Уключыць кантэнт (Уключыць кантэнт):

  

• ActiveWorkbook.Connections(“Імя_злучэння”).Абнавіць — абнаўленне дадзеных аб злучэнні Connection_Name.
• Аркушы («Аркуш5“).Зводныя табліцы(“Зводная табліца1«).PivotCache.Refresh – абнаўленне адной зводнай табліцы пад назвай Зводная табліца1 на аркушы Sheet5.
• Прымяненне.Вылічыць – пераразлік усіх адкрытых кніг Excel.
• Application.CalculateFullRebuild – прымусовы пераразлік усіх формул і перабудова ўсіх залежнасцяў паміж ячэйкамі ва ўсіх адкрытых кнігах (эквівалентна паўторнаму ўводу ўсіх формул).
• Рабочыя аркушы(“Справаздача”).Раздрукоўка – друкаваны ліст фатаграфіі.
• Выклік MyMacro – запусціць макрас з імем MyMacro.
• ThisWorkbook.Save – захаваць бягучую кнігу
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Цяпер, “:”, “-“) & “.xlsx” – захаваць кнігу ў тэчку Д:Архіў пад назвай фатаграфіі з датай і часам да назвы.

Калі вы хочаце, каб макрас выконваўся толькі пры адкрыцці файла Планавальнікам у 5:00 раніцы, а не кожны раз, калі карыстальнік адкрывае кнігу на працягу працоўнага дня, то мае сэнс дадаць праверку часу, напрыклад:

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

Гэта ўсе. Не забудзьцеся захаваць сваю працоўную кнігу ў фармаце з падтрымкай макрасаў (xlsm або xlsb), і вы зможаце бяспечна закрыць Excel і пайсці дадому, пакінуўшы кампутар уключаным. У пэўны момант (нават калі ПК заблакіраваны) Планавальнік запусціць Excel і адкрые ў ім зададзены файл, а наш макрас выканае запраграмаваныя дзеянні. А вы будзеце раскашавацца ў ложку, пакуль ваш цяжкі справаздачу аўтаматычна пералічваецца - прыгажосць! 🙂

• Што такое макрасы, як імі карыстацца, куды ўставіць код Visual Basic у Excel
• Як стварыць уласную надбудову макрасаў для Excel
• Як выкарыстоўваць Personal Macro Workbook як бібліятэку для вашых макрасаў у Excel