AppActivate inconsistent behavior

[Guest]

I'm using Access 97 and trying to use AppActivate "Microsoft Access" to
return focus to Access from Excel.

I'm not getting consistent behavior - sometimes it runs and other times I
get the message "An Error occurred - Invalid procedure call or argument".
When it does run without giving me the Invalid proc error it still isn't
quite the way I want it as it does not return to to Access to display the
message - I have to manually select Access from my task bar in order to see
the message, hit ok and continue. I use this same App Activate "Microsoft
Access" code at the end of the running of the 5 worksheets and display a done
message. The only difference is that in this case the done message is
displayed via a form vs msgbox.

My app outputs query results to Excel and I'm trying to handle error
messages in the event that there are no rows to output or the Excel worksheet
is already opened. Once Excel is opened it displays the applicable worksheet
then outputs another (up to 5 worksheets when the user hits applicable
button).

I've thought about changing all my messages to use a form but I really think
it should be easier than that. Any ideas on how to fix this problem would be
greatly appreciated. Thanks.

 

[Guest]

I was able to solve my problem. I think I was focusing too much on the
AppActivate. I'm guessing that what was happening was that the code was
running ahead and when it reached the AppActivate "Microsoft Access", Excel
had not always opened and thus the inconsistent behaviour when I was trying
to return to Access. By putting DoEvents in after Get Object
(,"Excel.Application") XLApp.Visible and CreateObject(,"Excel.Application") I
seemed to have corrected my problem

For reference, here's the code I ended up with:

On Error GoTo Err_OpenExcelFiles

Dim XLApp As Excel.Application

Set XLApp = GetObject(, "Excel.Application")
XLApp.Visible = True
DoEvents

If strExcelFileName <> "" Then
XLApp.Workbooks.Open FileName:=strExcelFileName
End If

Exit_OpenExcelFiles:
Set XLApp = Nothing
Exit Sub

Err_OpenExcelFiles:

If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
DoEvents
Resume
Else
If Err.Number = 1004 Then
AppActivate "Microsoft Access"
Exit Sub
End If
End If

End Sub