How to return user to Access after getting Excel worksheets

[Mo]

I have an application where I'm opening up several Excel worksheets from
Access. My queries are running fine, Excel opens the applicable worksheet(s)
but once I open Excel, excel is being displayed and I don't know how to
return control to Access in order to display a message via a form. My
message runs but the user can't see it because Excel is running on top.
Here's what I'm doing thus far.

from a form level procedure I call the following:

Sub OpenExcelFiles(strExcelFileName As String)

On Error GoTo Err_OpenExcelFiles

Dim XLApp As Excel.Application

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

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

Exit_OpenExcelFiles:
Exit Sub

Err_OpenExcelFiles:
If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
Resume
Else
MsgBox Error$ & ". Error Number " & Err.Number
Resume Exit_OpenExcelFiles
End If


End Sub

.....and then I try to display the following message once code returns to the
form level procedure except I don't see it because Excel is still being
displayed.

If strReportAll <> "Y" Then
[Forms]!frmLoadMsg.lblDisplayMsg.Caption = "All Done!"
DoEvents
Sleep = Timer
Do
Loop Until Timer >= Sleep + 2 'sleep for 2 seconds so user can see screen
Forms![frm_Input_Pricing]![txtCLIENT].SetFocus
End If

At one point, I switched around my message and call to display the message
indicating the query was done and outputting to Excel and it works well for 1
query but when I run multiple queries all calling this procedure I only want
to display 1 message at the end, after the excel worksheets have already been
opened.

Any assistance gratefully appreciated. Thanks.

 

[Kevin K. Sullivan]

I don't think you need the line:

XLApp.Visible = True

, it seems to be setting the focus to Excel. You can also investigate the
AppActivate statement, which is often used to set the focus *away* from
Access, but can be used to return the focus to Access, such as

AppActivate "Microsoft Access"

You may need to change the argument if you have changed the application
title.

HTH,

Kevin

 

[Mo]

Thanks Kevin

The AppActivate "Microsoft Access" got me closer to where I want to be. Now
my problem is that when I return to Access it's returning me to a code window
vs the form. I should also mention that I'm using Access 97. Any hints for
this one?

I found I still need to use the XLApp.Visible = True
because I do want my users to be able to access Excel from the task bar and
in some situations I actually do want to leave them in Excel.

I don't think you need the line:

XLApp.Visible = True

, it seems to be setting the focus to Excel. You can also investigate the
AppActivate statement, which is often used to set the focus *away* from
Access, but can be used to return the focus to Access, such as

AppActivate "Microsoft Access"

You may need to change the argument if you have changed the application
title.

HTH,

Kevin

I have an application where I'm opening up several Excel worksheets from
Access. My queries are running fine, Excel opens the applicable
worksheet(s)
but once I open Excel, excel is being displayed and I don't know how to
return control to Access in order to display a message via a form. My
message runs but the user can't see it because Excel is running on top.
Here's what I'm doing thus far.

from a form level procedure I call the following:

Sub OpenExcelFiles(strExcelFileName As String)

On Error GoTo Err_OpenExcelFiles

Dim XLApp As Excel.Application

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

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

Exit_OpenExcelFiles:
Exit Sub

Err_OpenExcelFiles:
If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
Resume
Else
MsgBox Error$ & ". Error Number " & Err.Number
Resume Exit_OpenExcelFiles
End If


End Sub

....and then I try to display the following message once code returns to
the
form level procedure except I don't see it because Excel is still being
displayed.

If strReportAll <> "Y" Then
[Forms]!frmLoadMsg.lblDisplayMsg.Caption = "All Done!"
DoEvents
Sleep = Timer
Do
Loop Until Timer >= Sleep + 2 'sleep for 2 seconds so user can see
screen
Forms![frm_Input_Pricing]![txtCLIENT].SetFocus
End If

At one point, I switched around my message and call to display the message
indicating the query was done and outputting to Excel and it works well
for 1
query but when I run multiple queries all calling this procedure I only
want
to display 1 message at the end, after the excel worksheets have already
been
opened.

Any assistance gratefully appreciated. Thanks.