Run-time error 462 and and Run-time error 429 1

[ztchnc]

I'm doing some excel automation through an access form and I've run into this problem today. Everytime my code runs through the fIsAppRunning("Excel") code (which I find is very useful) I get the following errors on the GetObject(,"Excel.Application") line. The weird thing is this doesn't happen when I have excel open, it only happens when I have excel closed. I made sure to check my code and reset any objects I created to nothing at the end.

I have put a piece of my code and the fIsAppRunning function I found online.

'***************** My Code Start ***************
Sub Add_To_Sum_Sheet(SecRCat As String, strFile As String)

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objWkSht As Excel.Worksheet
Dim boolXl As Boolean

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXl = False
Else
Set objXL = CreateObject("Excel.Application")
boolXl = True
End If

Set objWkb = objXL.Workbooks.Open(strFile)
..... (there is more) .. but I didn't think it was necessary
'***************** My Code End ***************


'***************** Code Start ***************
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Private Const SW_HIDE = 0
Private Const SW_SHOWNORMAL = 1
Private Const SW_NORMAL = 1
Private Const SW_SHOWMINIMIZED = 2
Private Const SW_SHOWMAXIMIZED = 3
Private Const SW_MAXIMIZE = 3
Private Const SW_SHOWNOACTIVATE = 4
Private Const SW_SHOW = 5
Private Const SW_MINIMIZE = 6
Private Const SW_SHOWMINNOACTIVE = 7
Private Const SW_SHOWNA = 8
Private Const SW_RESTORE = 9
Private Const SW_SHOWDEFAULT = 10
Private Const SW_MAX = 10

Private Declare Function apiFindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Private Declare Function apiSendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal Msg As Long, ByVal _
wParam As Long, lParam As Long) As Long

Private Declare Function apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal Hwnd As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias _
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function apiIsIconic Lib "user32" Alias _
"IsIconic" (ByVal Hwnd As Long) As Long

Function fIsAppRunning(ByVal strAppName As String, _
Optional fActivate As Boolean) As Boolean
Dim lngH As Long, strClassName As String
Dim lngX As Long, lngTmp As Long
Const WM_USER = 1024
On Local Error GoTo fIsAppRunning_Err
fIsAppRunning = False
Select Case LCase$(strAppName)
Case "excel": strClassName = "XLMain"
Case "word": strClassName = "OpusApp"
Case "access": strClassName = "OMain"
Case "powerpoint95": strClassName = "PP7FrameClass"
Case "powerpoint97": strClassName = "PP97FrameClass"
Case "notepad": strClassName = "NOTEPAD"
Case "paintbrush": strClassName = "pbParent"
Case "wordpad": strClassName = "WordPadClass"
Case Else: strClassName = vbNullString
End Select

If strClassName = "" Then
lngH = apiFindWindow(vbNullString, strAppName)
Else
lngH = apiFindWindow(strClassName, vbNullString)
End If
If lngH <> 0 Then
apiSendMessage lngH, WM_USER + 18, 0, 0
lngX = apiIsIconic(lngH)
If lngX <> 0 Then
lngTmp = apiShowWindow(lngH, SW_SHOWNORMAL)
End If
If fActivate Then
lngTmp = apiSetForegroundWindow(lngH)
End If
fIsAppRunning = True
End If
fIsAppRunning_Exit:
Exit Function
fIsAppRunning_Err:
fIsAppRunning = False
Resume fIsAppRunning_Exit
End Function
'******************** Code End ****************
I appreciate any help you can give. Thanks.

 

[RoyVidar]

It is probably due to either not releasing the objects correctly, or usage of implicit referencing, i e errors in the part you found not necessary to show. See Excel Automation Fails Second Time Code Runs, though using With Blocks sometimes also can give this result Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic.

Using GetObject/CreateObject should normally do what the function from Dev Ashis performs, for instance like in this thread707-893313

Roy-Vidar

 

[ztchnc]

Thanks RoyVidar for the help, I looked at both threads and I believe that I've followed their recommended guidelines. I have checked and made sure that I released all the objects but maybe there is something I am not seeing. I normally set any worksheet and workbook object back to nothing and quit the excel application object as while.

...
objWkb.Save
objWkb.Close

Set objWkSht = Nothing: Set objAltSht = Nothing
Set objTemp = Nothing 'WorkSheet

Set objWkb = Nothing 'Workbook

If boolXl Then objXL.Application.Quit
Set objXL = Nothing
...

Everything in my code is working and I've been just using an open instance of Excel to test my code. Another interesting thing is if I don't have excel open and I get an error on the line where it is supposed to get the Object, I guess because it thinks Excel is already open, I try to step back and go through the fisAppRunning function and it returns false and so then it creates a new object. So now I'm not sure if it is my code or the Dev Ashis function.

Thanks Again

 

[RoyVidar]

Oups - the second reference is wrong, should have been Automation Doesn't Release Excel Object from Memory.

I'm guessing that the fIsAppRunning doesn't work correctly becaues of either implicit referencing, or with blocks (leaving an instance of Excel in memory). I don't use this function, but the approach in my third reference (and following the Microsoft advice on fully qualified referencing and avoiding with blocks). Without seing the code, I don't know.

Roy-Vidar