Replace Module (Excel VBA)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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”.

Reply | Quote

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.

Reply | Quote

Thanks Hans, you rock.

Reply | Quote

Thanks Hans, you rock.

Reply | Quote

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.

Reply | Quote

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”.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote