Change module in multiple workbooks

[vikingatc]

I know I read how to do this somewhere, but I can't remember where. Is there some way, I do remember it was through code, to change a VBA module in multiple workbooks. They are all in the same directory, if that helps. (Administrators have decided to add some functionality to spreadsheets after design was finished... gotta love that.) Thanks for any help

Ben

 

[Rofeu]

Hi,

you could save the module with the code for the functionality somewhere and run the following just changing the 2 constants as desired:

Sub Add_VBComponent()

Const cYourFolder As String = "D:\TESTFOLDER"
Const cYourAddedFunctionality As String = "D:\TESTFOLDER\AddedFunction.bas"

Dim File As Variant
Dim wbFile As Workbook

With Application.FileSearch
    .LookIn = cYourFolder
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
    For Each File In .FoundFiles
        Set wbFile = Application.Workbooks.Open(File)
        With wbFile
            .VBProject.VBComponents.Import cYourAddedFunctionality
            .Close True
        End With
    Next
End With

Set wbFile = Nothing
        
End Sub

Caveat 1: ONLY the workbooks to undergo this addition should be in cYourFolder
Caveat 2: You won't be able to do it if the VBProjects of the files are protected

Cheers,

Roel

 

[vikingatc]

Rofeu,
You're a lifesaver. Thanks. I hate to ask another question, but... how would I go about putting the new code in the Workbook_Close event?

 

[vikingatc]

What I am actually trying to do is change the Workbook Before Close event on all of these workbooks... crazy, I know... but there are a ton of them and changing them by hand is gonna be unfun.

 

[combo]

Search VBA forum707, see for instance thread707-614467

combo

 

[Rofeu]

Hi,

in that case you'll need to use AddFromString or AddFromFile. I advise the latter. The thread that combo mentioned will tell you what you need to know.

Cheers,

Roel