Minimizing windows using VBA
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Minimizing windows using VBA
I use Skype to send SMS messages using VBA from within my database. To do that I start Skype using VBA. However I need to immediately minimize the Skype window also using VBA so I can see the underlying dialog box that my code calls up. Anyone have any clues how to do that (simply & easily) using VBA?
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
Sorry for the late reply. I haven't been able to find an answer to your question. Perhaps you could approach it from the other side and bring Access to the front:
AppActivate "Microsoft Access"
AppActivate "Microsoft Access"
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Ha! Lateral thinking, great. Not ideal, but great nonetheless.
Problem is that Skype takes anywhere from 5 to 10 seconds to come up, depending upon whether it is currently open or not.
To over come that I used a delay and then tried using AppActivate on Skype with the 'Wait' argument set to 'True', then using AppActivate on Access. But the Skype window title uses its version number and the trademark mark of superscript TM. As the version number will change with a new version I opted to use the ID generated by the Shell function, but it gives the error; 'Invalid procedure call or argument'
Problem is that Skype takes anywhere from 5 to 10 seconds to come up, depending upon whether it is currently open or not.
To over come that I used a delay and then tried using AppActivate on Skype with the 'Wait' argument set to 'True', then using AppActivate on Access. But the Skype window title uses its version number and the trademark mark of superscript TM. As the version number will change with a new version I opted to use the ID generated by the Shell function, but it gives the error; 'Invalid procedure call or argument'
Code: Select all
Dim SkypeID As Variant
SkypeID = Shell("C:\Program Files\Skype\Phone\Skype.exe", vbMinimizedNoFocus)
Start = Timer
Do While Timer < Start + 11: Loop
AppActivate SkypeID, True
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
Why do you need to activate Skype? By the way, the True argument in AppActivate makes it wait until the calling aplication, in this case Access, has the focus, then switches to the called application (Skype).
I'd insert DoEvents into the loop - this gives other applications a chance:
I'd insert DoEvents into the loop - this gives other applications a chance:
Code: Select all
Start = Timer
Do While Timer < Start + 11
DoEvents
Loop
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
I don't need to activate Skype, just need it running not in the foreground. I am trying everything I can think of to get Skype either minimized or at least (as per your suggestion) to the background. I definitely do not want the Access code to continue until Skype had been sent to the background because an Access dialog box will be hidden by Skype. Just using AppActivate ("Contacts") ('Contacts' is the name of my Db) didn't work, Skype opened and stayed on top.
So I tried AppActivate to make sure Skype was in the foreground, then AppActivate on Access to get it to the foreground. But no luck there.
I have discovered one thing. AppActivate SkypeID doesn't give an error if Skype was opened by the Shell function. But Skype still doesn't get put to the background. If Skype was already running and the Shell function simply brings it up, SkypeID won't work, but neither will AppActivate "Skypetm [3] - myUserName". "Skypetm [3] - myUserName" being the title of the Skype window
I'm at a bit of a loss. (Not for the first time).
So I tried AppActivate to make sure Skype was in the foreground, then AppActivate on Access to get it to the foreground. But no luck there.
I have discovered one thing. AppActivate SkypeID doesn't give an error if Skype was opened by the Shell function. But Skype still doesn't get put to the background. If Skype was already running and the Shell function simply brings it up, SkypeID won't work, but neither will AppActivate "Skypetm [3] - myUserName". "Skypetm [3] - myUserName" being the title of the Skype window
I'm at a bit of a loss. (Not for the first time).
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
You don't have to specify the entire title, just the beginning, so try
AppActivate "Skype"
AppActivate "Skype"
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Thanks Hans. The font of all VBA knowledge as always - truly amazing.
AppActivate "Skype" worked, but didn't give me the desired effect. I only tried using AppActivate "Skype" to get me to a known situation. From there I used AppActivate "Contacts" ('Contacts' is my Db). However AppActivate "Contacts" just plain doesn't work. Skype stays in the foreground. I even increased the time interval to 12 seconds - the Beep sounds a long time after Skype opens - but AppActivate "Contacts" doesn't work!
After the code has run, AppActivate "Contacts" works if run from the Immediate window. AppActivate "Contacts" also works just fine elsewhere in my Db.
Very frustrating.
AppActivate "Skype" worked, but didn't give me the desired effect. I only tried using AppActivate "Skype" to get me to a known situation. From there I used AppActivate "Contacts" ('Contacts' is my Db). However AppActivate "Contacts" just plain doesn't work. Skype stays in the foreground. I even increased the time interval to 12 seconds - the Beep sounds a long time after Skype opens - but AppActivate "Contacts" doesn't work!
After the code has run, AppActivate "Contacts" works if run from the Immediate window. AppActivate "Contacts" also works just fine elsewhere in my Db.
Very frustrating.
Code: Select all
Start = Timer
Do While Timer < Start + 12
DoEvents
Loop
Beep
'''AppActivate "Skype"
AppActivate "Contacts"
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Nope; "Invalid Procedure call"
I always get this if I use AppActivate "Microsoft Access" and have changed the title of an Access Db.
Still stymied by what should be a pretty simple requirement.
My workaround is to leave Skype in foreground, but to have it open in a corner of the computer screen (where I last closed it) so it won't cover any Access dialogs. (I only hope this position sticks after a reboot.) I then manually minimize Skype. Oh the ignominy of having to do something manually!
I always get this if I use AppActivate "Microsoft Access" and have changed the title of an Access Db.
Still stymied by what should be a pretty simple requirement.
My workaround is to leave Skype in foreground, but to have it open in a corner of the computer screen (where I last closed it) so it won't cover any Access dialogs. (I only hope this position sticks after a reboot.) I then manually minimize Skype. Oh the ignominy of having to do something manually!
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
That's one of the reasons for not changing the application title of an Access database...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Ah yes. But what a marvelous place to show the version of the Db. All my Dbs are constantly changing (I never stop fiddling/improving) and I run them on 3 computers. One of the Dbs ran on up to 10 computers. So having the same version on each computer is reasonably important.
EG, the title of the Db we have been discussing is - 'Contacts ver 7 May 11'.
Always visible no matter what is going on and extremely simple.
Thanks for your help Hans.
EG, the title of the Db we have been discussing is - 'Contacts ver 7 May 11'.
Always visible no matter what is going on and extremely simple.
Thanks for your help Hans.
Avagr8day, regards, Peter
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
I may have found a sort of solution in the web site below. Not a perfect solution, but better than nothing.
http://www.vbforums.com/showthread.php?t=360571
It shows a short piece of code that minimizes all open windows. However there is an embargo on reproducing it on other web pages, hence the url. I am darned if I can see how this works, but it sure does.
All I need from there is a way to bring Access back to the desktop. I tried AppActivate "Contacts" but that only activates Access in the taskbar still minimized.
Any ideas anyone?
http://www.vbforums.com/showthread.php?t=360571
It shows a short piece of code that minimizes all open windows. However there is an embargo on reproducing it on other web pages, hence the url. I am darned if I can see how this works, but it sure does.
All I need from there is a way to bring Access back to the desktop. I tried AppActivate "Contacts" but that only activates Access in the taskbar still minimized.
Any ideas anyone?
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
That code works by generating 4 keyboard events: depressing the Windows key, depressing the M key, releasing the M key and releasing the Windows key. The effect is the same as pressing Windows key+M, the shortcut for "Minimize All".
Try
RunCommand acCmdAppRestore
if you want to restore the Access application window to "normal" size, or
RunCommand acCmdAppMaximize
if you want to maximize it.
(And yes, I understand the reasons for setting the application title, and in fact, I do so for many of my databases. But you have to be aware of the disadvantages.)
Try
RunCommand acCmdAppRestore
if you want to restore the Access application window to "normal" size, or
RunCommand acCmdAppMaximize
if you want to maximize it.
(And yes, I understand the reasons for setting the application title, and in fact, I do so for many of my databases. But you have to be aware of the disadvantages.)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Thanks for the suggestions Hans.
I have tried all of these:
The Debug.Print ones are read only and can't help me. The others only effect the main Access window, not the application.
I have tried all of these:
Code: Select all
Access.Application.RunCommand acCmdAppMaximize
Debug.Print Access.AcWindowMode.acWindowNormal
Debug.Print Access.AcCommand.acCmdAppMaximize
RunCommand acCmdAppRestore
RunCommand acCmdAppMaximize
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
What is the difference between the main Access window and the application?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
My bad wording.
The Access main window is indeed restored or maximized, but stays minimized in the taskbar. If that makes sense.
IE if Access wasn't maximized, that is it's in the normal position, and the app is then minimized to the taskbar, and then the code 'Command acCmdAppMaximize' is run; Access stays in the taskbar. But when clicked on in the taskbar Access comes up as maximized.
The Access main window is indeed restored or maximized, but stays minimized in the taskbar. If that makes sense.
IE if Access wasn't maximized, that is it's in the normal position, and the app is then minimized to the taskbar, and then the code 'Command acCmdAppMaximize' is run; Access stays in the taskbar. But when clicked on in the taskbar Access comes up as maximized.
Avagr8day, regards, Peter
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Minimizing windows using VBA
You're correct. In an initial test it appeared to work, but now I get the same result as you. Sorry!
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Minimizing windows using VBA
Peter Kinross wrote:... So having the same version on each computer is reasonably important. ...
That's why we use a deployment manager which ensures that all workstations always have the same version of the database, and any components such as the security file that go with it. And we display a title form that shows the version on startup, which can also be displayed anywhere in the app with a single mouse click. There are a number of threads both here and on other forums that describe various approaches to that. That said, we also do typically rename the application, as in many cases clients run two or more databases, and the user needs to be able to determine which is which from the taskbar when both are open.HansV wrote:... (And yes, I understand the reasons for setting the application title, and in fact, I do so for many of my databases. But you have to be aware of the disadvantages.)
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- 2StarLounger
- Posts: 196
- Joined: 31 Mar 2011, 03:41
- Location: Perth, Western Australia
Re: Minimizing windows using VBA
For switching between applications, I prefer to use the pair of procedures as shown below. I cannot acknowledge the original authors as I have lost that information.
ShellAndWait won't return until the external application has completed being launched, so a wait loop isn't required.
myAppActivate will find nearly any window and, with a little teaking, could also minimize the window if it is found by using the SW_MINIMIZE constant. I haven't tested them with a Microsoft Access environment though.
Kevin
ShellAndWait won't return until the external application has completed being launched, so a wait loop isn't required.
myAppActivate will find nearly any window and, with a little teaking, could also minimize the window if it is found by using the SW_MINIMIZE constant. I haven't tested them with a Microsoft Access environment though.
Code: Select all
Option Explicit
Option Compare Text
Private Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetWindowLW Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nmaxCount As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function ShowWindow32 Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Private Declare Function SetForegroundWindow32 Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long
Private Const GWL_ID = (-12)
Private Const GW_HWNDNEXT = 2
Private Const GW_CHILD = 5
Public Sub myExcelActivate()
Dim i As Integer
i = 1
myAppActivate "Excel"
Do
If InStr(1, Windows(i).Caption, "Excel", vbTextCompare) > 0 Then Exit Do
i = i + 1
Loop Until i > Windows.Count
If i <= Windows.Count Then Windows(i).Activate
End Sub
Public Sub myAppActivate(appTitle As String, Optional Wait As Boolean)
Dim hWndArray() As Long
Dim iret As Long
Const SW_MAXIMIZE = 3
Const SW_MINIMIZE = 6
If Left(appTitle, 1) <> "*" Then appTitle = "*" & appTitle
If Right(appTitle, 1) <> "*" Then appTitle = appTitle & "*"
If FindWindowLike(hWndArray, 0, appTitle, "*", Null) > 0 Then
iret = SetForegroundWindow32(hWndArray(1))
iret = ShowWindow32(hWndArray(1), SW_MAXIMIZE)
End If
End Sub
'FindWindowLike
' - Finds the window handles of the windows matching the specified
' parameters
'
'hwndArray()
' - An integer array used to return the window handles
'
'hWndStart
' - The handle of the window to search under.
' - The routine searches through all of this window's children and their
' children recursively.
' - If hWndStart = 0 then the routine searches through all windows.
'
'WindowText
' - The pattern used with the Like operator to compare window's text.
'
'ClassName
' - The pattern used with the Like operator to compare window's class
' name.
'
'ID
' - A child ID number used to identify a window.
' - Can be a decimal number or a hex string.
' - Prefix hex strings with "&H" or an error will occur.
' - To ignore the ID pass the Visual Basic Null function.
'
'Returns
' - The number of windows that matched the parameters.
' - Also returns the window handles in hWndArray()
'
'----------------------------------------------------------------------
Private Function FindWindowLike(hWndArray() As Long, ByVal hWndStart As Long, _
WindowText As String, className As String, ID) As Long
Dim hwnd As Long
Dim r As Long
' Hold the level of recursion:
Static level As Long
' Hold the number of matching windows:
Static iFound As Long
Dim sWindowText As String
Dim sClassname As String
Dim sID
' Initialize if necessary:
If level = 0 Then
iFound = 0
ReDim hWndArray(0 To 0)
If hWndStart = 0 Then hWndStart = GetDesktopWindow()
End If
' Increase recursion counter:
level = level + 1
' Get first child window:
hwnd = GetWindow(hWndStart, GW_CHILD)
Do While hwnd <> 0
DoEvents ' Not necessary
' Search children by recursion:
r = FindWindowLike(hWndArray(), hwnd, WindowText, className, ID)
' Get the window text and class name:
sWindowText = Space(255)
r = GetWindowText(hwnd, sWindowText, 255)
sWindowText = Left(sWindowText, r)
sClassname = Space(255)
r = GetClassName(hwnd, sClassname, 255)
sClassname = Left(sClassname, r)
' If window is a child get the ID:
If GetParent(hwnd) <> 0 Then
r = GetWindowLW(hwnd, GWL_ID)
sID = CLng("&H" & Hex(r))
Else
sID = Null
End If
' Check that window matches the search parameters:
If sWindowText Like WindowText And sClassname Like className Then
If IsNull(ID) Then
' If find a match, increment counter and
' add handle to array:
iFound = iFound + 1
ReDim Preserve hWndArray(0 To iFound)
hWndArray(iFound) = hwnd
ElseIf Not IsNull(sID) Then
If CLng(sID) = CLng(ID) Then
' If find a match increment counter and
' add handle to array:
iFound = iFound + 1
ReDim Preserve hWndArray(0 To iFound)
hWndArray(iFound) = hwnd
End If
End If
' Debug.Print "Window Found: "
' Debug.Print " Window Text : " & sWindowText
' Debug.Print " Window Class : " & sClassname
' Debug.Print " Window Handle: " & CStr(hwnd)
End If
' Get next child window:
hwnd = GetWindow(hwnd, GW_HWNDNEXT)
Loop
' Decrement recursion counter:
level = level - 1
' Return the number of windows found:
FindWindowLike = iFound
End Function
Code: Select all
Option Explicit
Option Base 0
'-----------------------Windows 32-Bit API Function Declarations--------------------
Global Const NORMAL_PRIORITY_CLASS = &H20&
Global Const INFINITE = -1&
Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type
Type STARTUPINFO
cb As Long
lpReserved As String
lpdesktop As String
lptitle As String
dwX As Long
dwY As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttributes As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hstdInput As Long
hstdoutput As Long
hstderror As Long
End Type
Private Declare Function CreateProcessA Lib "kernel32" _
(ByVal lpApplicationName As Long, _
ByVal lpCommandline As String, ByVal lpProcessAttributes As Long, _
ByVal lpThreadAttributes As Long, ByVal bInheritHandles As Long, _
ByVal dwCreationFlags As Long, ByVal lpEnvironment As Long, _
ByVal lpCurrentDirectory As Long, lpStartUpInfo As STARTUPINFO, _
lpProcessInformation As PROCESS_INFORMATION) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" _
(ByVal hHandle As Long, _
ByVal dwmilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (hObject As Long) As Boolean
'----------------------------------------------------------------------
Public Sub ShellAndWait(AppCmdLine As String)
Dim NameOfProc As PROCESS_INFORMATION
Dim NameStart As STARTUPINFO
Dim rC As Long
NameStart.cb = Len(NameStart)
rC = CreateProcessA(0&, AppCmdLine, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, NameStart, NameOfProc)
rC = WaitForSingleObject(NameOfProc.hProcess, INFINITE)
rC = CloseHandle(NameOfProc.hProcess)
End Sub
-
- 5StarLounger
- Posts: 962
- Joined: 09 Feb 2010, 00:33
- Location: Patterson Lakes, Victoria, Australia
Re: Minimizing windows using VBA
Wow, thanks Kevin.
Problem: ShellAndWait makes Access 'Not Responding', IE nothing can be done, the whole application becomes inactive. Access stays that way until Skype is quit.
One question. Is the 'Option Base 0' needed in the declarations for that sub? Or would 'Option Compare Text' suffice? If so they can all go in the same module.
Problem: ShellAndWait makes Access 'Not Responding', IE nothing can be done, the whole application becomes inactive. Access stays that way until Skype is quit.
One question. Is the 'Option Base 0' needed in the declarations for that sub? Or would 'Option Compare Text' suffice? If so they can all go in the same module.
Avagr8day, regards, Peter