Sign in to follow this  
Followers 0
PhilHibbs

Updating Excel VBA Modules

2 posts in this topic

#1 ·  Posted (edited)

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.

$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 by PhilHibbs

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by DisabledMonkey

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0