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

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

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

On Apr 8, 9:38*am, Forgone wrote:
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...acro-free.html

Sub Delete_Module()
Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents

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

End Sub

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

On Apr 8, 9:40*am, Forgone wrote:
On Apr 8, 9:38*am, Forgone wrote:

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...e-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).Exp ort strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub

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

On Apr 8, 9:42*am, Forgone wrote:
On Apr 8, 9:40*am, Forgone wrote:



On Apr 8, 9:38*am, Forgone wrote:

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...e-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).Exp ort 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.