vba 在多个 excel 文件上运行宏并将它们保存在新目录中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24370441/
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
Running a Macro over multiple excel files and saving them in a new directory
提问by Operationpbj
I know a very similar form of this question has been asked many times before, but I'm having some problems getting it to work with my macro.
我知道这个问题的一个非常相似的形式之前已经被问过很多次了，但是我在让它与我的宏一起工作时遇到了一些问题。
Basically, what I need is to have a Macro that will run over several hundred files all located in the same folder, and then "save" the new files (without replacing the old files) in a separate folder/directory. Obviously without having to open each file individually and running the Macro over and over again.
基本上，我需要的是有一个宏可以运行数百个文件，所有文件都位于同一文件夹中，然后将新文件（不替换旧文件）“保存”在单独的文件夹/目录中。显然不必单独打开每个文件并一遍又一遍地运行宏。
I've tried a few different suggestions but they have all either resulted in drastically unnecessary changes to my fully functioning macro, or have ended in substantial errors.
我尝试了一些不同的建议，但它们要么导致对我功能齐全的宏进行了彻底不必要的更改，要么以大量错误告终。
The location of the original files is--> F:\Reports_Complete\Reports_201308_2014\
. . . The folder only contains files that the Macro needs to run on, and they are all .csv files (the final files should be .xls). . .
原始文件的位置是--> F:\Reports_Complete\Reports_201308_2014\
。. . 该文件夹仅包含运行宏所需的文件，并且它们都是 .csv 文件（最终文件应为 .xls）。. .
The location of the files after the Macro runs should be--> F:\Reports_Complete\
宏运行后文件的位置应该是--> F:\Reports_Complete\
As opposed to copying the entirety of my several hundred line Macro, here are the important lines:
与复制整个几百行宏相反，这里是重要的几行：
In the beginning of the Macro:
在宏的开头：
ActiveWorkbook.SaveAs Filename:="F:\Reports_Complete\EXT872_VTDT_" & Range("B2").Text & ".xls"
........................
……………………
At the end of the Macro:
在宏结束时：
With ActiveWorkbook
'MsgBox .FileFormat, , .FullName
Application.DisplayAlerts = False
.SaveAs .FullName, xlNormal
Application.DisplayAlerts = True
'MsgBox .FileFormat, , .FullName
End With
I would be extremely grateful for some help, and if possible without jumping over any steps or procedures ;-)
我将非常感谢您的帮助，如果可能的话，不要跳过任何步骤或程序；-)
Thanks again!!!
再次感谢！！！
EDIT
编辑
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.csv")
Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
DoWork wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
End Sub
..........
.....
Sub DoWork(wb As Workbook)
With wb
'Here is where my code is
End With
End Sub
I've tried using the above code, but it isn't working for me. Is there another way to make my macro work across an entire folder?
我试过使用上面的代码，但它对我不起作用。还有另一种方法可以让我的宏在整个文件夹中工作吗？
回答by David Zemens
This is a simple method to loop over the files in a specified folder.
这是循环指定文件夹中文件的简单方法。
This assumes that your current macro acts on the ActiveWorkbookand already does:
这假设您当前的宏作用于ActiveWorkbook并且已经这样做了：
- Formats the value in Range("B2")
- Saves the file as new file to a specified destination
- 格式化 Range("B2") 中的值
- 将文件作为新文件保存到指定的目的地
Try:
尝试：
Sub LoopFile()
'Enable reference to Microsoft Scripting Runtime if you want to use early binding
Dim fso as Object 'Scritping.FileSystemObject
Dim fldr as Object 'Scripting.Folder
Dim file as Object 'Scripting.File
Dim wb as Workbook
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("F:\Reports_Complete\Reports_201308_2014\")
For Each file In fldr.Files
'Open the file
Set wb = Workbooks.Open(file.Path)
'## You will need to modify this line to refer to the correct
' module name and macro name:
Application.Run "Personal.xlsb!Module1.Macro6"
'Close the file (it was saved in Macro6 already)
wb.Close
Next
Set file = Nothing
Set fldr = Nothing
Set fso = Nothing
End Sub
NBI normally do not recommend relying on ActiveWorkbookbut I'm not going to sift through hundreds of lines of your code to optimize it. I will recommend that you read THISon how (and why) to avoid Selectand Activatemethods.
注意我通常不建议依赖，ActiveWorkbook但我不会筛选数百行代码来优化它。我会建议您阅读本关于如何（为什么），以避免Select和Activate方法。
