App Activation Problem

[Keithr]

Can anyone work out why the following code fails to re-activate the Access
application. The code fails at the point where it tells the XL object to
save the file in workbook format. I've tried using AppActivate instead of
SetFocusAPI, to no avail. The Debug.Print gets the correct application name
and xlFound gets to true, but nothing else works on the xl object.
When the code stops on the SaveAs line, I can get into Debug mode and then
tell the code to continue (F5) - everything works OK. I know about the
dealyed registration in ROT problem, but as far as I can tell, the SetFocus
should get around that by de-focusing from Excel - except that it doesn't.
The embedded OWC spreadsheet is in use for a variety of reasons, not least
of which is speed of response scrolling through records combined with the
ability to get at most of the Excel maths library from an Access form. The
export button is an option for the user to export the currently-displayed
data.

Any ideas welcome
Thanks
Keith

Option Compare Database
Option Explicit

Private Declare Function SetFocusAPI Lib "user32" Alias _
"SetForegroundWindow" (ByVal hwnd As Long) As Long
Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Dim ws As OWC11.Spreadsheet

Private Sub cmdExport_Click()
Dim fName As String, xl As Object
Dim i As Integer, xlFound As Boolean

' replace as necessary with filename selector code
fName = GetFName(, MyDocsPath(), "XLS")

If fName = "" Then Exit Sub
ws.Export

On Error GoTo ErrorPoint
Do Until xlFound
Set xl = GetObject(, "Excel.Application")
Debug.Print xl.Name
If Not xl Is Nothing Then
xlFound = True
Else
Sleep 100
End If
Loop
On Error GoTo 0

If xlFound Then
SetFocusAPI Application.hWndAccessApp
xl.ActiveWorkbook.SaveAs fName, -4143 ' normal workbook format
End If

ExitPoint:
If Not xl Is Nothing Then Set xl = Nothing
Exit Sub

ErrorPoint:
Select Case Err.Number
Case 91, 429
Resume Next
Case Else
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitPoint
End Select
End Sub

 

[SteveM]

Try switching the lines so that you save the xl doc before setting focus to
Access.

If xlFound Then
xl.ActiveWorkbook.SaveAs fName, -4143 ' normal workbook format
SetFocusAPI Application.hWndAccessApp
End If

Steve

 

[Keithr]

Steve
That helped, along with one other change - see attached code. The
re-arrangement of the set focus works properly as you suggested, but the
alteration to the error handler keeps the sequence going until Excel has a
chance to get started properly before the focus is switched. as soon as that
happens the eror handler keeps things going again (from the save line) until
Excel registers properly into the ROT as a running instance (I think - it
works anyway). Thanks for the help.

Keith

Private Sub cmdExport_Click()
Dim fName As String, xl As Object
Dim i As Integer, xlFound As Boolean

' replace as necessary with filename selector code
fName = GetFName(, MyDocsPath(), "XLS")

If fName = "" Then Exit Sub
ws.Export

On Error GoTo ErrorPoint
Do Until xlFound
Set xl = GetObject(, "Excel.Application")
SetFocusAPI Application.hWndAccessApp
xl.ActiveWorkbook.SaveAs fName, -4143 ' normal workbook format
If Not xl Is Nothing Then xlFound = True
Loop
On Error GoTo 0

If xlFound Then
SetFocusAPI Application.hWndAccessApp
xl.ActiveWorkbook.SaveAs fName, -4143 ' normal workbook format
End If

ExitPoint:
If Not xl Is Nothing Then Set xl = Nothing
Exit Sub

ErrorPoint:
Select Case Err.Number
Case 91, 429
Sleep 100
Resume 0
Case Else
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitPoint
End Select
End Sub