Sign in to follow this  
Followers 0
PhilHibbs

Updating Excel VBA code

11 posts in this topic

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

You can find complete documentation on the MSDN

Share this post


Link to post
Share on other sites

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)

$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!

Share this post


Link to post
Share on other sites

Bump! Can anyone help?

Share this post


Link to post
Share on other sites

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

Set excel security options to low.

What error exactly do you get?

Share this post


Link to post
Share on other sites

Works now - thanks

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