在XLSM中自动运行数据连接 [英] Automatically run data connections in XLSM

问题描述

我有一个XLSM文件，其中包含2个Web查询连接.当我转到数据"选项卡中的连接"时，将看到我拥有的两个连接.

I have an XLSM file which contains 2 Web Query connection. When I go to "Connections" in the "Data" tab I am presented with the two connections I have.

对于其中的每一个，我都可以编辑一些属性，即每X分钟更新一次".我将其设置为1分钟，还勾选了激活后台更新".

For each of these I can edit some properties, one which says "Update every X minutes". I've set this to 1 minute and also ticked "Activate background update".

但是，由于网络查询连接仍然无法运行，因此该方法将无法正常工作.

This, however, won't work as the web query connections aren't run anyway.

最终，我需要每小时自动运行一次这些连接.优选地，没有任何用户交互并且没有打开文档.

Ultimately what I need is to run these connections automatically once every hour. Preferably without any user interaction and without the document being open.

这可能吗?

推荐答案

在打开文件之前无法刷新连接.您可以在打开文件时运行查询，或创建一个VBA例程来打开文件，然后使用RefreshAll并每小时保存一次原始文件.
您也可以使用VBS.
OP的最终解决方案:

You can't refresh a connection without the file being open. You can run queries on opening the file or create a VBA routine that opens the file, then uses the RefreshAll and saves over the original file every hour.
You can use VBS too.
The final solution by OP:

我最终制作了一些VBS脚本来处理打开，刷新， 关闭.然后使用批处理脚本包装程序来处理运行VBS脚本 和记录.最后，Windows Task Scheduler运行批处理脚本

I ended up making a little VBS script to handle the open, refresh, close. Then a batch script wrapper to handle running the VBS script and logging. Finally Windows Task Scheduler to run the batch script periodically.

这篇关于在XLSM中自动运行数据连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！