Updating Excel VBA code

[PhilHibbs]

I have a load of Excel spreadsheets with VBA code attached, and I want to update them all to the latest version of the VBA code. AutoIt seems like the tool for the job. Has anyone done anything like this before?

I'm thinking like this...

1. Place the new version of the VBA code in the clipboard

2. Open the .xls to be updated

3. Run the AutoIt script which...

3a. Sends Alt-F11 to open the VBA macro editor

3b. Switches between the code windows, attempting to work out which window contains the module that is being updated

3c. Select all and paste clipboard contents

3d. Close the VBA macro editor

4. Save and close

3b is probably the hardest step. I could include snippets of the old version(s) in the new code and search for these. Ideally I would have some kind of structured header, but I don't have that right now and I want to update the code in several dozen spreadsheets.

[Juvigy]

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
ReplaceMacro() 

Func ReplaceMacro()
    $oExcel.WorkBooks.Open($FileName)
    $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
    For $oModule in $oModules
        If $oModule.Type = 1 Then
            $oModules.Remove($oModule)
;            Exitloop; uncomment to remove only first VB macro found
        EndIf
    Next
    $oModules.Import("C:\VBA code file.bas")
    $oExcel.Run("Macro1")
    $oExcel.ActiveWorkbook.Save
    $oExcel.ActiveWorkbook.Close
    $oExcel.Quit
EndFunc

This will remove the existing macro and import the new macro from a file.

Edited January 6, 2010 by Juvigy

[PhilHibbs]

Awexome! Thanks a lot, that looks great! I tried to do it from within VB using the Excel4MacroSheets property but that got me nowhere.

[PhilHibbs]

Except... that does not give the new module the same name as the old module. I suppose I could rename the last module, assuming the newly imported module would be the last one in the list. I'll update this post with any solution that I come up with.

Update: How do I access the last element in an array? Update2: Found it, UBound function

Edited January 6, 2010 by PhilHibbs

[PhilHibbs]

Update: How do I access the last element in an array? Update2: Found it, UBound function

...now I hit the problem that $oModules isn't an array. What it is, and can I specifically access the last object? Where can I find documentation on the VBComponents class?

[PhilHibbs]

OK here's my current solution, which relies on the newly imported module being the last one in the $oModules collection. At present, it needs the Module Name to be hard-coded into the script.

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$FileName = FileOpenDialog("Select Excel File", "C:\", "Excel Workbooks (*.xls)", 1 )
$ModuleName = "MyModule"
$ModuleCode = "c:\vba.txt"

ReplaceMacro( $FileName, $ModuleName, $ModuleCode ) 

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

Edited January 7, 2010 by PhilHibbs

[Juvigy]

$oModules is a collection of objects. It is a little different then arrays.

You can find complete documentation on the MSDN

[happy2help]

Hi

I think I should revive this thread as I'm using most of the coding!

This script works (just) on my machine (XP sp3 and Office 2007 sp2)

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$FileName = FileOpenDialog("Select Excel File", "My Documents", "Excel Workbooks (*.xls;*.xlsx;*.xlsm)", 1 )
$ModuleName = "MyModule"
$ModuleCode = "B:\Scripts\Macro.txt"

$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
AdlibRegister("Myadlib")
WinWait("Microsoft Excel", "Ready to continue", 300)
Send("{SPACE}")
Sleep(30000)
WinWait("Microsoft Excel", "Ready to continue", 300)
Send("{SPACE}")
$oExcel.ActiveWorkbook.Close
$oExcel.Quit
FileRecycle($FileName)

Func Myadlib()
    If WinExists("Microsoft Office Excel", "A formula or sheet you") Then
        WinActivate("Microsoft Office Excel", "A formula or sheet you")
        Send("{SPACE}")
    EndIf
    If WinExists("Microsoft Office Excel - Compatibility Checker") Then
        WinActivate("Microsoft Office Excel - Compatibility Checker")
        Send("+{TAB 2}{SPACE}")
        Sleep(500)
        Send("{TAB 2}{SPACE}")
    EndIf
    If WinExists("Microsoft Office Excel", "Do you want Excel to recalculate") Then
        WinActivate("Microsoft Office Excel", "Do you want Excel to recalculate")
        Send("n")
        Sleep(500)
    EndIf
    If WinExists("Microsoft Office Excel", "Data may exist in the sheet") Then
        WinWait("Microsoft Office Excel", "Data may exist in the sheet")
        MsgBox(0,"working","",1)
        WinActivate("Microsoft Office Excel", "Data may exist in the sheet")
        Send("{ENTER}")
        WinWaitActive("Microsoft Office Excel", "Data may exist in the sheet",5)
        Send("{ENTER}")
        WinWaitActive("Microsoft Office Excel", "Data may exist in the sheet",5)
        Send("{ENTER}")
    EndIf
EndFunc

But it errors on my boss's laptop (Win7 and same Office)

At first it errored on $oExcel.WorkBooks.Open($FileName) as Win7 uses virtual folders. If you go directly to My Documents then it's fine.

Once I figured that out, it now errors on the next line $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

It says $oModules = $oExcel.ActiveWorkbook.VBProject^ERROR

HELP! We need to use this from next week!

[happy2help]

Bump! Can anyone help?

[Juvigy]

Put "require admin" in the beginning of the script.

Set excel security options to low.

What error exactly do you get?

[happy2help]

Works now - thanks