按时运行宏

实践中非常常见的情况：您需要在给定时间或以特定频率运行一个或多个宏。 例如，您有一个半小时更新一次的大而繁重的报告，您希望在早上上班前半小时运行更新。 或者你有一个宏，它应该以指定的频率自动向员工发送电子邮件。 或者，在使用数据透视表时，您希望它每 10 秒动态更新一次，依此类推。

让我们看看 Excel 和 Windows 有什么能力来实现这一点。

以给定频率运行宏

最简单的方法是使用内置的 VBA 方法 申请时间在指定时间运行指定宏的 。 让我们通过一个实际的例子来理解这一点。

使用选项卡上的同名按钮打开 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 专家寻求帮助。

启动调度程序

所以让我们启动调度程序。 为此，您可以：

  • 右键单击按钮 Start 开始 并选择 计算机管理 （计算机管理）
  • 在控制面板中选择： 管理 - 任务计划程序 （控制面板——管理工具——任务计划程序)
  • 从主菜单中选择 开始——附件——系统工具——任务计划程序
  • 按键盘快捷键 Win+R，输入 taskschd.msc 并按下 输入

屏幕上应出现以下窗口（我有英文版本，但您也可以有版本）：

按时运行宏

创建一个任务

要使用简单的分步向导创建新任务，请单击链接 创建一个简单的任务 （创建基本任务） 在右侧面板中。

在向导的第一步，输入要创建的任务的名称和描述：

按时运行宏

单击按钮 下一页 （下一个） 在下一步中，我们选择一个触发器——启动频率或将启动我们的任务的事件（例如，打开计算机）：

按时运行宏

如果您选择 日间 （日常），然后在下一步中，您将需要选择特定时间、序列和步骤的开始日期（每 2 天、第 5 天等）：

按时运行宏

下一步是选择一个动作—— 运行程序 （启动程序）:

按时运行宏

最后，最有趣的是究竟需要打开什么：

按时运行宏

在 程序或脚本 （程序/脚本） 您需要以程序的形式输入 Microsoft Excel 的路径，即直接输入 Excel 可执行文件。 在具有不同版本的 Windows 和 Office 的不同计算机上，此文件可能位于不同的文件夹中，因此您可以通过以下几种方法找到它的位置：

  • 右键单击图标（快捷方式）以在桌面或任务栏中启动 Excel，然后选择命令 材料 （属性），然后在打开的窗口中，从该行复制路径 目标:

    按时运行宏                      按时运行宏

  • 打开任何 Excel 工作簿，然后打开 Task Manager （任务管理器） 推动 按Ctrl+其他+从 并通过右键单击该行 微软的Excel, 选择一个命令 材料 （属性）. 在打开的窗口中，您可以复制路径， 不要忘记在最后添加反斜杠和 EXCEL.EXE:

    按时运行宏              按时运行宏

  • 打开 Excel，使用键盘快捷键打开 Visual Basic 编辑器 其他+F11, 打开面板 即时 的组合 按Ctrl+G，在其中输入命令：

    ? 应用程序路径

    …然后点击 输入

    按时运行宏

    复制生成的路径， 不要忘记在最后添加反斜杠和 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 中的宏库

发表评论