Handle Other Applications Through Excel VBA

During our everyday life in many cases, we have to deal with a repetitive task at our computers. The procedure of doing the same thing again and again apart from boring carries the risk of making a mistake. The good news is that with a little help from Excel (VBA) we can save some time by automating many tasks in external applications.

In the sample code below you will find a few techniques that might help you automate your daily routine. The sample macros demonstrate how to:

• Open an application (Shell command).
• Send text and shortcuts to an application (Sendkeys method).
• Control the mouse left and right click (Windows APIs).
• Wait for an action to complete (DoEvents, Application.Wait).

Here are the sample macros:

Option Explicit

'--------------------------------------------------------------------------------
'This module contains various macros that can be used in order to handle other
'applications from Excel. It was created only for demonstration purposes.
       
'Written By:    Christos Samaras
'Date:          07/11/2011
'Last Updated:  05/10/2014
'E-mail:        [email protected]
'Site:          https://myengineeringworld.net/////
'--------------------------------------------------------------------------------
    
'Declaring the necessary API functions, subs and constants.
#If VBA7 And Win64 Then

    'For 64 bit Excel.
    Private Declare PtrSafe Function SetCursorPos Lib "user32" _
                            (ByVal x As Long, _
                            ByVal y As Long) As Long
                           
    Private Declare PtrSafe Sub mouse_event Lib "user32" _
                            (ByVal dwFlags As Long, _
                            ByVal dx As Long, _
                            ByVal dy As Long, _
                            ByVal cButtons As Long, _
                            ByVal dwExtraInfo As LongPtr)

#Else

    'For 32 bit Excel.
    Private Declare Function SetCursorPos Lib "user32" _
                            (ByVal x As Long, _
                            ByVal y As Long) As Long
                            
    Private Declare Sub mouse_event Lib "user32" _
                            (ByVal dwFlags As Long, _
                            ByVal dx As Long, _
                            ByVal dy As Long, _
                            ByVal cButtons As Long, _
                            ByVal dwExtraInfo As Long)

#End If

Private Const MOUSEEVENTF_LEFTDOWN  As Long = &H2
Private Const MOUSEEVENTF_LEFTUP    As Long = &H4
Private Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Private Const MOUSEEVENTF_RIGHTUP   As Long = &H10

Sub OpenWord()
    
    'Opens MS Word using the Shell command and writes a sample text.
    
    'Shell command opens an application using its exact path. For example:
    'shell "C:Program Files (x86)Microsoft OfficeOffice15WINWORD.EXE", vbNormalFocus
    'Or we can use just the name of exe file like "WINWORD.EXE", as it shown here:
    shell "WINWORD.EXE", vbNormalFocus
    
    'Make an action but with a time delay (2 seconds here).
    DoEvents
    Application.Wait Now + TimeValue("00:00:02")
    SendKeys "{ENTER}", True
    
    'Send a keyboard press.
    SendKeys "My Text", True
    
    'Close a program (supposing that its window has the focus).
    SendKeys ("%{F4}"), True

End Sub

Sub MouseLeftClick()
    
    'Evokes a (left) mouse click at a specific position on the screen.
    
    'Set cursor position (width, height).
    SetCursorPos 660, 190
    
    'Send a down event (press the left button of the mouse).
    mouse_event MOUSEEVENTF_LEFTDOWN, 0&, 0&, 0&, 0&
    
    'And an up event (release the button).
    mouse_event MOUSEEVENTF_LEFTUP, 0&, 0&, 0&, 0&
    
End Sub

Sub MouseRightlick()

    'Evokes a right mouse click at a specific position on the screen.

    'Set cursor position (width, height).
    SetCursorPos 1020, 500
    
    'Send a down event (press the right button of the mouse).
    mouse_event MOUSEEVENTF_RIGHTDOWN, 0&, 0&, 0&, 0&
    
    'And an up event (release the button).
    mouse_event MOUSEEVENTF_RIGHTUP, 0&, 0&, 0&, 0&

End Sub

Sub NotepadExample()
    
    'Writes two lines in notepad.
            
    'Declare the necessary variables.
    Dim line1 As String
    Dim line2 As String
    
    'Pass the cells content into the variables.
    With Sheets("Handle Other Applications")
        .Activate
        line1 = .Range("A12").Value
        line2 = .Range("A13").Value
    End With
    
    'Use shell command to open the notepad.
    shell "notepad", vbNormalFocus
    
    'Send the first line, press enter and then send the second.
    SendKeys line1, True
    SendKeys "{ENTER}"
    SendKeys line2, True

End Sub

It should be highlighted that we don’t usually automate Word like this (i.e., OpenWord macro), but, instead, we use the CreateObject function/method. The file below contains all the above subroutines. More information about Sendkeys method you can find here.

The file can be opened with Excel 2007 or newer. Please enable macros before using it.