How to Update an Excel file Module by using a sub routine from another workbook

[mahhdy]

Hello,

I have same file is used by my coworkers, sometimes I need to update my codes on those files, I am looking a way to send a new file with most updated codes, so they can update their files simply using a update button in my send file. I know this needs VBE coding, but I am not sure if they all have to configure their excel app to accept such a level of change, or no I can force excel from my file to delete a module in a selected file, and copy a new module or copy a new user form to that. If you can give me some advise or tips, I would be so much grateful.

Regards,
M

 

[mahhdy]

I think I figured it out, I need to teach them to enable the "Trust Access to VBA Project object Model"

and here is my Code, I can add the ones need to be updated to my array. This code imports it to thisworkbook, but I will make it to import on their selected file, using application.FileDialogue.

Sub UpdateTheCodes()
Dim ToUpdate, i&
    On Error Resume Next
    ToUpdate = Array("Mahhdy", "MMIDSheet")
    For i = LBound(ToUpdate) To UBound(ToUpdate)
        Workbooks("Developmental Sample.xlsm").VBProject.VBComponents(ToUpdate(i)).Export (ToUpdate(i))
        With Workbooks(ThisWorkbook.Name).VBProject
            .VBComponents.Remove .VBComponents(ToUpdate(i))
            .VBComponents.Import (ToUpdate(i))
        End With
    Next i

End Sub

Hello,

I have same file is used by my coworkers, sometimes I need to update my codes on those files, I am looking a way to send a new file with most updated codes, so they can update their files simply using a update button in my send file. I know this needs VBE coding, but I am not sure if they all have to configure their excel app to accept such a level of change, or no I can force excel from my file to delete a module in a selected file, and copy a new module or copy a new user form to that. If you can give me some advise or tips, I would be so much grateful.

Regards,
M

 

[mahhdy]

Here is my final Code, I just need to rename the worksheets of my sending file to the procedure or userforms I need to update.

Sub UpdateTheCodes()
Dim ToUpdate, i&, j&, FD As FileDialog, OSWFile As Workbook
    On Error Resume Next
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    ThisWorkbook.Activate
    ToUpdate = [index(Sheetnames,)] 'Array("Mahhdy", "MMIDSheet")
    With FD
        .AllowMultiSelect = True
        .Filters.Add "XLSM file", "*.xlsm"
        .ButtonName = "Select"
        .Title = "please Select you Latest OSW file for Updating the Code"
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then Exit Sub
        For i = 1 To .SelectedItems.count
            If Not AlreadyOpen(Dir(.SelectedItems(i))) Then
                Application.DisplayAlerts = False
                Workbooks.Open .SelectedItems(i), False, False, , , , True, , , True, False, , , , True
                Application.DisplayAlerts = True
            End If
            Set OSWFile = Workbooks(Dir(.SelectedItems(i)))
        
            For j = LBound(ToUpdate) To UBound(ToUpdate)
                ThisWorkbook.VBProject.VBComponents(ToUpdate(j)).Export (ToUpdate(j))
                With OSWFile.VBProject
                    .VBComponents.Remove .VBComponents(ToUpdate(j))
                    .VBComponents.Import (ToUpdate(j))
                End With
            Next j
        Next i
        
        MsgBox "Total of " & .SelectedItems.count & " file has been updated."
        
    End With
    
    
End Sub

I think I figured it out, I need to teach them to enable the "Trust Access to VBA Project object Model"

and here is my Code, I can add the ones need to be updated to my array. This code imports it to thisworkbook, but I will make it to import on their selected file, using application.FileDialogue.

Sub UpdateTheCodes()
Dim ToUpdate, i&
    On Error Resume Next
    ToUpdate = Array("Mahhdy", "MMIDSheet")
    For i = LBound(ToUpdate) To UBound(ToUpdate)
        Workbooks("Developmental Sample.xlsm").VBProject.VBComponents(ToUpdate(i)).Export (ToUpdate(i))
        With Workbooks(ThisWorkbook.Name).VBProject
            .VBComponents.Remove .VBComponents(ToUpdate(i))
            .VBComponents.Import (ToUpdate(i))
        End With
    Next i

End Sub

 

[Kyle123]

You don’t really want them enabling that - it’s turned off for a reason.

Why don’t you just use an add in? You could save it on a shared drive and when you update it, they’ll get the updated bersion

 

[mahhdy]

Hello,
Thanks for your note. We don't have any share Drive, like that. But I will ask them to turned off back. That's a good point.
Thanks

You don’t really want them enabling that - it’s turned off for a reason.

Why don’t you just use an add in? You could save it on a shared drive and when you update it, they’ll get the updated bersion