Hi group, I need your advice.

Once a workbook has been developed and placed into production (at many 
sites) does anyone know a method or a tool that will allow a module to be 
updated?

Similar to MS Access front-end/back-end where the data is split from the 
code.

Thanks for your help.

Rick 

An XLA or COM add-in is the preferred method for this type of
situation. The data reside in XLS workbooks and all the code, user
interface, etc, resides in the add-in. When a change of code is
required, you just send out a new add-in, thus not disturbing any data
workbooks. 

However, if you do need to replace a code module in a workbook, the
best way is to create a workbook that contains the new version of the
module and code to replace the old module with the new module. Create
a workbook named ModuleTransfer.xls and insert two code modules it the
VBProject. The first module in this workbook is the module that is to
be replaced in the other workbooks. The next module contains the code
to transfer the module. It can have any name. In the code below,
change REPLACE_MODULE_NAME to the name of the module that is to be
removed and replaced.

You would then distribute the ModuleTransfer.xls workbook to all the
users with a big fat button on the first sheet to kick off the
UpdateModule procedure.

''''''''''''''''''''''''''''''
' BEGIN CODE
''''''''''''''''''''''''''''''
Sub UpdateModule()
Dim WB As Workbook
Dim FName As Variant
Dim ModFileName As String
Dim N As Long
Const REPLACE_MODULE_NAME = "modSomeModule" '<<<<< CHANGE

ModFileName = ThisWorkbook.Path & "\" & REPLACE_MODULE_NAME & ".bas"
ThisWorkbook.VBProject.VBComponents(REPLACE_MODULE_NAME).Export _
    Filename:=ModFileName

FName = Application.GetOpenFilename( _
        filefilter:="Excel Files,*.xls;*.xlsx;*.xlsm;*.xlsb", _
        MultiSelect:=True)
If IsArray(FName) Then
    For N = LBound(FName) To UBound(FName)
        Set WB = Workbooks.Open(FName(N))
        With WB.VBProject.VBComponents
            On Error Resume Next
            Err.Clear
            If WB.VBProject.Protection = vbext_pp_none Then
                .Remove .Item(REPLACE_MODULE_NAME)
                If Err.Number = 0 Then
                    .Import ModFileName
                End If
            End If
        End With
        WB.Close savechanges:=True
    Next N
Else
    Exit Sub
End If
Kill ModFileName
End Sub
''''''''''''''''''''''''''''''
' END CODE
''''''''''''''''''''''''''''''

This code exports REPLACE_MODULE_NAME  from this workbook to a text
file located in the same folder as this workbook. Then, it prompts the
user for any number of workbooks whose modules are to be replaced. It
removes REPLACE_MODULE_NAME from the workbook and imports the module
from the temp file. Finally, after all the workbooks have been
processed, it Kills the temp file. 

Note that the VBProject must not be locked. If so, it is skipped and
the code module will not be replaced.

You can find tons more information about working with the VBA editor
objects and the code objects of workbooks at
www.cpearson.com/Excel/VBE.aspx


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com









On Thu, 15 Apr 2010 15:41:48 -0400, "rick" <someone@microsoft.com>
wrote:

>Hi group, I need your advice.
>
>Once a workbook has been developed and placed into production (at many 
>sites) does anyone know a method or a tool that will allow a module to be 
>updated?
>
>Similar to MS Access front-end/back-end where the data is split from the 
>code.
>
>Thanks for your help.
>
>Rick 

Thank you very much Chip! I will try both methods.
Rick

"Chip Pearson" <chip@cpearson.com> wrote in message 
news:n7res5hbe9h0ibbq8kj7i1fb0kraa5mnbs@4ax.com...
> An XLA or COM add-in is the preferred method for this type of
> situation. The data reside in XLS workbooks and all the code, user
> interface, etc, resides in the add-in. When a change of code is
> required, you just send out a new add-in, thus not disturbing any data
> workbooks.
>
> However, if you do need to replace a code module in a workbook, the
> best way is to create a workbook that contains the new version of the
> module and code to replace the old module with the new module. Create
> a workbook named ModuleTransfer.xls and insert two code modules it the
> VBProject. The first module in this workbook is the module that is to
> be replaced in the other workbooks. The next module contains the code
> to transfer the module. It can have any name. In the code below,
> change REPLACE_MODULE_NAME to the name of the module that is to be
> removed and replaced.
>
> You would then distribute the ModuleTransfer.xls workbook to all the
> users with a big fat button on the first sheet to kick off the
> UpdateModule procedure.
>
> ''''''''''''''''''''''''''''''
> ' BEGIN CODE
> ''''''''''''''''''''''''''''''
> Sub UpdateModule()
> Dim WB As Workbook
> Dim FName As Variant
> Dim ModFileName As String
> Dim N As Long
> Const REPLACE_MODULE_NAME = "modSomeModule" '<<<<< CHANGE
>
> ModFileName = ThisWorkbook.Path & "\" & REPLACE_MODULE_NAME & ".bas"
> ThisWorkbook.VBProject.VBComponents(REPLACE_MODULE_NAME).Export _
>    Filename:=ModFileName
>
> FName = Application.GetOpenFilename( _
>        filefilter:="Excel Files,*.xls;*.xlsx;*.xlsm;*.xlsb", _
>        MultiSelect:=True)
> If IsArray(FName) Then
>    For N = LBound(FName) To UBound(FName)
>        Set WB = Workbooks.Open(FName(N))
>        With WB.VBProject.VBComponents
>            On Error Resume Next
>            Err.Clear
>            If WB.VBProject.Protection = vbext_pp_none Then
>                .Remove .Item(REPLACE_MODULE_NAME)
>                If Err.Number = 0 Then
>                    .Import ModFileName
>                End If
>            End If
>        End With
>        WB.Close savechanges:=True
>    Next N
> Else
>    Exit Sub
> End If
> Kill ModFileName
> End Sub
> ''''''''''''''''''''''''''''''
> ' END CODE
> ''''''''''''''''''''''''''''''
>
> This code exports REPLACE_MODULE_NAME  from this workbook to a text
> file located in the same folder as this workbook. Then, it prompts the
> user for any number of workbooks whose modules are to be replaced. It
> removes REPLACE_MODULE_NAME from the workbook and imports the module
> from the temp file. Finally, after all the workbooks have been
> processed, it Kills the temp file.
>
> Note that the VBProject must not be locked. If so, it is skipped and
> the code module will not be replaced.
>
> You can find tons more information about working with the VBA editor
> objects and the code objects of workbooks at
> www.cpearson.com/Excel/VBE.aspx
>
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
>
>
>
>
> On Thu, 15 Apr 2010 15:41:48 -0400, "rick" <someone@microsoft.com>
> wrote:
>
>>Hi group, I need your advice.
>>
>>Once a workbook has been developed and placed into production (at many
>>sites) does anyone know a method or a tool that will allow a module to be
>>updated?
>>
>>Similar to MS Access front-end/back-end where the data is split from the
>>code.
>>
>>Thanks for your help.
>>
>>Rick