Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace Module (Excel VBA)

    What is the easiest way to replace a module in many excel files in one folder with a new one. I need to make a change in the code in the Test_Performance module. I have exported the module and made the changes but I don't want to open each file and make the change.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace Module (Excel VBA)

    Try code like the following.

    Notes:
    - Added: You must set a reference (in Tools | References... in the Visual Basic Editor) to Microsoft Visual Basic for Applications Extensibility 5.3.
    - I would test on a copy of the folder first, to see if the code does what you intend.
    - Make sure that none of the workbooks is in use when running the code.
    - If some of the workbooks are protected (the workbook itself or the VBA project), you will have to adapt the code to deal with that.
    - You must, of course, substitute the appropriate paths.

    Sub ReplaceModule()
    Dim wbk As Workbook
    Dim vbc As VBComponent
    Dim strPath As String
    Dim strFile As String
    Dim strNewModule As String
    Dim f As Boolean

    On Error GoTo ErrHandler

    ' Substitute the path and file name of the modified exported module.
    strNewModule = "C:ExcelTest_Performance.bas"

    ' Substitute the path of the folder to be modified. Trailing backslash is required.
    strPath = "C:Excel"

    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbk = Workbooks.Open(strPath & strFile)
    f = False
    For Each vbc In wbk.VBProject.VBComponents
    If vbc.Name = "Test_Performance" Then
    wbk.VBProject.VBComponents.Remove vbc
    wbk.VBProject.VBComponents.Import strNewModule
    f = True
    Exit For
    End If
    Next vbc
    wbk.Close SaveChanges:=f
    strFile = Dir
    Loop

    ExitHandler:
    On Error Resume Next
    Set vbc = Nothing
    wbk.Close SaveChanges:=False
    Set wbk = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Module (Excel VBA)

    Thanks for help, I get compile error on vbc As VBComponentUser-defined type not defined

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace Module (Excel VBA)

    Yes, I forgot to tell you to set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library, sorry about that. I edited my original reply, but you had already read it by then, apparently.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Module (Excel VBA)

    That worked but I get a programmatic access to project not tested. I guess that is a password thing. Let's say the password is "testing".

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace Module (Excel VBA)

    It sounds more like your security level is too high for this to work. You didn't specify which version of Excel you are using, but try this:
    - Select Tools | Macro | Security...
    - In the Security Level tab, set macro security to Low.
    - In the Trusted Sources tab, tick "Trust access to Visual Basic project"
    - Click OK.
    This is temporary only, I recommend setting macro security to Medium after you're finished with this. Low is too risky.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Module (Excel VBA)

    Thanks Hans, you rock.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Replace Module (Excel VBA)

    I do the same thing for a bunch of workbooks I have.

    Is there any way to specify the password for the VBA project?

    What I need to do is to unlock the project, change the code and lock the project again to stop prying users. Assume that i know the project password.

    Regards,
    Kevin Bell

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace Module (Excel VBA)

    There is no programmatic access to the VB Project password, you must use the reputedly flaky SendKeys. See here for an example. I haven't tested it, but Bill Manville knows his stuff (he's an Excel MVP.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •