Menjalankan makro tepat waktu

Kasus yang sangat umum dalam praktik: Anda perlu menjalankan satu atau lebih makro Anda pada waktu tertentu atau pada frekuensi tertentu. Misalnya, Anda memiliki laporan besar dan berat yang diperbarui setengah jam dan Anda ingin menjalankan pembaruan setengah jam sebelum Anda tiba di tempat kerja di pagi hari. Atau Anda memiliki makro yang secara otomatis mengirimkan email ke karyawan pada frekuensi tertentu. Atau, saat bekerja dengan PivotTable, Anda ingin agar PivotTable diperbarui dengan cepat setiap 10 detik, dan seterusnya.

Mari kita lihat kemampuan Excel dan Windows untuk mengimplementasikan ini.

Menjalankan makro pada frekuensi tertentu

Cara termudah untuk melakukannya adalah dengan menggunakan metode VBA bawaan Aplikasi.OnTimeYang menjalankan makro yang ditentukan pada waktu yang ditentukan. Mari kita pahami ini dengan contoh praktis.

Buka editor Visual Basic dengan tombol dengan nama yang sama pada tab pembangun (Pengembang) atau pintasan keyboard lain+F11, masukkan modul baru melalui menu Sisipkan – Modul dan salin kode berikut di sana:

Dim TimeToRun 'variabel global tempat waktu run berikutnya disimpan 'ini adalah makro utama Sub MyMacro() Application.Calculate' hitung ulang buku Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'isi sel A1 dengan warna acak :) Panggil NextRun 'jalankan makro NextRun untuk mengatur waktu berjalan berikutnya End Sub' makro ini mengatur waktu untuk menjalankan makro utama berikutnya Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'tambahkan 3 detik ke waktu saat ini Application.OnTime TimeToRun, "MyMacro" 'jadwalkan run berikutnya End Sub 'makro untuk memulai urutan pengulangan Sub Start() Panggil NextRun End Sub 'makro untuk menghentikan urutan pengulangan Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Mari kita cari tahu apa yang ada di sini.

Pertama, kita membutuhkan variabel yang akan menyimpan waktu menjalankan makro kita berikutnya – saya menyebutnya Waktunya untuk lari. Harap dicatat bahwa konten variabel ini harus tersedia untuk semua makro berikutnya, jadi kami harus membuatnya global, yaitu mendeklarasikan di awal modul sebelum yang pertama Sub.

Berikutnya adalah makro utama kami Makro Saya, yang akan melakukan tugas utama – menghitung ulang buku menggunakan metode Aplikasi. Hitung. Agar lebih jelas, saya menambahkan rumus =TDATE() ke lembar di sel A1, yang menampilkan tanggal dan waktu – ketika dihitung ulang, isinya akan diperbarui tepat di depan mata kita (hidupkan saja tampilan detik di sel format). Untuk kesenangan ekstra, saya juga menambahkan ke makro perintah untuk mengisi sel A1 dengan warna yang dipilih secara acak (kode warna adalah bilangan bulat dalam kisaran 0, yang dihasilkan oleh fungsi Rnd dan membulatkan ke atas ke fungsi bilangan bulat Int).

Makro BerikutnyaJalankan menambah nilai sebelumnya Waktunya untuk lari 3 detik lagi dan kemudian jadwalkan makro utama berikutnya Makro Saya untuk waktu baru ini. Tentu saja, dalam praktiknya, Anda dapat menggunakan interval waktu lain yang Anda butuhkan dengan mengatur argumen fungsi Nilai waktu dalam format jj:mm:dd.

Dan akhirnya, hanya untuk kenyamanan, lebih banyak makro peluncuran urutan telah ditambahkan. Beranda dan penyelesaiannya Finish. Yang terakhir menggunakan argumen metode keempat untuk memecahkan urutan. Pada waktu sama Salah.

Total jika Anda menjalankan makro Beranda, maka seluruh carousel ini akan berputar, dan kita akan melihat gambar berikut pada sheet:

Anda dapat menghentikan urutan dengan menjalankan, masing-masing, makro Finish. Untuk kenyamanan, Anda dapat menetapkan pintasan keyboard ke kedua makro menggunakan perintah Makro – Opsi tab pembangun (Pengembang — Makro — Opsi).

Menjalankan makro sesuai jadwal

Tentu saja, semua yang dijelaskan di atas hanya mungkin jika Anda menjalankan Microsoft Excel dan file kami terbuka di dalamnya. Sekarang mari kita lihat kasus yang lebih rumit: Anda perlu menjalankan Excel sesuai dengan jadwal yang diberikan, misalnya, setiap hari pukul 5:00, buka laporan besar dan kompleks di dalamnya dan perbarui semua koneksi dan kueri di dalamnya sehingga akan bersiaplah pada saat kita tiba di tempat kerja

