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

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
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

Board Regular
Joined
Sep 15, 2016
Messages
86
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.

Code:
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

Board Regular
Joined
Sep 15, 2016
Messages
86
:LOL:
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.

Code:
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.

Code:
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
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

Board Regular
Joined
Sep 15, 2016
Messages
86
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
 

Forum statistics

Threads
1,081,992
Messages
5,362,601
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top