I know this post is really old and I doubt I will get a reply.. Can you post your working script for excel spreadsheets? This is exactly what I have been looking for. Good work!



Phil Hibbs wrote:

Fixed it - I cannot use the module name as a collection index, I needto loop
07-Jan-10

Fixed it - I cannot use the module name as a collection index, I need
to loop through them checking the Name property of the VBComponent.

Phil Hibbs.

Previous Posts In This Thread:

On Thursday, January 07, 2010 8:32 AM
Phil Hibbs wrote:

Updating the VBA code in multiple spreadsheets
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.

On Thursday, January 07, 2010 12:24 PM
Phil Hibbs wrote:

I am now trying to port this to Excel, and I am hitting a problem wherethe
I am now trying to port this to Excel, and I am hitting a problem where
the Remove method fails with "Object does not support this method or
property":

Sub UpdateVBA()
Dim oExcel As Application
Dim oComponent As Object

Set oExcel = New Excel.Application
Set oBook = oExcel.Workbooks.Open("C:\Test.xls", 0, False, , , ,
True)
Set oComponent = oBook.VBProject.VBComponents("TestModule")
oBook.VBProject.VBComponents.Remove (oComponent) ' <== FAIL
oBook.VBProject.VBComponents.Import ("C:\TestModule.txt")
oBook.VBProject.VBComponents
(oBook.VBProject.VBComponents.Count).Name = "TestModule"
oBook.Close
oExcel.Quit

End Sub

Any ideas?

Phil Hibbs.

On Thursday, January 07, 2010 12:24 PM
Phil Hibbs wrote:

Fixed it - I cannot use the module name as a collection index, I needto loop
Fixed it - I cannot use the module name as a collection index, I need
to loop through them checking the Name property of the VBComponent.

Phil Hibbs.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorials...sers-redu.aspx