Chạy macro đúng giờ

Một trường hợp rất phổ biến trong thực tế: bạn cần chạy một hoặc nhiều macro của mình tại một thời điểm nhất định hoặc ở một tần suất nhất định. Ví dụ: bạn có một báo cáo lớn và nhiều cập nhật nửa giờ và bạn muốn chạy bản cập nhật nửa giờ trước khi bạn đến nơi làm việc vào buổi sáng. Hoặc bạn có một macro sẽ tự động gửi email cho nhân viên với tần suất được chỉ định. Hoặc, khi làm việc với PivotTable, bạn muốn nó cập nhật nhanh sau mỗi 10 giây, v.v.

Hãy xem những gì Excel và Windows có khả năng thực hiện điều này.

Chạy macro ở một tần suất nhất định

Cách dễ nhất để làm điều này là sử dụng phương pháp VBA được tích hợp sẵn Ứng dụng.OnTimeAn chạy macro được chỉ định tại thời điểm được chỉ định. Hãy hiểu điều này bằng một ví dụ thực tế.

Mở trình soạn thảo Visual Basic bằng nút cùng tên trên tab nhà phát triển (Nhà phát triển) hoặc phím tắt Khác+F11, chèn một mô-đun mới thông qua menu Chèn - Mô-đun và sao chép mã sau vào đó:

Dim TimeToRun 'biến toàn cục nơi lưu trữ thời gian chạy tiếp theo', đây là macro chính Sub MyMacro () Application.Calculate 'tính toán lại phạm vi sách ("A1"). Interior.ColorIndex = Int (Rnd () * 56)' điền ô A1 với màu ngẫu nhiên :) Gọi NextRun 'chạy macro NextRun để đặt thời gian chạy tiếp theo End Sub' macro này đặt thời gian cho lần chạy tiếp theo của macro chính Sub NextRun () TimeToRun = Now + TimeValue ("00: 00:03 ") 'thêm 3 giây vào thời gian hiện tại Application.OnTime TimeToRun," MyMacro "' lập lịch cho macro chạy tiếp theo End Sub 'để bắt đầu chuỗi lặp lại Sub Start () Gọi macro NextRun End Sub' để dừng chuỗi lặp lại Sub Finish () Application.OnTime TimeToRun, "MyMacro",, False End Sub  

Hãy tìm hiểu những gì ở đây.

Đầu tiên, chúng ta cần một biến sẽ lưu trữ thời gian chạy macro tiếp theo của chúng ta - tôi đã gọi nó là Thời gian để chạy. Xin lưu ý rằng nội dung của biến này phải có sẵn cho tất cả các macro tiếp theo của chúng tôi, vì vậy chúng tôi cần làm cho nó toàn cầu, tức là khai báo ở phần đầu của mô-đun trước phần đầu tiên Sub.

Tiếp theo là macro chính của chúng tôi MyMacro, sẽ thực hiện nhiệm vụ chính - tính toán lại sách bằng phương pháp Ứng dụng. Tính toán. Để làm rõ hơn, tôi đã thêm công thức = TDATE () vào trang tính trong ô A1, hiển thị ngày và giờ - khi được tính toán lại, nội dung của nó sẽ được cập nhật ngay trước mắt chúng ta (chỉ cần bật hiển thị giây trong ô định dạng). Để thêm phần thú vị, tôi cũng đã thêm vào macro lệnh điền vào ô A1 bằng màu được chọn ngẫu nhiên (mã màu là một số nguyên trong phạm vi 0..56, được tạo bởi hàm Rnd và làm tròn đến một hàm số nguyên Int).

Macro Tiếp theoChạy thêm vào giá trị trước đó Thời gian để chạy 3 giây nữa và sau đó lên lịch cho lần chạy tiếp theo của macro chính MyMacro cho thời gian mới này. Tất nhiên, trong thực tế, bạn có thể sử dụng bất kỳ khoảng thời gian nào khác mà bạn cần bằng cách đặt các đối số của hàm Giá trị thời gian ở định dạng hh: mm: ss.

