我如何一一打开所有excel文件并运行宏 [英] how do i open ALL the excel files one by one and run a macro

问题描述

我需要在 VBA 中编写一个宏，该宏将一一打开给定目录中的每个文件并在其上运行宏.

I need to write a macro in VBA that will open every file in a given directory one by one and run a macro on them.

到目前为止我有类似的东西

so far i have something like

for i = 1 to number_of_files
    open Dir("C:yourPath*.*", vbNormal)
    call some_macro
    close file
next i

推荐答案

通过调用带有适当过滤器的 Dir() 函数，例如 "c:folder*.xls"，你开始枚举，得到第一个文件名.
之后，不带任何参数重复调用Dir()函数，会得到所有*.xls文件名，每次调用一个.

By calling the Dir() function with an appropriate filter, such as "c:folder*.xls", you start enumeration and get the first file name.
After that, repeatedly calling the Dir() function without any parameters, you will get all *.xls file names, one for each call.

您可以通过调用 Workbooks.Open(full_path) 打开工作簿.这为您提供了一个 Workbook 对象，您可以针对该对象运行宏.

You open a workbook by calling Workbooks.Open(full_path). This gives you a Workbook object, against which you can run a macro.

此Workbook 对象的.Close() 方法关闭工作簿.您可以使用 .Close(SaveChanges:=True) 保存更改，.Close(SaveChanges:=False) 放弃更改，或省略参数让用户决定.

The .Close() method of this Workbook object closes the workbook. You can use .Close(SaveChanges:=True) to save changes, .Close(SaveChanges:=False) to discard changes, or omit the parameter to have the user decide.

这篇关于我如何一一打开所有excel文件并运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！