I asked about this a while ago, now I have a solution. I have used
AutoIt (http://www.autoitscript.com/) , but I think this could also be
done in VB.

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.

This is the code of the AutoIt script:

$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

--
Phil Hibbs.