按時運行宏

實踐中非常常見的情況：您需要在給定時間或以特定頻率運行一個或多個宏。 例如，您有一個半小時​​更新一次的大而繁重的報告，您希望在早上上班前半小時運行更新。 或者你有一個宏，它應該以指定的頻率自動向員工發送電子郵件。 或者，在使用數據透視表時，您希望它每 10 秒動態更新一次，依此類推。

讓我們看看 Excel 和 Windows 有什麼能力來實現這一點。

以給定頻率運行宏

最簡單的方法是使用內置的 VBA 方法 應用程序.OnTime在指定時間運行指定宏的 。 讓我們通過一個實際的例子來理解這一點。

使用選項卡上的同名按鈕打開 Visual Basic 編輯器 開發人員 （開發商） 或鍵盤快捷鍵 其他+F11, 通過菜單插入一個新模塊 插入 - 模塊 並在那裡複製以下代碼：

Dim TimeToRun '全局變量存儲下一次運行時間 '這是主宏 Sub MyMacro() Application.Calculate '重新計算書 Range("A1").Interior.ColorIndex = Int(Rnd() * 56) '填充單元格 A1 具有隨機顏色 :) Call 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

讓我們弄清楚這裡是什麼。

首先，我們需要一個變量來存儲下一次運行宏的時間——我稱之為 運行時間. 請注意，這個變量的內容必須對我們所有後續的宏都可用，所以我們需要使它 整體，即在第一個模塊之前的最開始聲明 子.

接下來是我們的主要宏 我的宏, 這將執行主要任務 - 使用方法重新計算書籍 應用程序.計算. 為了更清楚，我在單元格 A1 的表格中添加了公式 =TDATE()，它顯示了日期和時間——當重新計算時，它的內容將在我們眼前更新（只需打開單元格中的秒顯示格式）。 為了更有趣，我還在宏中添加了用隨機選擇的顏色填充單元格 A1 的命令（顏色代碼是 0..56 範圍內的整數，由函數生成 RND 並向上取整為整數函數 詮釋).

宏 下一個運行 添加到先前的值 運行時間 再等 3 秒，然後安排下一次運行主宏 我的宏 對於這個新的時間。 當然，在實踐中，您可以通過設置函數參數來使用您需要的任何其他時間間隔 時間價值 格式為 hh:mm:ss。

最後，為了方便起見，添加了更多的序列啟動宏。 首頁 及其完成 完. 最後一個使用第四個方法參數來打破序列。 按時 等於 假.

如果您運行宏，則總計 首頁 ，那麼整個旋轉木馬就會旋轉，我們會在工作表上看到如下圖片：

您可以通過分別運行宏來停止序列 完. 為方便起見，您可以使用以下命令為兩個宏分配鍵盤快捷鍵 宏 - 選項 選項卡 開發人員 （開發者——宏——選項）.

按計劃運行宏

當然，只有在您運行 Microsoft Excel 並且我們的文件在其中打開時，上述所有操作才可能實現。 現在讓我們來看一個更複雜的案例：您需要按照給定的時間表運行 Excel，例如每天 5:00，在其中打開一個大而復雜的報表並更新其中的所有連接和查詢，以便它將在我們上班時準備好🙂

在這種情況下，最好使用 Windows 調度程序 – 專門內置於任何版本的 Windows 中的程序，可以按計劃執行指定的操作。 其實你已經在不知不覺中使用它了，因為你的PC會定期檢查更新、下載新的殺毒數據庫、同步雲文件夾等等，這些都是Scheduler的工作。 因此，我們的任務是向現有任務添加另一個將啟動 Excel 並在其中打開指定文件的任務。 我們將把我們的宏掛在事件上 工作簿_打開 這個文件——問題就解決了。

我想立即警告您，使用調度程序可能需要高級用戶權限，因此如果您在辦公室的工作計算機上找不到下面描述的命令和功能，請聯繫您的 IT 專家尋求幫助。

啟動調度程序

所以讓我們啟動調度程序。 為此，您可以：

  • 右鍵單擊按鈕 開始 並選擇 計算機管理 （計算機管理）
  • 在控制面板中選擇： 管理 - 任務計劃程序 （控制面板——管理工具——任務計劃程序)
  • 從主菜單中選擇 開始——附件——系統工具——任務計劃程序
  • 按鍵盤快捷鍵 Win+R，進入 taskschd.msc 並按下 Enter

螢幕上應出現以下視窗（我有英文版本，但您也可以有版本）：

按時運行宏

創建一個任務

要使用簡單的分步嚮導創建新任務，請單擊鏈接 創建一個簡單的任務 （創建基本任務） 在右側面板中。

在嚮導的第一步，輸入要創建的任務的名稱和描述：

按時運行宏

單擊按鈕 下一頁 （下一個） 在下一步中，我們選擇一個觸發器——啟動頻率或將啟動我們的任務的事件（例如，打開計算機）：

按時運行宏

如果您選擇 每日 （日常），然後在下一步中，您將需要選擇特定時間、序列和步驟的開始日期（每 2 天、第 5 天等）：

按時運行宏

下一步是選擇一個動作—— 運行該程序 （啟動程序）:

按時運行宏

最後，最有趣的是究竟需要打開什麼：

按時運行宏

在 程序或腳本 （程序/腳本） 您需要以程序的形式輸入 Microsoft Excel 的路徑，即直接輸入 Excel 可執行文件。 在使用不同版本的 Windows 和 Office 的不同計算機上，此文件可能位於不同的文件夾中，因此您可以通過以下幾種方法找到它的位置：

  • 右鍵單擊圖標（快捷方式）以在桌面或任務欄中啟動 Excel，然後選擇命令 材料 （屬性），然後在打開的窗口中，從該行複制路徑 目標:

    按時運行宏                      按時運行宏

  • 打開任何 Excel 工作簿，然後打開 Task Manager （任務管理器） 推動 按Ctrl+其他+起 並通過右鍵單擊該行 Microsoft Excel中, 選擇一個命令 材料 （屬性）. 在打開的窗口中，您可以復制路徑， 不要忘記在最後添加反斜杠和 EXCEL.EXE:

    按時運行宏              按時運行宏

  • 打開 Excel，使用鍵盤快捷鍵打開 Visual Basic 編輯器 其他+F11, 打開面板 即時 的組合 按Ctrl+G，在其中輸入命令：

    ? 應用程序路徑

    …然後點擊 Enter

    按時運行宏

    複製生成的路徑， 不要忘記在最後添加反斜杠和 EXCEL.EXE.

在 添加參數（可選） （添加參數（可選）） 您需要使用我們要打開的宏插入書籍的完整路徑。

全部輸入後，點擊 下一頁 進而 完 （結束）. 該任務應添加到總列表中：

按時運行宏

使用右側的按鈕可以方便地管理創建的任務。 在這裡，您可以通過立即運行來測試任務 （跑）無需等待指定時間。 您可以暫時停用任務 （禁用）使其停止運行一段時間，例如您的假期。 好吧，您可以隨時通過按鈕更改參數（日期、時間、文件名） 材料 （屬性）.

添加宏以打開文件

現在，我們需要在文件打開事件中啟動我們需要的宏。 為此，打開書本並使用鍵盤快捷鍵轉到 Visual Basic 編輯器 其他+F11 或按鈕 Visual Basic中 選項卡 開發人員 （開發商）. 在左上角打開的窗口中，需要在樹上找到我們的文件，雙擊打開模塊 這本書 （本工作簿）.

如果在 Visual Basic 編輯器中看不到此窗口，則可以通過菜單打開它 查看 — 項目資源管理器.

在打開的模塊窗口中，通過從頂部的下拉列表中選擇來添加圖書打開事件處理程序 工作簿 и 已提交， 分別：

按時運行宏

程序模板應出現在屏幕上。 工作簿_打開, 線之間的位置 私人小組 и END SUB 並且您需要插入那些應該在打開此 Excel 工作簿時自動執行的 VBA 命令，當調度程序根據計劃打開它時。 以下是一些有用的超頻選項：

  • ThisWorkbook.RefreshAll – 刷新所有外部數據查詢、Power Query 查詢和數據透視表。 最通用的選擇。 只是不要忘記默認允許連接到外部數據並通過更新鏈接 文件——選項——信任中心——信任中心選項——外部內容，否則，當你打開書本時，會出現一個標準的警告，而Excel不會更新任何東西，會以點擊按鈕的形式等待你的祝福 啟用內容 （啟用內容）:

    按時運行宏

  • ActiveWorkbook.Connections(“Connection_Name”).刷新 — 更新 Connection_Name 連接上的數據。
  • 表（“表 5").PivotTables("數據透視表1«).PivotCache.Refresh – 更新一個名為 數據透視表1 在單子上 Sheet5.
  • 應用程序.計算 – 重新計算所有打開的 Excel 工作簿。
  • 應用程序.CalculateFullRebuild - 強制重新計算所有公式並重建所有打開的工作簿中單元格之間的所有依賴關係（相當於重新輸入所有公式）。
  • 工作表（“報告”）.PrintOut – 打印表 照片.
  • 調用 MyMacro – 運行一個名為的宏 我的宏.
  • ThisWorkbook.保存 - 保存當前書籍
  • ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-”) & “.xlsx” - 將書保存到文件夾 D:存檔 以...之名 照片 名稱後附有日期和時間。

如果您希望僅在調度程序在凌晨 5:00 打開文件時執行宏，而不是在工作日期間每次用戶打開工作簿時執行，那麼添加時間檢查是有意義的，例如：

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

就這樣。 不要忘記以啟用宏的格式（xlsm 或 xlsb）保存您的工作簿，您可以安全地關閉 Excel 並回家，讓您的計算機保持打開狀態。 在給定的時刻（即使 PC 被鎖定），調度程序將啟動 Excel 並打開其中的指定文件，我們的宏將執行編程的操作。 當您沉重的報告自動重新計算時，您將在床上盡情享受——美麗！ 🙂

  • 什麼是宏，如何使用它們，在 Excel 中插入 Visual Basic 代碼的位置
  • 如何為 Excel 創建自己的宏加載項
  • 如何將個人宏工作簿用作 Excel 中的宏庫

發表評論