Dalam situasi seperti itu, lebih baik menggunakan Penjadwal Windows – sebuah program yang dibangun secara khusus ke dalam versi Windows apa pun yang dapat melakukan tindakan tertentu sesuai jadwal. Bahkan, Anda sudah menggunakannya tanpa menyadarinya, karena PC Anda secara teratur memeriksa pembaruan, mengunduh basis data anti-virus baru, menyinkronkan folder cloud, dll. Itu semua pekerjaan Penjadwal. Jadi tugas kita adalah menambahkan tugas yang ada ke tugas lain yang akan meluncurkan Excel dan membuka file yang ditentukan di dalamnya. Dan kami akan menggantung makro kami di acara tersebut Buku Kerja_Buka file ini – dan masalahnya terpecahkan.

Saya ingin segera memperingatkan Anda bahwa bekerja dengan Penjadwal mungkin memerlukan hak pengguna tingkat lanjut, jadi jika Anda tidak dapat menemukan perintah dan fungsi yang dijelaskan di bawah ini pada komputer kerja Anda di kantor, hubungi spesialis TI Anda untuk mendapatkan bantuan.

Meluncurkan Penjadwal

Jadi mari kita mulai Penjadwal. Untuk melakukan ini, Anda dapat:

• Klik kanan pada tombol Start Dan pilihlah Manajemen komputer (Manajemen Komputer)
• Pilih di Panel Kontrol: Administrasi – Penjadwal Tugas (Panel Kontrol — Alat Administratif — Penjadwal Tugas)
• Pilih dari menu utama Mulai – Aksesori – Alat Sistem – Penjadwal Tugas
• Tekan pintasan keyboard menang+R, Masuk taskschd.msc dan tekan Enter

Jendela berikut akan muncul di layar (Saya memiliki versi bahasa Inggris, tetapi Anda juga dapat memiliki versinya):

Buat tugas

Untuk membuat tugas baru menggunakan panduan langkah demi langkah sederhana, klik tautan Buat tugas sederhana (Buat Tugas Dasar) di panel kanan.

Pada langkah pertama wizard, masukkan nama dan deskripsi tugas yang akan dibuat:

Klik pada tombol Selanjutnya (Lanjut) dan pada langkah selanjutnya kami memilih pemicu – frekuensi peluncuran atau peristiwa yang akan meluncurkan tugas kami (misalnya, menyalakan komputer):

Jika Anda memilih Setiap hari (Harian), maka pada langkah berikutnya Anda harus memilih waktu tertentu, tanggal mulai dari urutan dan langkah (setiap hari ke-2, hari ke-5, dll.):

Langkah selanjutnya adalah memilih tindakan – Jalankan programnya (Mulai program):

Dan akhirnya, hal yang paling menarik adalah apa yang sebenarnya perlu dibuka:

Dalam majalah Program atau skrip (Program/skrip) Anda harus memasukkan path ke Microsoft Excel sebagai program, yaitu langsung ke Excel yang dapat dieksekusi. Pada komputer yang berbeda dengan versi Windows dan Office yang berbeda, file ini mungkin berada di folder yang berbeda, jadi berikut adalah beberapa cara untuk mengetahui lokasinya:

• Klik kanan pada ikon (pintasan) untuk meluncurkan Excel di desktop atau di bilah tugas dan pilih perintah bahan (Properti), lalu di jendela yang terbuka, salin jalur dari baris target:

                       

• Buka buku kerja Excel apa pun, lalu buka Task Manager (Pengelola tugas) mendorong Ctrl+lain+Dari dan dengan mengklik kanan pada garis Microsoft Excel, pilih perintah bahan (Properti). Di jendela yang terbuka, Anda dapat menyalin jalur, jangan lupa untuk menambahkan garis miring terbalik dan EXCEL.EXE di akhir:

               

• Buka Excel, buka editor Visual Basic dengan pintasan keyboard lain+F11, buka panel Segera kombinasi dari Ctrl+G, masukkan perintah ke dalamnya:

  ? Aplikasi.Path

  … dan klik Enter

  

  Salin jalur yang dihasilkan, jangan lupa untuk menambahkan garis miring terbalik dan EXCEL.EXE di akhir.

Dalam majalah Tambahkan argumen (opsional) (Tambahkan argumen (opsional)) Anda perlu memasukkan path lengkap ke buku dengan makro yang ingin kita buka.

Setelah semuanya masuk, lalu klik Selanjutnya lalu Finish (Selesai). Tugas harus ditambahkan ke daftar umum:

