工作中经常会遇到需要每天更新的excel,虽然power query连接数据源后刷新一下就可以,但是还要从每天重复的劳动中解放出来,类似的工具有很多,比如自动化工具uipath、Python、VBA等,本文是用Windows批处理BAT+VBS配合Windows任务计划程序来实现,不用任何第三方软件,而且也比较稳定。

1.准备需要更新的excel文件

建议使用最新版的office365,以支持更多的数据源和功能

示例数据自动更新天气信息(excel抓取网站数据)
点击下载excel文件
注意查询属性允许后台刷新必须要去掉勾选,否则会出现没有刷新完就保存,全部刷新要勾选
设置方法:点击excel数据选项卡-查询和连接,屏幕右侧就会出现连接的查询,点击鼠标右键接口设置需要的查询
52140-rfnb7go12cr.png
94974-pk1scl9rg4h.png

2.新建vbs运行文件

打开记事本,将以下内容粘贴进去,保存为tq.vbs文件就可以了

Set objExcel = CreateObject("Excel.Application")
On Error Resume Next  '错误继续运行
objExcel.Visible = false  '隐藏excel窗口
Set objWorkbook = objExcel.Workbooks.Open ("D:\vbs\天气.xlsx") '填写excel文件的真实路径
objWorkbook.RefreshAll  '更新
objExcel.DisplayAlerts = False  '关闭提示
objWorkbook.Save '保存工作表
objWorkbook.Close False '关闭工作表

objExcel.Quit  ' 退出 
Set objWorkbook = Nothing
Set objExcel = Nothing
'关闭excel进程,避免长期占用
Set ObjShell=CreateObject("Wscript.Shell")
SttCommand=("cmd.exe /C  Taskkill  /f /im Excel.exe")
ObjShell.Run SttCommand, 0, False

3.新建bat批处理文件

使用bat可以调用多个vbs,方便执行,Windows server系统必须要设置bat才能在任务计划运行

打开记事本,将以下内容粘贴进去,保存为.bat文件就可以了,确保与vbs文件在一个文件夹

cscript tq.vbs

注意tq.vbs是vbs的文件名,如果有多个vbs直接复制以上内容回车粘贴即可

4.设置Windows任务计划程序

在开始菜单搜索'任务计划程序',点击创建任务,注意填写名称方便辨认,例如'刷新天气excel表'
83658-xdainkzjad.png
设置触发器需要运行的时间,每天运行选择每天即可,其他同理。
然后添加操作,点击启动程序,填写bat文件路径就可以了,注意填写起始于的文件夹路径
72594-yd2hsupouo.png
大功告成,到时间excel就会自动刷新保存了

文章目录