如何使用VBScript有效刷新多个Excel电子表格的外部数据? [英] How can I use VBScript to effectively refresh the external data of several Excel spreadsheets?

问题描述

我在一个 XLSM 工作簿的模块中有一个宏，用于刷新所有外部数据，然后保存并关闭该工作簿.

I have a macro in one XLSM workbook's module that refreshes all the external data, then saves and closes that workbook.

ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Quit

我使用 VBScript 文件来运行该宏作为计划任务的一部分

I use a VBScript file to run that macro as part of a scheduled task

objExcel.Workbooks.Open(fname)
objExcel.Visible = True
On error resume next
objExcel.Run "RefreshAllData"

问题:如何重用现有工作簿中的现有宏来刷新多个其他工作簿的所有数据?(即.我正在寻找对 VBScript 文件的必要修改，我想尽量减少对宏本身的更改.文件名将包含在 VBScript 文件中)TIA.

Question: How can I reuse the existing macro in the existing workbook to refresh all the data of multiple other workbooks? (ie. I'm looking for the necessary modifications to the VBScript file, I want to minimise changes to the macro itself. The filenames will be contained in the VBScript file) TIA.

推荐答案

我建议不要重复使用这样的微不足道的宏.而是在 VBScript 中加入刷新功能:

I'd recommend against re-using a trivial macro like that. Instead incorporate the refresh functionality in the VBScript:

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")
xl.Visible = True

For Each f In fso.GetFolder("C:\some\folder").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsx" Then
    Set wb = xl.Workbooks.Open(f.Path)
    wb.RefreshAll
    wb.Save
    wb.Close
  End If
Next

xl.Quit

这篇关于如何使用VBScript有效刷新多个Excel电子表格的外部数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！