Và cuối cùng, để thuận tiện, nhiều macro khởi chạy trình tự hơn đã được thêm vào. Trang Chủ và sự hoàn thành của nó Kết thúc. Cái cuối cùng sử dụng đối số phương thức thứ tư để phá vỡ chuỗi. Về thời gian như nhau Sai.

Tổng số nếu bạn chạy macro Trang Chủ, sau đó toàn bộ băng chuyền này sẽ quay và chúng ta sẽ thấy hình ảnh sau trên trang tính:

Bạn có thể dừng trình tự bằng cách chạy macro tương ứng Kết thúc. Để thuận tiện, bạn có thể gán phím tắt cho cả hai macro bằng lệnh Macro - Tùy chọn chuyển hướng nhà phát triển (Nhà phát triển - Macro - Tùy chọn).

Chạy macro theo lịch trình

Tất nhiên, mọi thứ được mô tả ở trên chỉ có thể thực hiện được nếu bạn có Microsoft Excel đang chạy và tệp của chúng tôi đang mở trong đó. Bây giờ chúng ta hãy xem xét một trường hợp phức tạp hơn: bạn cần chạy Excel theo một lịch trình nhất định, ví dụ: hàng ngày vào lúc 5:00, hãy mở một báo cáo lớn và phức tạp trong đó và cập nhật tất cả các kết nối và truy vấn trong đó để nó sẵn sàng trước khi chúng tôi đến nơi làm việc 🙂

Trong tình huống như vậy, tốt hơn là sử dụng Bộ lập lịch Windows - một chương trình được tích hợp đặc biệt trong bất kỳ phiên bản Windows nào có thể thực hiện các hành động cụ thể theo lịch trình. Trên thực tế, bạn đang sử dụng nó mà không hề hay biết, vì PC của bạn thường xuyên kiểm tra các bản cập nhật, tải cơ sở dữ liệu diệt virus mới, đồng bộ hóa các thư mục đám mây,… Tất cả đều là công việc của Scheduler. Vì vậy, nhiệm vụ của chúng ta là thêm vào các tác vụ hiện có một tác vụ khác sẽ khởi chạy Excel và mở tệp được chỉ định trong đó. Và chúng tôi sẽ treo macro của mình vào sự kiện Sổ làm việc_Mở tệp này - và vấn đề đã được giải quyết.

Tôi muốn cảnh báo bạn ngay rằng làm việc với Bộ lập lịch có thể yêu cầu quyền người dùng nâng cao, vì vậy nếu bạn không thể tìm thấy các lệnh và chức năng được mô tả bên dưới trên máy tính làm việc của mình trong văn phòng, hãy liên hệ với các chuyên gia CNTT của bạn để được trợ giúp.

Khởi chạy Trình lập lịch

Vì vậy, hãy khởi động Trình lập lịch. Để làm điều này, bạn có thể:

• Nhấp chuột phải vào nút Bắt đầu Và chọn Quản lý máy tính (Quản lý máy tính)
• Chọn trong Bảng điều khiển: Quản trị - Bộ lập lịch tác vụ (Bảng điều khiển - Công cụ quản trị - Bộ lập lịch tác vụ)
• Chọn từ menu chính Khởi động - Phụ kiện - Công cụ hệ thống - Bộ lập lịch tác vụ
• Nhấn phím tắt Giành chiến thắng+R, đi vào taskschd.msc và báo chí đăng ký hạng mục thi

Cửa sổ sau sẽ xuất hiện trên màn hình (Tôi có phiên bản tiếng Anh, nhưng bạn cũng có thể có phiên bản):

Tạo một nhiệm vụ

Để tạo một nhiệm vụ mới bằng trình hướng dẫn từng bước đơn giản, hãy nhấp vào liên kết Tạo một nhiệm vụ đơn giản (Tạo tác vụ cơ bản) trong bảng điều khiển bên phải.

Ở bước đầu tiên của trình hướng dẫn, hãy nhập tên và mô tả của nhiệm vụ sẽ được tạo:

Nhấn nút Sau (Kế tiếp) và trong bước tiếp theo, chúng tôi chọn một trình kích hoạt - tần suất khởi chạy hoặc một sự kiện sẽ khởi chạy tác vụ của chúng tôi (ví dụ: bật máy tính):

Nếu bạn đã chọn Hàng ngày (Hằng ngày), thì trong bước tiếp theo, bạn sẽ cần chọn thời gian cụ thể, ngày bắt đầu của trình tự và bước (mỗi ngày thứ 2, ngày thứ 5, v.v.):

Bước tiếp theo là chọn một hành động - Chạy chương trình (Bắt đầu một chương trình):

Và cuối cùng, điều thú vị nhất là những gì chính xác cần được mở:

Trong tạp chí Chương trình hoặc kịch bản (Chương trình / kịch bản) bạn cần nhập đường dẫn đến Microsoft Excel dưới dạng một chương trình, tức là trực tiếp đến tệp thực thi Excel. Trên các máy tính khác nhau có các phiên bản Windows và Office khác nhau, tệp này có thể nằm trong các thư mục khác nhau, vì vậy dưới đây là một số cách để bạn tìm ra vị trí của tệp:

• Nhấp chuột phải vào biểu tượng (phím tắt) để khởi chạy Excel trên màn hình nền hoặc trên thanh tác vụ và chọn lệnh Vật liệu (Tính chất), rồi trong cửa sổ mở ra, hãy sao chép đường dẫn từ dòng Mục tiêu:

                       

• Mở bất kỳ sổ làm việc Excel nào, sau đó mở Task Manager (Quản lý công việc) đẩy Ctrl+Khác+Từ và bằng cách nhấp chuột phải vào dòng Microsoft Excel, chọn một lệnh Vật liệu (Tính chất). Trong cửa sổ mở ra, bạn có thể sao chép đường dẫn, không quên thêm dấu gạch chéo ngược vào nó và EXCEL.EXE ở cuối:

               

• Mở Excel, mở trình soạn thảo Visual Basic bằng phím tắt Khác+F11, mở bảng điều khiển lập tức một sự kết hợp của Ctrl+G, nhập lệnh vào đó:

  ? Ứng dụng.Path

  … Và nhấp vào đăng ký hạng mục thi

  

  Sao chép đường dẫn kết quả, không quên thêm dấu gạch chéo ngược vào nó và EXCEL.EXE ở cuối.

Trong tạp chí Thêm đối số (tùy chọn) (Thêm đối số (tùy chọn)) bạn cần chèn đường dẫn đầy đủ đến sách bằng macro mà chúng tôi muốn mở.

Khi mọi thứ được nhập, sau đó nhấp vào Sau và sau đó Kết thúc (Kết thúc). Nhiệm vụ nên được thêm vào danh sách chung:

Thật thuận tiện để quản lý tác vụ đã tạo bằng cách sử dụng các nút ở bên phải. Tại đây bạn có thể kiểm tra tác vụ bằng cách chạy nó ngay lập tức (chạy)mà không cần chờ đợi trong thời gian quy định. Bạn có thể tạm thời hủy kích hoạt một công việc (Vô hiệu hóa)để nó ngừng chạy trong một khoảng thời gian, chẳng hạn như kỳ nghỉ của bạn. Vâng, bạn luôn có thể thay đổi các thông số (ngày, giờ, tên tệp) thông qua nút Vật liệu (Tính chất).

Thêm macro để mở tệp

Bây giờ nó vẫn còn để treo trong cuốn sách của chúng tôi về việc khởi chạy macro mà chúng tôi cần trong sự kiện mở tệp. Để thực hiện việc này, hãy mở sách và đi tới trình chỉnh sửa Visual Basic bằng phím tắt Khác+F11 hoặc các nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển). Trong cửa sổ mở ra ở góc trên bên trái, bạn cần tìm tệp của chúng tôi trên cây và nhấp đúp để mở mô-đun Cuốn sách này (Sách bài tập này).

