CODEWITHSUNDEEP
pythonexcelvba
newbie
newbie

Reputation: 1412

How to update vba code in multiple excel macro-enabled files?

I have a bunch of excel files with same VBA code in all of them. They were created/cloned from a single file(template file) and then stored with some data in it. So I would like to update the VBA code in all the excel files including template file (an empty excel file with no data in it). Is there a way to do the same in a single shot using python script or something.

Upvotes: 3

Views: 4062

Answers (2)

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1203

When is possible I try to have separate .xlsx files for data entry and separate .xlsm program files for operating with the entered data, but recently I need to solve a similar to this question problem when I need constantly update code in the template and this code must be updated in created from this empty of data template multiple files by a lot of steps of data entry. The requirement was that this file can work on users' PC without any additional files.

We used the solution when the template is copied into a separate folder and sheets from workbook with loaded data were copied into this template, but in this case, left links to the old workbook in some sheets formulas and buttons with assigned macros. To enter data from scratch into the updated template wasn't a good solution either.

I decided to solve the problem with the program which copies updated modules from template file into the selected file. My code is below:

Sub ControlSelectedWorkbookModulesUpdatingFromSourceWorkbook()

Dim sourceWorkbook As Workbook, destinationWorkbook As Workbook
Dim pathToSourceWorkbook As String
Dim fileDialog As Office.fileDialog, pathToDestinationWorkbook As String

Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
With fileDialog
  .AllowMultiSelect = False
  .Title = "Please select the file."
  .Filters.Clear
  .Filters.Add "Excel", "*.xlsm"
  If .Show = True Then
    pathToDestinationWorkbook = fileDialog.SelectedItems(1)
  End If
End With

Application.ScreenUpdating = False

Set destinationWorkbook = Workbooks.Open(pathToDestinationWorkbook)

Call RemoveModules.RemoveAllVBA_ModulesFromDestinationWorkbook(destinationWorkbook)

pathToSourceWorkbook = "C:\Users\SourceFolder\SourceFileName.xlsm"
Set sourceWorkbook = Workbooks.Open(pathToSourceWorkbook)

Call CopyModules.CopyAllVBA_ModulesFromSourceWorkbookToDestinationWorkbook(sourceWorkbook, destinationWorkbook)

sourceWorkbook.Close SaveChanges:=False
destinationWorkbook.Close SaveChanges:=True

Application.ScreenUpdating = True

MsgBox "The end of program", vbInformation, ThisWorkbook.Name

End Sub

Code in Module RemoveModules:

Sub RemoveAllVBA_ModulesFromDestinationWorkbook(destinationWorkbook As Workbook)

'This will remove all modules including ClassModules and UserForms but keep all 
'object modules (sheets, workbook).

Dim module As Object

On Error Resume Next
For Each module In destinationWorkbook.VBProject.VBComponents
    destinationWorkbook.VBProject.VBComponents.Remove module
Next
On Error GoTo 0

End Sub

Code in Module CopyModules:

Sub CopyAllVBA_ModulesFromSourceWorkbookToDestinationWorkbook(sourceWorkbook As Workbook, destinationWorkbook As Workbook)

'This will copy from source workbook all modules except sheets, workbook.
'As direct copy is not allowed it is accomplished by 2 steps
'1 module from sourceWorkbook is exported into temporary file
'2 temporary file is imported into destinationWorkbook and then temporary file is deleted

Dim module As Object, pathToTemporaryFilesFolder As String, pathToTemporaryFile As String

pathToTemporaryFilesFolder = "C:\Users\OtherPartOfYourPath"

On Error Resume Next
For Each module In sourceWorkbook.VBProject.VBComponents
    If InStr(module.Name, "ThisWorkbook") = 0 And InStr(module.Name, "Sheet") = 0 Then
        pathToTemporaryFile = pathToTemporaryFilesFolder & "\" & module.Name & ".bas"
        module.Export (pathToTemporaryFile)
        destinationWorkbook.VBProject.VBComponents.Import (pathToTemporaryFile)
        Kill pathToTemporaryFile
    End If
Next
On Error GoTo 0

End Sub

To update code can only the user on which PC Excel is set - Trust access to the VBA project object model, has access to the template file and this program file, but then the file with data and updated code can be sent to other users and will work independently.

Upvotes: 2

Harassed Dad
Harassed Dad

Reputation: 4704

You can do it in VBA if the files have had the "Trust Access to the VBA project object model" option set on. http://www.cpearson.com/excel/vbe.aspx

Upvotes: 1

Related Questions