Execute Macro/Sub in Workbook2 from VBA in Workbook1 - PLEASE help?

[Myriad_Rocker]

Hey all...I've spent several hours trying to get this to work. I've searched Google, tried multiple ways to get it done, but I just can't do it.

I have a set of code that opens another workbook. I know it's open and I can see that it's open. I even have a loop that checks to make sure the file is done opening. Then I call a sub to execute a macro in that workbook I just opened. Below is that sub.

Public Sub RunPrePostMacros(MacroName)

    If MacroName <> "" Then      
        'Checking to see if the macro (proc) even exists
        If CBool(Len(ActiveWorkbook.VBProject.VBComponents(MacroName).Name)) = True Then
            Application.Run ("'" & ActiveWorkbook.Name & "'" & "!" & MacroName)
        
            'Wait for workbook to finish running macro
            Do Until ActiveWorkbook.Application.Ready = True
                'Do nothing, just wait
            Loop
        End If
    End If
End Sub

The if statement is testing as true because MacroName does exist. But then when it hits the run, it says Subscript out of range!!! Every time...

I know the MacroName is correct because the code just above the run validates as True. Any help here? PLEASE?

This is where the error is being thrown...

Application.Run ("'" & ActiveWorkbook.Name & "'" & "!" & MacroName)



I've also tried this and it doesn't work:

ActiveWorkbook.Application.Run (MacroName)

 

[Myriad_Rocker]

I started commenting some code out and found out that my problem is in the if statement that checks to see if the macro exists. I thought it was fine since when I put a break in the code, it was testing as true.

Anyway, you have to add a reference to the VBA extensibility package. However, I read that virus scanners will often delete anything that references the VBProject object model because of the inherent risks of how viruses propagate. It will just delete the code...which I don't want to happen. So...I need to find another way not using the VBA extensibility package to test for the existence of a module.