Minimizing windows using VBA

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Minimizing windows using VBA

Post by Peter Kinross »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

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"
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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'

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

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:

Code: Select all

Start = Timer
Do While Timer < Start + 11
    DoEvents
Loop
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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).
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

You don't have to specify the entire title, just the beginning, so try

AppActivate "Skype"
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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.

Code: Select all

Start = Timer
Do While Timer < Start + 12
    DoEvents
Loop
Beep
'''AppActivate "Skype"
AppActivate "Contacts"
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

How about

AppActivate "Microsoft Access"
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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!
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

That's one of the reasons for not changing the application title of an Access database...
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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.
Avagr8day, regards, Peter

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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?
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

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.)
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

Thanks for the suggestions Hans.
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
The Debug.Print ones are read only and can't help me. The others only effect the main Access window, not the application.
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

What is the difference between the main Access window and the application?
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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.
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Minimizing windows using VBA

Post by HansV »

You're correct. In an initial test it appeared to work, but now I get the same result as you. Sorry! :sad:
Best wishes,
Hans

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Minimizing windows using VBA

Post by Wendell »

Peter Kinross wrote:... So having the same version on each computer is reasonably important. ...
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.)
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.
Wendell
You can't see the view if you don't climb the mountain!

Becks
2StarLounger
Posts: 196
Joined: 31 Mar 2011, 03:41
Location: Perth, Western Australia

Re: Minimizing windows using VBA

Post by Becks »

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. :sad:
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

Kevin

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Minimizing windows using VBA

Post by Peter Kinross »

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.
Avagr8day, regards, Peter