I have a need to provide a updates for the application I am developing.
I have used John Walkenbach's methodology, shown below, to attempt to do
do this. However, I am having a problem trying to Remove and Replace
the Microsoft Excel objects. The Modules replace fine, but there is a
"lot" of code in "sheet1" and in the "ThisWorkbook" objects.

When I export them, the become .cls files instead of .bas files. When I
invoke the .Remove method I immediately invoke the error process and the
..Import does not happen.

Any ideas?

Wes

********* Code ************

Option Base 1 ' set Array index base to 1 instead of zero
Sub UpdateModules()

Dim x As Variant
Dim strPath, strModname As String
Dim strModArray(1 To 4) As String ' Use 4 for testing
Dim i As Integer

strModArray(1) = "Sheet1"
strModArray(2) = "ThisWorkbook"
strModArray(3) = "Common"
strModArray(4) = "ExportData"

' Determine what module to import
' Not sure how to do this at this time
' Need to check some indicator to see if it needs
' to be replaced

strPath = "c:\"

' Make sure access to the VBProject is allowed
On Error Resume Next
Set x = ActiveWorkbook.VBProject
If Err < 0 Then
MsgBox "Your security settings do not allow this macro to run.",
vbCritical
On Error GoTo 0
Exit Sub
End If

' Send user a message telling him what we are doing
Msg = "This macro will replace the Validaton Macro Modules "
Msg = Msg & "with an updated Module." & vbCrLf & vbCrLf
Msg = Msg & "Click OK to continue."
If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then
For i = 1 To 4
strModname = strModArray(i)
strPath = "c:\"
Call ReplaceModule(strPath, strModname)
Next i
Else
MsgBox "Module not replaced!", vbCritical
End If
End Sub

Sub ReplaceModule(ByVal strPath, strModname As String)
Dim FileName As String
FileName = strPath & strModname & ".bas"

' Replace indicated Module in Application

Set VBP = ActiveWorkbook.VBProject

' set up for an error
On Error GoTo ErrHandle

' Use the VB Component object to delete and import the module
With VBP.VBComponents
.Remove VBP.VBComponents("strModname")
.Import FileName
End With

' Let the user know the module has been updated
MsgBox "The module has been replaced.", vbInformation
Exit Sub

ErrHandle:
' Did an error occur?
MsgBox "ERROR. The module was not replaced.", vbCritical
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!