Remove Userforms and Modules for each workbook in array

Question

I'm working with workbooks that all use the same userforms and modules. I've exported these userforms and modules to a folder on a shared drive.

I'd like to loop through every workbook in the folder, remove existing modules and userforms, and then import selected userforms and modules from a local folder.

Here's the code

Sub UpdateAllSheets()
'//THIS SUB REMOVES ALL USERFORMS AND MODULES FROM EACH WORKBOOK IN A 
SPECIFIED DESTINATION FOLDER
'//UPDATED VERSIONS OF EACH OF THESE USERFORMS AND MODULES ARE THEN IMPORTED FROM

Dim MyPath As String                                                                'Pathway where files are stored
Dim MyFiles() As String                                                             'Array where files in path are stored
Dim wbarr() As Object                                                               'Array for workbooks
Dim Element As Object

MyPath = "S:\Water TCM and Outreach\Data\Follow-Up\Daily Follow-Up (CM and FN Use)"
MyFiles = FindFilesinPath(MyPath)
Call OpenallFiles(MyPath, MyFiles)
wbarr = AssignBooks(MyFiles)

'Loop through all workbooks

For i = LBound(wbarr) To UBound(wbarr)
'Steps for this process:
    Debug.Print "Updating Elements in "; wbarr(i).name

    'Remove all existing Userforms and Modules from target workbook

    For Each Element In wbarr(i).VBProject.VBComponents
        wbarr(i).VBProject.VBComponents.Remove Element
    Next

    'Add updated Userforms and Modules from Destination folder
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\CalendarForm.frm"
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\CaseType.frm"
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\NewCase.frm"
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\NewEncounterShared.frm"
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\ModifyCase.frm"
    wbarr(i).VBProject.VBComponents.Import "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\CaseManagementPreReqs.bas"

    'Delete lines on "ThisWorkbook" for each workbook and import text from 'ThisWorkbook' cls file
    With wbarr(i).VBProject.VBComponents("ThisWorkbook").CodeModule
        .DeleteLines StartLine:=1, Count:=.CountOfLines
        .AddFromFile "S:\Water TCM and Outreach\Data\Follow-Up\Admin\CM UserForms and Modules\ThisWorkbook.cls"
    End With

Next i

'Move on to the next worksheet

MsgBox ("Update Process Complete: Fingers Crossed!")

End Sub

The line I'm having issue with is the following:

For Each Element In wbarr(i).VBProject.VBComponents
    wbarr(i).VBProject.VBComponents.Remove Element
Next

I'm getting the following error: Run-time error '5': Invalid procedure call or argument.

I think this has to do with trying to modify a workbook using its location in an array. Maybe I could create a holding variable for the workbook name then use Workbooks(TempName) or something like that.

There are several other functions here that I haven't shown. I don't believe there are any errors with them, as another macro I run uses the same functions to sift through all workbooks in a target folder.

[See this answer](https://stackoverflow.com/a/51938513/4088852). — Comintern, Aug 23 '18 at 15:56

Domenic · Accepted Answer · 2018-08-23T18:08:15.210

1

To remove only userforms and standard modules, you can test each Element for the type...

For Each Element In wbarr(i).VBProject.VBComponents
    If Element.Type = 1 Or Element.Type = 3 Then '1 = vbext_ct_StdModule; 3 = vbext_ct_MSForm
        wbarr(i).VBProject.VBComponents.Remove Element
    End If
Next

edited Aug 23 '18 at 18:08

answered Aug 23 '18 at 16:20

Domenic

7,844
2
9
17

Remove Userforms and Modules for each workbook in array

1 Answers1