Updating Excel VBA Modules

[PhilHibbs]

Perhaps this would be better done by having all the code in an Excel AddIn, but that is not where I am right now - I have dozens of spreadsheets all of which have their own copy of the macro code, and so if I make a change or fix a bug, updating them all is a pain.

Here is my AutoIt solution - an updated version of the one that I posted in the Q&A thread.

It can work in one of two ways:

1. Run it, select the file that contains the new VBA code, then select the Excel spreadsheet to update

2. Drag and drop a set of files onto a compiled version, and you will only be prompted for the VBA code

The reason I did the latter rather than just multi-selecting in the File Open Dialog is that I want to be able to process multiple files across multiple directories, so I search in Explorer and then drag a set of search results onto the executable.

The first line of the VBA code file must be in this format:

`Name=MyModule

This specifies the module that will be removed, and the newly imported module will be given this name.

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$ModuleCode = FileOpenDialog("Select Excel File", "C:\", "VBA Module Code (*.txt;*.bas)", 1 )
If @error Then Exit
$CodeFile = FileOpen( $ModuleCode, 0 )
$ModuleName = FileReadLine( $CodeFile )
FileClose( $CodeFile )
If StringLeft( $ModuleName, 6 ) = "'Name=" Then
  $ModuleName = StringMid( $ModuleName, 7 )
  If $CmdLine[0] > 0 Then
    $FileName = ""
    For $i = 1 To $CmdLine[0]
      $FileName &= "|" & $CmdLine[$i]
    Next
    $FileName = StringMid( $FileName, 2 ) ; remove the first | character
  Else
    $FileName = FileOpenDialog("Select Excel File", "C:\", "Excel Workbooks (*.xls)", 1 )
    If @error Then Exit
  EndIf
  $xlscount = 0
  For $xls In StringSplit( $FileName, "|", 2 )
    ReplaceMacro( $xls, $ModuleName, $ModuleCode )
    $xlscount += 1
  Next
  MsgBox( 1, "Finished", $xlscount & " files updated" )
Else
  MsgBox( 1, "Error", "First line must begin with 'Name="
EndIf

Func ReplaceMacro( $FileName, $ModuleName, $ModuleCode )
    $oExcel.WorkBooks.Open($FileName)
    $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
    For $oModule in $oModules
        If $oModule.Type = 1 And $oModule.Name = $ModuleName Then
            $oModules.Remove( $oModule )
        EndIf
    Next
    $oModules.Import( $ModuleCode )
    $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
    $ModuleCount = 0
     For $oModule in $oModules
        $ModuleCount += 1
        If $ModuleCount = $oModules.Count Then
          $oModule.Name = $ModuleName
        EndIf
    Next
    $oExcel.ActiveWorkbook.Save
    $oExcel.ActiveWorkbook.Close
    $oExcel.Quit
EndFunc

*Update*: I'm no longer using AutoIt to do this, I have an Excel spreadsheet that does it instead. Send me a private message if you want me to send you a copy.

Edited March 4, 2010 by PhilHibbs

[DisabledMonkey]

Looks Promising, really something that could save a lot of copy and pasting.

Only problem is I can't get it to run.

C:\Users\Monkey\Desktop\exceltest.au3 (8) : ==> The requested action with this object has failed.:

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

$oModules = $oExcel.ActiveWorkbook.VBProject^ ERROR

Your code looks solid after looking at the Visual Basic documentation.

Any idea what could be causing this problem?

Edit: Setting in Excel "Trust Access to the VBA project object model" needed to be enabled. Works without problems now.

Thanks,

Disabled Monkey

Edited January 9, 2010 by DisabledMonkey