Excel 2010 VBA宏可更改另一个文件中模块的内容 [英] Excel 2010 VBA macro to change the content of a module in another file

问题描述

我开发了一个宏来批量更新文件位置中的文件.我正在使用以下代码，效果很好，但是脚本的编辑"部分需要在每个文件的VBA模块中进行更改，才能使调用编辑宏中的更改生效.执行批量文件更新时，我还如何批量更新模块内容.

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.

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

推荐答案

虽然可以通过使用代码来修改代码，但是使用它并不是一种很好的做法(请参见此处(如果您坚持的话).相反，最好将值存储在一个更易变的位置并从那里引用它，或者仅将其捕获为来自用户的输入.

While it is possible to modify your code by using code, it is not a very good practice to use (see here and here for 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.

这篇关于Excel 2010 VBA宏可更改另一个文件中模块的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！