Lebih mudah untuk mengelola tugas yang dibuat menggunakan tombol di sebelah kanan. Di sini Anda dapat menguji tugas dengan menjalankannya segera (Lari)tanpa menunggu waktu yang ditentukan. Anda dapat menonaktifkan tugas untuk sementara (Nonaktifkan)sehingga berhenti berjalan untuk jangka waktu tertentu, seperti liburan Anda. Nah, Anda selalu dapat mengubah parameter (tanggal, waktu, nama file) melalui tombol bahan (Properti).

Tambahkan makro untuk membuka file

Sekarang tinggal menggantung di buku kami peluncuran makro yang kami butuhkan pada acara buka file. Untuk melakukan ini, buka buku dan buka editor Visual Basic menggunakan pintasan keyboard lain+F11 atau tombol Visual Basic tab pembangun (Pengembang). Di jendela yang terbuka di sudut kiri atas, Anda perlu menemukan file kami di pohon dan klik dua kali untuk membuka modul Buku ini (Buku Kerja Ini).

Jika Anda tidak melihat jendela ini di editor Visual Basic, maka Anda dapat membukanya melalui menu Lihat — Penjelajah Proyek.

Di jendela modul yang terbuka, tambahkan pengendali acara buka buku dengan memilihnya dari daftar drop-down di bagian atas Buku catatan и Openmasing-masing:

Template prosedur akan muncul di layar. Buku Kerja_Buka, di mana di antara garis Sub Pribadi и End Sub dan Anda perlu memasukkan perintah VBA yang harus dijalankan secara otomatis saat buku kerja Excel ini dibuka, saat Penjadwal membukanya sesuai jadwal. Berikut adalah beberapa opsi yang berguna untuk overclocking:

• Buku Kerja Ini. Segarkan Semua – Me-refresh semua kueri data eksternal, kueri Power Query, dan PivotTable. Opsi paling serbaguna. Jangan lupa untuk mengizinkan koneksi ke data eksternal secara default dan memperbarui tautan melalui File – Opsi – Pusat Kepercayaan – Opsi Pusat Kepercayaan – Konten Eksternal, jika tidak, ketika Anda membuka buku, peringatan standar akan muncul dan Excel, tanpa memperbarui apa pun, akan menunggu restu Anda dalam bentuk mengklik tombol Aktifkan konten (Aktifkan konten):

  

• ActiveWorkbook.Connections(“Connection_Name”).Segarkan — memperbarui data pada koneksi Connection_Name.
• Lembar("Lembar5"“).PivotTable(“PivotTable1«).PivotCache.Refresh . – memperbarui tabel pivot tunggal bernama Tabel Pivot1 di atas lembaran Sheet5.
• Aplikasi. Hitung – perhitungan ulang semua buku kerja Excel yang terbuka.
• Aplikasi.CalculateFullRebuild – penghitungan ulang paksa semua rumus dan membangun kembali semua dependensi antar sel di semua buku kerja yang terbuka (setara dengan memasukkan kembali semua rumus).
• Lembar Kerja("Laporan").PrintOut – lembar cetak Foto.
• Hubungi MyMacro – jalankan makro bernama Makro Saya.
• Buku Kerja ini.Simpan - simpan buku saat ini
  
• Buku Kerja Ini.Simpan Sebagai “D:ArchiveReport” & Ganti(Sekarang, “:”, “-“) & “.xlsx” – simpan buku ke folder D: Arsip atas nama Foto dengan tanggal dan waktu ditambahkan ke nama.

Jika Anda ingin makro dijalankan hanya saat file dibuka oleh Penjadwal pada pukul 5:00 pagi, dan tidak setiap kali pengguna membuka buku kerja selama hari kerja, maka masuk akal untuk menambahkan pemeriksaan waktu, misalnya:

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

Itu saja. Jangan lupa untuk menyimpan buku kerja Anda dalam format yang mendukung makro (xlsm atau xlsb) dan Anda dapat menutup Excel dengan aman dan pulang, meninggalkan komputer Anda dalam keadaan hidup. Pada saat tertentu (bahkan jika PC terkunci), Penjadwal akan meluncurkan Excel dan membuka file yang ditentukan di dalamnya, dan makro kami akan melakukan tindakan yang diprogram. Dan Anda akan menikmati di tempat tidur sementara laporan berat Anda secara otomatis dihitung ulang – cantik!

• Apa itu makro, bagaimana menggunakannya, di mana menyisipkan kode Visual Basic di Excel
• Cara membuat add-in makro Anda sendiri untuk Excel
• Cara menggunakan Buku Kerja Makro Pribadi sebagai pustaka untuk makro Anda di Excel