Nếu bạn không thấy cửa sổ này trong trình chỉnh sửa Visual Basic, thì bạn có thể mở nó qua menu Chế độ xem - Trình khám phá dự án.

Trong cửa sổ mô-đun mở ra, hãy thêm một trình xử lý sự kiện mở sách bằng cách chọn nó từ danh sách thả xuống ở trên cùng Bảng tính и Mở, tương ứng:

Một mẫu thủ tục sẽ xuất hiện trên màn hình. Sổ làm việc_Mở, ở đâu giữa các dòng Sub riêng tư и End Sub và bạn cần chèn các lệnh VBA đó sẽ được tự động thực thi khi mở sổ làm việc Excel này, khi Bộ lập lịch mở theo lịch trình. Dưới đây là một số tùy chọn hữu ích để ép xung:

• ThisWorkbook.RefreshAll - Làm mới tất cả các truy vấn dữ liệu bên ngoài, truy vấn Power Query và PivotTable. Tùy chọn linh hoạt nhất. Chỉ cần đừng quên cho phép các kết nối với dữ liệu bên ngoài theo mặc định và cập nhật các liên kết qua Tệp - Tùy chọn - Trung tâm tin cậy - Tùy chọn Trung tâm tin cậy - Nội dung bên ngoài, ngược lại, khi bạn mở sách, một cảnh báo tiêu chuẩn sẽ xuất hiện và Excel, không cần cập nhật bất cứ điều gì, sẽ đợi bạn ban phước bằng hình thức nhấp vào nút Bật nội dung (Bật nội dung):

  

• ActiveWorkbook.Connections (“Tên_kết nối”). Làm mới - cập nhật dữ liệu trên kết nối Connection_Name.
• Trang tính (“Trang tính5“) .PivotTables (“ PivotTable1«) .PivotCache.Refresh - cập nhật một bảng tổng hợp có tên PivotTable1 trên trang tính Sheet5.
• Ứng dụng. Tính toán - tính toán lại tất cả các sổ làm việc Excel đang mở.
• Application.CalculateFullRebuild - buộc phải tính toán lại tất cả các công thức và xây dựng lại tất cả các phụ thuộc giữa các ô trong tất cả các sổ làm việc đang mở (tương đương với việc nhập lại tất cả các công thức).
• Trang tính (“Báo cáo”). PrintOut - tờ in Hình ảnh.
• Gọi MyMacro - chạy một macro có tên MyMacro.
• ThisWorkbook.Save - lưu cuốn sách hiện tại
  
• ThisWorkbooks.SaveAs “D: ArchiveReport” & Replace (Now, “:”, “-“) & “.xlsx” - lưu sách vào một thư mục D: Lưu trữ dưới tên Hình ảnh với ngày và giờ được thêm vào tên.

Nếu bạn muốn macro chỉ được thực thi khi tệp được Bộ lập lịch mở vào lúc 5 giờ sáng và không phải bất cứ khi nào người dùng mở sổ làm việc trong ngày làm việc, thì bạn nên thêm kiểm tra thời gian, ví dụ:

Nếu Định dạng (Bây giờ, "hh: mm") = "05:00" Thì ThisWorkbook.RefreshAll  

Đó là tất cả. Đừng quên lưu sổ làm việc của bạn ở định dạng hỗ trợ macro (xlsm hoặc xlsb) và bạn có thể đóng Excel một cách an toàn và về nhà, bật máy tính của bạn. Tại một thời điểm nhất định (ngay cả khi PC bị khóa), Bộ lập lịch biểu sẽ khởi chạy Excel và mở tệp được chỉ định trong đó và macro của chúng tôi sẽ thực hiện các hành động được lập trình. Và bạn sẽ nằm dài trên giường trong khi báo cáo nặng nề của bạn được tự động tính toán lại - vẻ đẹp! 🙂

• Macro là gì, cách sử dụng chúng, nơi chèn mã Visual Basic trong Excel
• Cách tạo phần bổ trợ macro của riêng bạn cho Excel
• Cách sử dụng Sổ làm việc Macro Cá nhân làm thư viện cho các macro của bạn trong Excel