Code - Loop through userforms and upgrade.

[staticbob]

Guys,

I have about 75 copies of a workbook out in the field. I have a fix that needs to be applied to these. The fix involves replacing 20 user forms with new ones.

So, I have created a new workbook that contains the new ones, along with a dialogue box for the users to select the book to upgrade. (str_PWB)

What I now need to do is loop thru each of the 20 userforms in the original file (str_PWB) , delete them, then copy the new form out of the upgrade workbook (str_UPWB)

There are other userforms that I dont want to touch, I only need to upgrade the defined list of 20 forms.

Thanks
Bob

 

[Richie(UK)]

Hi Bob,

How about:

Sub Test()
    Dim varUFArray As Variant, lCnt As Long
    Dim wbkData As Workbook
    
    Set wbkData = Workbooks("deletemetoo.xls")
    varUFArray = Array("UserForm1", "UserForm3", "UserForm6", "UserForm7")
    
    For lCnt = LBound(varUFArray) To UBound(varUFArray)
        Call DeleteComp(wbkData, varUFArray(lCnt))
    Next lCnt
    
End Sub

Sub DeleteComp(wbk As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    
    On Error Resume Next
    Set VBComp = wbk.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        wbk.VBProject.VBComponents.Remove VBComp
    End If
    
End Sub

Just amend the names in the array to suit.

HTH

 

[staticbob]

Thanks Richie,

Doesn't that just delete the the userfrms ? It doesn't acually copy the new versions back from the current workbook.

I've got this far . . (EDIT - I've decided to just delete ALL forms, then copy them all back in)

Public Sub upgrade_forms(PWB As String, UGWB As Workbook)
Application.ScreenUpdating = False
Windows(PWB).Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
    If oVBComp.Type = 3 Then
    oVBComps.Remove oVBComp
    End If
Next oVBComp

MsgBox ("All old forms deleted")

That wipes out all the userforms from PWB, I then need to copy all of the forms from the current workbook back into PWB. . .

This is where I'm strugglin . . this code is total scribble at the moment, and I'm getting more confused.

Dim FName As String
'Dim VBComp As VBIDE.VBComponent

UGWB.Activate
    FName = "C:\code.txt"
    If Dir(FName) <> "" Then
        Kill FName
    End If
    For Each VBComp In .
        If VBComps.Type <> 3 Then
           VBComps.Export FName
           Workbooks(PWB).VBProject.VBComponents.Import FName
           Kill FName
        End If
    Next VBComps
End With

Thanks
Bob

 

[Richie(UK)]

Hi Bob,

Yes, it does just delete the userforms - I thought that was the part that you wanted help with.

OK, extending on the original I'd go with something like this:

Sub Test()
    Dim varUFArray As Variant, lCnt As Long
    Dim wbkData As Workbook
    
    Set wbkData = Workbooks("deletemetoo.xls")
    varUFArray = Array("UserForm1", "UserForm3", "UserForm6", "UserForm7")
    
    For lCnt = LBound(varUFArray) To UBound(varUFArray)
        Call DeleteComp(wbkData, varUFArray(lCnt))
        Call ExportImport(ThisWorkbook, wbkData, varUFArray(lCnt))
    Next lCnt
    
End Sub

Sub DeleteComp(wbk As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    
    On Error Resume Next
    Set VBComp = wbk.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        wbk.VBProject.VBComponents.Remove VBComp
    End If
    
End Sub

Sub ExportImport(wbkSource As Workbook, wbkTarget As Workbook, ByVal strCompName As String)
    Dim VBComp As VBComponent
    'set a reference to the VBA Ext lib
    Const strFName As String = "C:\tempcomp"
    
    On Error Resume Next
    Set VBComp = wbkSource.VBProject.VBComponents(strCompName)
    On Error GoTo 0
    If Not VBComp Is Nothing Then
        VBComp.Export FileName:=strFName
        wbkTarget.VBProject.VBComponents.Import FileName:=strFName
        Kill PathName:=strFName
    End If

End Sub

I like the modular approach, especially when it comes to debugging, but you can combine it into one routine if you wish. Note that the same array of names is used for each routine ie I am assuming that you replace UserForm1 with UserForm1. Amend to suit.

HTH

 

[staticbob]

Richie,

Thanks for the response again. I also prefer the modular approach, but this is really bugging me and I can't understand why my method isn't working !

I'm getting runtime error - Object doesn't support prop or method - on the line marked with ***

Any ideas ?

Public Sub upgrade_forms(PWB As String, UGWB As Workbook)
Application.ScreenUpdating = False
Windows(PWB).Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents

For Each oVBComp In oVBComps
    If oVBComp.Type = 3 Then
    oVBComps.Remove oVBComp
    End If
Next oVBComp

MsgBox ("All old forms deleted")

UGWB.Activate
Set oVBComps = ActiveWorkbook.VBProject.VBComponents
Const strFName As String = "C:\tempcomp"
For Each oVBComp In oVBComps
        *** oVBComps.Export Filename:=strFName
        Workbooks(PWB).VBProject.VBComponents.Import Filename:=strFName
        Kill PathName:=strFName
Next oVBComp
Application.ScreenUpdating = True

End Sub

 

[Richie(UK)]

Hi Bob,

Looks like a typo - you are using oVBComps instead of oVBComp (no 's').

HTH

PS: Just noticed one of your earlier queries also posted at VBAX. If you are going to cross-post your questions please provide links to the other sites used. Thanks.

 

[staticbob]

PS: Just noticed one of your earlier queries also posted at VBAX. If you are going to cross-post your questions please provide links to the other sites used. Thanks.

Good point, well put. I should know that by now.

Thanks
bob