PhilHibbs Posted January 7, 2010 Posted January 7, 2010 (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. 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 Posted January 8, 2010 Posted January 8, 2010 (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 January 9, 2010 by DisabledMonkey
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now