Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace code module in distributed app?
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" 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace code module in distributed app?
Thank you very much Chip! I will try both methods.
Rick "Chip Pearson" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code in one Module referencing Public Variables Declared inanother Module failing | Excel Programming | |||
Find/Replace in Module | Excel Programming | |||
Replace module with code | Excel Programming | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
Run worksheet module code from workbook module? | Excel Programming |