Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Module not file when open the excel file | Excel Discussion (Misc queries) | |||
Ghost Links which force Excel to update on File Open | Excel Programming | |||
Macro to replace a VBA module? | Excel Programming | |||
Replace Excel File Open with Windows Explorer | Excel Discussion (Misc queries) | |||
Automate open file, update links, run macro, close and save file | Excel Programming |