Excel 2010 VBA 宏更改另一个文件中的模块内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18523172/
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
Excel 2010 VBA macro to change the content of a module in another file
提问by Adrian Gornall
I have developed a macro to mass update files in a file location. Im using the following code, which works perfectly, however the Edit part of the script requires changes in a VBA module in each of the files for the changes in the call edit macro to work. How can i also mass update the module contents when performing the mass file update.
我开发了一个宏来批量更新文件位置中的文件。我使用以下代码，它完美地工作，但是脚本的编辑部分需要在每个文件中的 VBA 模块中进行更改，以便调用编辑宏中的更改起作用。执行批量文件更新时，如何批量更新模块内容。
Sub Auto_open_change()
Dim WrkBook As Workbook
Dim StrFileName As String
Dim FileLocnStr As String
Dim LAARNmeWrkbk As String
PERNmeWrkbk = ThisWorkbook.Name
FileLocnStr = "C:\Users\gornalla\Desktop\PER Update" 'ThisWorkbook.Path
Dim StrFile As String
StrFile = Dir(FileLocnStr & "\*.xlsm")
Do While Len(StrFile) > 0
DoStuff (FileLocnStr & "\" & StrFile)
StrFile = Dir
Loop
End Sub
Private Sub DoStuff(StrFileName)
Workbooks.Open (StrFileName)
'Workbooks(StrFileName).Activate
ActiveSheet.Unprotect ("147258369")
Sheets("Property Evaluation Report").Select
ActiveSheet.Unprotect ("147258369")
Call Edit
ActiveWorkbook.RefreshAll
Sheets("Property Evaluation Report").Select
ActiveSheet.Protect Password:="147258369", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
ActiveWorkbook.Close
Application.SendKeys ("Enter")
End Sub
回答by techturtle
While it is possible to modify your code by using code, it is not a very good practice to use (see hereand herefor some references, if you insist). Instead, it would be better to store the value in a more mutable location and reference it from there, or just capture it as input from the user.
虽然可以通过使用代码来修改您的代码，但这不是一个很好的做法（如果您坚持，请参阅此处和此处以获取一些参考资料）。相反，最好将值存储在更易变的位置并从那里引用它，或者只是将其捕获为用户的输入。
You could read in the data from a text file that is stored in a common and unlikely-to-change location, though this adds an extra level of complication. Instead, I would suggest just creating a hidden column or hidden worksheet that you can reference in the code. Either could even be locked and protected if you wanted, rather than just hidden.
您可以从存储在常见且不太可能更改的位置的文本文件中读取数据，尽管这会增加额外的复杂性。相反，我建议只创建一个可以在代码中引用的隐藏列或隐藏工作表。如果您愿意，甚至可以锁定和保护其中一个，而不仅仅是隐藏。
The advantage to using a hidden sheet or column is that the data is attached to the workbook (an external file is not), and it can also persist changes between sessions. You can update the value in that field, so that the next time you access the file it remembers the last one you used.
使用隐藏的工作表或列的优点是数据附加到工作簿（外部文件不是），并且它还可以在会话之间保持更改。您可以更新该字段中的值，以便下次访问该文件时，它会记住您上次使用的文件。
