在特定时间运行 VBA excel 宏 [英] Running a VBA excel macro at a certain time

问题描述

试图找出在特定时间启动 Excel 宏的最佳方式.我找到了一些建议，但我对这个想法仍有很多疑问，希望你们能提供一些澄清.

Trying to figure out the best way to kick off an excel macro at a certain time. I found a few suggestions, but I still have plenty of questions on the idea and was hoping you guys could provide some clarification.

我想要做什么:我有连接到我的数据库的实时 excel 工作簿.所以在每个月的最后一天晚上 11:30 我想进入几个 excel 工作簿(我为我们销售的每个供应商都有一个)，启用安全性和编辑并刷新连接，以便工作表中的所有信息都是最新.最后，我想将该文件的副本保存在当前文件夹内的子文件夹中.

What I am trying to do: I have live excel workbooks that are connected to my database. So at 11:30PM on the last day of every month I want to go into several excel workbooks (I have one for every vendor that we sell), enable the security and editing and refresh the connection so all of the information in the sheets is up to date. Lastly I would like to then save a copy of that file in a sub folder inside the current folder.

准时运行方法:

这是我发现的按时运行方法在 ozgrid 上.我的问题是，我假设当时需要运行 excel 才能执行该方法?如果是这样，打开 excel 并刷新要保存的工作簿的一种方法是什么?

Here is this run on time method that I found on ozgrid. My question is, I am assuming that excel would need to be running at the time in order to execute the method? If so what would be one way of opening up excel and refreshing a workbook to be saved?

在 Windows 任务计划程序上运行 excel Windows 任务计划程序

本质上，这种方法是使用 Excel 控制器使用 VBS 运行 excel ......似乎比按时运行方法更有希望，但我对 excel 控制器或 VBS 的工作原理不是很熟悉，所以任何帮助或想法将不胜感激.

Essentially this method is using an Excel Controller to run excel using VBS...Seems more promising than the run on time method but I am not very familiar with how an excel controller works or VBS, so any help or ideas on this one would be greatly appreciated.

非常感谢任何输入.如果我的想法在左场有出路，你们能帮我指出正确的方向吗?

Any input is greatly appreciated. If my ideas are way out in left field could you guys help point me in the right direction?

提前感谢所有帮助！

推荐答案

解决问题. 使用普通 Excel 查询工具创建一个工作簿，并让它在工作簿时加载查询的结果集已打开(您没有指定版本，但请参阅 为 Excel 2013 刷新外部数据连接).

Turn the problem around. Create a workbook with normal Excel query tools and have it load the result set of the query when the workbook is opened (You don't specify a version, but see Refresh an external data connection for Excel 2013).

然后，在包含您需要的结果的数据库中创建一个视图，或者将视图的结果具体化到一个临时表中，然后由工作簿读取.

Then, either create a view in the database that contains the results you require, or to materialise the result of a view into a staging table that is then read by the workbook.

然后用户可以在需要数据时打开工作簿，当前结果将可用.然后，查询的参数化版本可以访问旧时期的历史记录表.

Users can then open the workbook whenever they require data, and the current results will be available. A parametrised version of the query could then access a history table to older periods.

通过这种方式，所有数据仅在需要时才准备好，并且始终可用，而没有调度复杂性.

This way all data is only prepared when required, and is always available without the scheduling complexity.

这篇关于在特定时间运行 VBA excel 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！