Problem with Events not firing when using Automation Object

[TomCarnahan]

Hi!

I am having difficulty with using Automation objects
between Excel and MS-Access.

I have three files, an MS-Access (.MDB) file, an Excel
(.XLS) workbook, and an Exel (.XLA) Add-In.

The .MDB file is the main file. In one of its functions,
the .MDB file launches the .XLS file as an Automation object. When the .XLS file opens, it's "Open_Workbook" event calls a

procedure in the .XLS' class modules (called "XLA_Interface&quot which loads and installs the .XLA file. Then, the .XLS file

calls a procedure in the .XLA file that loads a custom menu.

If I open the .XLS file directly, and invoke the procedure
in the "XLA_Interface" class module, the .XLA file loads
just fine. The "Install" event of the .XLA file loads the menu. However, if I launch the .XLS file from the Access .MDB file,

the .XLS "Open_Workbook" event executes fine until it gets to the point where the "XLA_Interface" class module is invoked. At

that point, execution passes on by the procedure call and no error is raised (Err = 0)

I suspect that there is something different about how an
Excel file behaves when it is opened by the user and when
it is opened as an Automation object that causes it to
skip certain code. For example, I know that if I put a
"Stop" command in the .XLS "Open_WOrkbook" event, it is
ignored when the workbook opens (even when events are enabled)

The VBA code I used in MS-Access is modeled after an example I found in the Help file on the subject of "GetObject" as

follows:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CODE SEGMENT RUNNING IN MS_ACCESS
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Module Declaration Area

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long, _
ByVal wParam as Long, _
ByVal lParam As Long) As Long

'''''''''''''''''''''''''''''
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

'''''''''''''''''''''''''''''
Sub GetExcel() 'MAIN Procedure in .MDB file.

Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.

On Error Resume Next ' Defer error trapping.


' Check for Microsoft Excel. If Microsoft Excel
' is 'running, enter it into the Running Object table.

DetectExcel 'Access procedure call (see above)

' Set the object variable to reference the .XLS workbook.
'The next line causes the Excel "Open_Workbook" command
' to fire in the .XLS file.

Set MyXL = Getobject("c:\....\MYTEST.XLS&quot

' Show Microsoft Excel through its Application property.
' Then show the actual window containing the file using
' the Windows collection of the MyXL object reference.

MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' At this point, my ACCESS VBA calls a
' procedure in
' the .XLS workbook. This procedure calls another
' procedure in the "XLA_Interface" class module
' which checks to make sure the .XLA is loaded
' and installed,
' then calls the procedure in the .XLA file.

MyXL.Application.Run _
(<name of procedure in .XLS>, _
<name of procedure in .XLA>)


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' THE .XLS &quot;Open_Workbook&quot; EVENT
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim MyXLA as New XLA_Interface

... 'Miscellaneous code that runs OK ...

'Hand-off to the procedure in the .XLS Class Module that
'handles all procedure calls to the .XLA file.

MyXLA.Run_My_Macro(<name of procedure in .XLA>)


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Class module &quot;XLA_Interface&quot; procedure in the .XLS file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Sub Run_My_Macro(str_Name_of_XLA_Macro as String)

Const str_XLA_FilePath as String = &quot;C:\ .... \ABC.XLA&quot;

'*** HERE is where the problem is ***
'The next line runs the desired procedure in the .XLA
'file IF the .XLS file is launched by the user
'directly, but NOT if the .XLS file is launched as an
'Automation object (in this case, by MS-Access).

Application.Run _
str_XLA_FilePath & _
&quot;!&quot; & _
str_Name_of_XLA_Macro

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END OF CODE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

There must be something that I am missing that is obvious, but that I am overlooking.

Can anyone give me a suggestion as how to proceed?

Thank you ahead of time for any help you can provide!




--- Tom