vba VBA宏可批量更新同一位置的多个文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17461935/
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
VBA macro to mass update multiple files in same location
提问by Adrian Gornall
Very new to this so please help. Im trying to mass update files in a static folder location, many files in one folder.
对此很陌生，所以请帮忙。我试图批量更新静态文件夹位置中的文件，一个文件夹中的多个文件。
What i want to do is
我想做的是
- run VBA macro in Excel 2010 to goto a network location folder,
- open the first file in the folder.
- Unprotect the workbook and worksheets call another marco to run changes
- then protect the worksheet close the file
- and then move onto the next file in the folder until all files have been corrected.
- 在 Excel 2010 中运行 VBA 宏以转到网络位置文件夹，
- 打开文件夹中的第一个文件。
- 取消保护工作簿和工作表调用另一个 marco 来运行更改
- 然后保护工作表关闭文件
- 然后移动到文件夹中的下一个文件，直到所有文件都已更正。
I have created the marco to make the changes, this is called "Edit"
我创建了 marco 来进行更改，这称为“编辑”
File types are xlsm and the workbook and worksheet are password protected How can i automatically run the macro to goto the network location and in series open each file, unprotect, call the macro, then re protect the document close file and move onto the next file until they are all updated.
文件类型为 xlsm，工作簿和工作表受密码保护如何自动运行宏以转到网络位置并依次打开每个文件，取消保护，调用宏，然后重新保护文档关闭文件并移至下一个文件直到全部更新完毕。
Sub Auto_open_change()
Dim WrkBook As Workbook
Dim StrFileName As String
Dim FileLocnStr As String
Dim LAARNmeWrkbk As String
PERNmeWrkbk = ThisWorkbook.Name
StrFileName = "*.xlsx"
FileLocnStr = ThisWorkbook.Path
Workbooks.Open (FileLocnStr & "\" & StrFileName)
Workbooks(StrFileName).Activate
With Application.FindFile
SearchSubFolders = False
LookIn = "Network location"
Filename = "*.xlsm"
If .Execute > 0 Then
Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
WrkBook.Worksheets(1).Select
ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
Next i
Else
Debug.Print "There were no files found."
End If
Im managing to unprotect the file update and reprotect the file fine, just cant get the file from the network location.
我设法取消保护文件更新并重新保护文件，只是无法从网络位置获取文件。
采纳答案by Farfromunique
I'm using Excel 07, which doesn't allow Application.FindFile, so I can't test this. However, I believe the issue may be that you need to Setthe variable Wrkbook, not just assign it.
我正在使用 Excel 07，它不允许 Application.FindFile，所以我无法对此进行测试。但是，我相信问题可能是您需要Set变量Wrkbook，而不仅仅是分配它。
Change
改变
WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
to
到
Set WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
and let me know how that turns out!
让我知道结果如何！
