VBA Macro to open excel file and replace VBA module with update

[Forgone]

I do not know if this is possible but can only ask.
I've got a series of workbooks that have a module which contains UDF's
in a module.
I've since discovered that one of the UDF's wasn't calculating
correctly and had to make a change, since then, I've now got to go
over every workbook that I created with the old UDF and update it.
Was hoping that there is a way to programatically open up each
workbook and replace the existing VBA module with a new one to save
time...

 

[Forgone]

I do not know if this is possible but can only ask.
I've got a series of workbooks that have a module which contains UDF's
in a module.
I've since discovered that one of the UDF's wasn't calculating
correctly and had to make a change, since then, I've now got to go
over every workbook that I created with the old UDF and update it.
Was hoping that there is a way to programatically open up each
workbook and replace the existing VBA module with a new one to save
time...

Ok... have found a way to delete a VBA module.....

http://www.teachexcel.com/free-excel-macros/m-146,delete-vba-module-excel-from-macro-free.html

Sub Delete_Module()
Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

End Sub

 

[Forgone]

Ok... have found a way to delete a VBA module.....

http://www.teachexcel.com/free-excel-macros/m-146,delete-vba-module-e...

Sub Delete_Module()
Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

End Sub

Macro to copy a module......

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub

 

[Forgone]

Macro to copy a module......

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
    TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
    Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

Never mind...... I found that it was much easier to do it manually by
copying and pasting via the VBA editor as I also had to update the
formulas to include the additional range to look at in each workbook
as well.