Help Please: Run Excel based sub from Access using GetObject

[Perico]

I'm running Excel from Access 2k. (I must use late binding because early
binding results in Excel left Task Mgr. Processes after routine completes.)

Running looping code residing in Excel via Access is much faster than
running that code from Access. Thus, I must use late binding and I must run
code in Excel from Access.

Problem: With early binding (Application.Run "MyExcelBasedSubroutine") I get
no errors running the code. With late binding I do. But I must use late
binding as stated. I'm getting errors on both line ** and line ****. Please
help.

Code:

Set objExcel = GetObject(vFullPath)
Set objXLApp = objExcel.Parent

On Error GoTo 0
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
IStartedXL = True
End If

objXLApp.Visible = True
objExcel.Parent.windows(2).Visible = True


'faster to run Excel routine from within Excel:
If answr = "Res" Then
objXLApp.Run "cycleRes" 'sub is in Excel module "modCycle" '**
Else answr = "Nres" Then
objXLApp.Module("modCycle").Run "cycleNRes" '****
End If

 

[JLGWhiz]

And the error message is?

 

[Perico]

"Object does not support this property error 438."

Here is some simpler Access code I tested tonight and it too did not work.
As per articles re: expunging Excel from memory, I used late binding and set
no reference to the Excel Object library:

Sub Test()
Dim xlApp As Object
Dim oWb As Object
Dim oWs As Object
Set xlApp = GetObject("C:\Test\TestAutoma.xls")
Set oWb = xlApp.Workbook
Set oWs = xlApp.oWb("Sheet1")
xlApp.Visible = True
oWs.Visible = True
oWs.Application.Run "TestAutomation" 'modcycle module
End Sub

 

[Perico]

Disregard. This code from Access 97 Developer's Handbook, pg 432 worked,
with slight modification, Dimming as Object:

Sub Test()
On Error Resume Next
'Dim xlApp As Object
'Dim ref As Reference
'Set ref = References!Excel

Dim XL As Object
Set XL = GetObject("C:\Test\TestAutoma.xls", "Excel.Sheet")

XL.Application.Visible = True
XL.Application.Windows("TestAutoma.xls").Visible = True

XL.Application.Run "TestAutomation" 'modcycle module

XL.Close
Set XL = Nothing
End Sub