Minimize Excel

S

slymeat

As part of a function I use, a few excel files are opened. The problem is
that Excel becomes the active window and the user is not seeing a
prompt/message box in Access that is used to complete the function. They only
see the prompt when they return to Access. Is there a way to launch the excel
files and then minimize Excel to the Taskbar while leaving the files open?
The user could then comply with the prompt in Access and then work away in
Excel by clicking on it in the Taskbar
 
G

GeoffG

You can use the SetForegroundWindow API function.

In the general declarations section at the top of the module, insert the
following declaration:

Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As
Long

Then call the function in your code like this:

Dim objXL As Excel.Application

Dim hWndAccess As Long
Dim hWndExcel As Long
Dim lngRetVal As Long

' Start Excel.

' Store Excel's Window handle:
hWndExcel = objXL.hWnd

' Store Access's Window Handle:
hWndAccess = Access.Application.hWndAccessApp

' Call the API function to give Access the focus
' and store the function's return value:
lngRetVal = SetForegroundWindow(hWndAccess)

' See if call to function worked:
If lngRetVal = 0 Then
' Call failed.
' Take appropriate action.
Else
' Show message in Access:
MsgBox "My Message"
End If

' Activate Excel:
lngRetVal = SetForegroundWindow(hWndExcel)
If lngRetVal = 0 Then
' Call failed.
End If

Geoff
 
S

slymeat

Geoff
Think I sorted the problem.

I'm a bit of a novice at VB so took me a while!

I changed the line to:

Dim objXL As Object

The code is working perfectly now. Thanks for the help.
 
S

slymeat

Thanks for the reply Geoff

I've used your suggestions but when I go to compile my function, I'm getting
a
"User defined type not defined" in respect of the line declaring the Excel
Application as an object:

Dim objXL As Excel.Application

Any suggestions?

Thanks again for the help

Andrew
 
G

GeoffG

Well done that man!

My guess is you've not set a reference to Excel. If you want to set a
reference, do this:

1. In the VBA editor, open the Tools menu and select References.
2. In the References dialog, select Microsoft Excel.

The advantages of having a reference are (1) you can declare variables as
objects within the object library you've referenced (eg Dim objXL as
Excel.Application) and (2) "Intellisense" then works in the VBA editor
(which means when you type a full stop after an object variable, you get to
see the object's properties, methods, etc, in a pop-up list - which makes
programming easier).

The requirement for setting a reference to an object library is that the
program (eg Excel) must be installed on all the machines you plan to run
your Access application on. Otherwise, declaring object variables as
"Object" is the right approach.

If you need more info, read about late- and early-binding.

Regards
Geoff
 
G

GeoffG

Well done that man!

My guess is you've not set a reference to Excel. If you want to set a
reference, do this:

1. In the VBA editor, open the Tools menu and select References.
2. In the References dialog, select Microsoft Excel.

The advantages of having a reference are (1) you can declare variables as
objects within the object library you've referenced (eg Dim objXL as
Excel.Application) and (2) "Intellisense" then works in the VBA editor
(which means when you type a full stop after an object variable, you get to
see the object's properties, methods, etc, in a pop-up list - which makes
programming easier).

The requirement for setting a reference to an object library is that the
program (eg Excel) must be installed on all the machines you plan to run
your Access application on. Otherwise, declaring object variables as
"Object" is the right approach.

If you need more info, read about late- and early-binding.

Regards
Geoff
 
Top