[Resolved] VBA/Office/Excel experts?

[Spiff59]

I have about 10,000 Excel spreadsheets that all contain the same small embedded macro.

I have an update for that macro and wanted to write a script to pass through all the directories and replace the macros.

Something like the following was my best guess:

$TargetFile = "C:\test.xls"
    $oExcel = ObjCreate("Excel.Application")
    With $oExcel
        .Visible = 0
        .WorkBooks.Open($TargetFile)
        .ActiveWorkbook.VBProject.VBComponents.Remove("Module1")
        .ActiveWorkbook.VBProject.VBComponents.Import("C:\Module1.bas")
;      .Workbooks.VBProject.VBComponents.Remove("Module1")
;      .Workbooks.VBProject.VBComponents.Import("C:\Module1.bas")
    EndWith

I'm getting a "The requested action with this object has failed." error with the caret pointing between "VBProject" and "VBComponents".

Does Excel not automatically have access to the VBIDE object models? Will I need to reference the VBIDE DLL directly?

Has anyone had any experience using the VBProject objects?

Thank you.

Edited August 5, 2008 by Spiff59

[ResNullius]

I have about 10,000 Excel spreadsheets that all contain the same small embedded macro.

I have an update for that macro and wanted to write a script to pass through all the directories and replace the macros.

Something like the following was my best guess:

$TargetFile = "C:\test.xls"
    $oExcel = ObjCreate("Excel.Application")
    With $oExcel
        .Visible = 0
        .WorkBooks.Open($TargetFile)
        .ActiveWorkbook.VBProject.VBComponents.Remove("Module1")
        .ActiveWorkbook.VBProject.VBComponents.Import("C:\Module1.bas")
;      .Workbooks.VBProject.VBComponents.Remove("Module1")
;      .Workbooks.VBProject.VBComponents.Import("C:\Module1.bas")
    EndWith

I'm getting a "The requested action with this object has failed." error with the caret pointing between "VBProject" and "VBComponents".

Does Excel not automatically have access to the VBIDE object models? Will I need to reference the VBIDE DLL directly?

Has anyone had any experience using the VBProject objects?

Thank you.

Installing a custom COM error handler yields the problem:

Programmatic access to Visual Basic Project is not trusted

Info at: http://support.microsoft.com/kb/282830

[Spiff59]

I saw a post yesterday where Jos suggested someone install a COM+ error handler to retrieve meaningful error information from this same type of error.

I was going to investigate the COM+ area of AutoIt's help section this morning and try to set one up.

Seems you beat me to it.

You Sir, are my hero!

Thank you.

Progress Report: Your advice got me farther along, but still no joy. I corrected the prior error and stuck in an error handler. Now I'm getting an 80020005 "Type Mismatch" with the caret at the very end of the line of code (past the "remove("Module1"))

Edited August 5, 2008 by Spiff59

[Spiff59]

Got it working...

Wish I could have found a reference for the VBProject object model somewhere on the web, but no luck.

From numerous searches, bits-and-pieces gleaned from here-and-there, and lots of trial-and-error, I came up with this.

Since I only have one code module in the workbook, it works without checking the module name. I left some of the other commands that worked with the VBProject object commented-out at the bottom in case anyone finds this of interest.

(Macro manipulation in the Word or Excel UDF maybe?)

$oExcel = ObjCreate("Excel.Application")
    With $oExcel
        .Visible = 0
        .WorkBooks.Open("C:\test.xls")
    EndWith
    
    $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
    For $y = 1 to $x
        Switch $oModules.Item($y).Type
            Case 1; BASIC
                $oModules.Remove($y)
            Case 100; Excel
        EndSwitch
    Next
    $oModules.Import("C:\Module1.bas")
;   $oExcel.ActiveWorkbook.Save 
    _ExcelBookClose($oExcel,1)

;
;   For $oModule In $oModules; alternate referencing with $oModule instead of $oModules($x)
;
;   $oModules.Item(1).CodeModule.CodePane.Show 
;   $oModules.Item(1).Activate 
;   $x = $oExcel.ActiveWorkbook.VBProject.VBComponents.Count
;
;   With $oModules.Item($y).CodeModule
;       .ReplaceLine (1 , "Sub SpellCheck()")
;       .DeleteLines (10, 1)
;       .InsertLines (7 , "TEST")
;   EndWith
;
;   ConsoleWrite("Excel Module: " & $oModules.Item($y).Name &   ", Type: " & $oModules.Item($y).Type  & @CR)
;
;   For $z = 1 to $oModules.Item($y).CodeModule.CountOfLines
;       ConsoleWrite("  " & $z & ":  " & $oModules.Item($y).CodeModule.Lines($z, 1) & @CR)
;   Next

Edited August 5, 2008 by Spiff59

[ResNullius]

Got it working...

That's good to hear!

Question for you though:

You say: "I have about 10,000 Excel spreadsheets that all contain the same small embedded macro."

Are those sheets for use by other people or only by yourself? I ask because it seems rather inefficient to manage it this way.

At our office, I have several macros that I like like to have available for everybody in all worksheets.

FWIW, what I did was create a worksheet that contains those macros, made it a hidden sheet, and saved it as an XLA on a network share.

Then I created a link in the users profiles at "@AppDataDir & "\Microsoft\EXCEL\XLSTART\" to the XLA.

So whenever Excel starts, the macros are loaded.

That way, when I want to make a change to the macro, I just have one sheet to update.

[Spiff59]

I'd chopped up that code as soon as I got it working to put up my findings here. Actually, I think I horked up my example when I trimmed off the fat. I ended up running something more like this:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
While 1
    GetFilename()
    If $EOF Then Exitloop
    ReplaceMacro()
WEnd
$oExcel.Quit
Exit

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:\Module1.bas")
    $oExcel.ActiveWorkbook.Save
    $oExcel.ActiveWorkbook.Close
EndFunc

It ran a lot faster once I pulled the ObjGet and $oExcel.Quit out of the loop and only started Excel once during the entire run.

Are those sheets for use by other people or only by yourself?

They are on a network file server, and accessed by many users.. They're what we call the "Patient Ledger". Each patient's folder has 3 different spreadsheets, this macro would only apply to the ledger. But, I'm betting a couple lines of code in the ledger worksheet could tell Excel to load the proper macro whenever the worksheet is opened, so per your suggestion, I'd have just one macro to update and it would instantly apply for all users. I don't expect to be updating this macro again soon, but if I have to, I might opt for the "shared" approach. I'm far from an Excel guru... I've just lately been getting moderately proficient with it.

[Juvigy]

Hi Spiff,

Could you please post more of the code as i get errors. For example:

Here - GetFilename()

Here - If $EOF

And most importantly here - $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

The first 2 i figured out how to bypass (or how to write myself)...

[Spiff59]

Those lines were just pseudocode, not the good, excel-related, stuff. I have spreadsheets in 4 different folders on the server depending if the patient is active, inactive,collections, or archived (our politically-correct way of saying "*never* coming back"). I think I threw something together using FileFindFirstFile and FileFindNextFile and FileGetAttrib to scan the folders on my file server and dump all the patient folder names (their chart number) into a text file. Then I just fed that text file into something like this:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$file = FileOpen("C:\patientchartnumbers.txt", 0)
While 1
        $sPatientChart = FileReadLine($file)
        If @error = -1 Then ExitLoop
        $PatientLedgerPath = "X:\Patients\" & $sPatientChart & "\Patient Ledger.xls"
;       ToolTip($sPatientChart, 160, 160)
        ReplaceLedger()
Wend
FileClose($file)
$oExcel.Quit
Exit

;---------------------------------------------------------------------------------------------------------------------
Func ReplaceLedger()
    $oExcel.WorkBooks.Open($sPatientPath)
    Local $oProject = $oExcel.ActiveWorkbook.VBProject
    Local $oModules = $oProject.VBComponents
    For $oModule in $oModules
        If $oModule.Type = 1 Then
            $oProject.VBcomponents.Remove($oModule)
;           Exitloop
        EndIf
    Next
    $oModules.Import("C:\Module1.bas")
    $oExcel.ActiveWorkbook.Save
    $oExcel.ActiveWorkbook.Close
EndFunc

I did simplify path names, cut out error-checking, etc, to make it more readable. But something like that worked fine for me. As far as getting an array returned by VBProject.VBComponents, I've never had a problem. You could stick an IsObj after your ObjCreate, as well as error code after your WorkBook.Open to make sure they are functioning correctly. You can usually find examples or error checking in the Excel UDF or at MSDN. I am using Office 2003 SP3, although I would think 2007 was backward-compatible. Hmm, I notice I did this one a little differently than my first example, with vbproject and vbcomponents never actually appearing in the same statement. Am just flying by the seat of my pants here, but did get it to work fine.

Edited October 27, 2008 by Spiff59

[Juvigy]

I found what was wrong in the link a few post up in the thread. It was this thing:

Office 2003 and Office XP

;1. Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.

;2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.

;3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

;Back to the top

;Office 2007

;1. Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click Application Options.

;2. Click the Trust Center tab, and then click Trust Center Settings.

;3. Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.

;4. Click OK.}