Excel 2010 VBA macro to change the content of a module in another file

Question

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

Solution

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.

Comments

[David Zemens]

What have you tried so far? This should get you started. cpearson.com/excel/vbe.aspx