vba 如何使用VBScript有效刷新多个Excel电子表格的外部数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16932104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use VBScript to effectively refresh the external data of several Excel spreadsheets?
提问by Sam
I have a macro in one XLSM workbook's module that refreshes all the external data, then saves and closes that workbook.
我在一个 XLSM 工作簿的模块中有一个宏，用于刷新所有外部数据，然后保存并关闭该工作簿。
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Quit
I use a VBScript file to run that macro as part of a scheduled task
我使用 VBScript 文件来运行该宏作为计划任务的一部分
objExcel.Workbooks.Open(fname)
objExcel.Visible = True
On error resume next
objExcel.Run "RefreshAllData"
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 文件的必要修改，我想尽量减少对宏本身的更改。文件名将包含在 VBScript 文件中）TIA。
回答by Ansgar Wiechers
I'd recommend against re-using a trivial macro like that. Instead incorporate the refresh functionality in the VBScript:
我建议不要重复使用这样的微不足道的宏。而是在 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
回答by MrPandav
I suppose you can do something like this.
我想你可以做这样的事情。
- Find all the excel file in the given folder
- Select Each Excel file and activate
- call refreshdata function
- 查找给定文件夹中的所有excel文件
- 选择每个 Excel 文件并激活
- 调用 refreshdata 函数
code snippet to support this
支持这一点的代码片段
Sub RefreshAllExcelInFolder()
Dim fso
Dim ObjFolder
Dim ObjFiles
Dim ObjFile
Dim objExcel
'Creating File System Object
Set fso = CreateObject("Scripting.FileSystemObject")
'Getting the Folder Object
Set ObjFolder = fso.GetFolder("<<C:folder path>>")
'Getting the list of Files
Set ObjFiles = ObjFolder.Files
'On Error Resume Next
For Each ObjFile In ObjFiles
If LCase(Right(ObjFile.Name, 5)) = ".xlsx" Or LCase(Right(ObjFile.Name, 4)) = ".xls" Then
Workbooks.Open(ObjFile).Activate
RefreshAllData
End If
Next
End Sub
Sub RefreshAllData()
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox ("Going back In")
End Sub
Hope this will help
希望这会有所帮助
回答by JosieP
If you don't change the macro at all you will have to keep reloading excel, then open the macro workbook followed by the target workbook and then run the macro. If you can remove:
如果您根本不更改宏，则必须继续重新加载 excel，然后打开宏工作簿，然后打开目标工作簿，然后运行宏。如果可以删除：
Application.quit
then you can at least keep excel open and just keep opening target workbooks before running the macro each time. Seems to me it'd be simpler to put the macro code into the vbs file though
那么您至少可以在每次运行宏之前保持 excel 打开并保持打开目标工作簿。在我看来，将宏代码放入 vbs 文件会更简单
