Handle Other Applications Through Excel VBA

Share this

November 6, 2011

Last updated: 30/09/2017, 1 min read

Handle Other Applications Through Excel VBA


Introduction


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).


VBA code


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.


Downloads


Download

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

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • Hi, Amit,

    The code will invoce the keystroke ALT + TAB:

    SendKeys "%{TAB}"

    I tested with and without the True parameter at the end and it worked fine.
    Do you execute the code on the correct application window?

  • Amit Shukla says:

    Thanks to myengineeringworld.net
    It’s very helpful
    Sir “SendKeys (“%{F4}”), True” works fine,
    but “SendKeys (“%{TAB}”), True” isn’t working Pls suggest.
    Thanks, and Regards – Amit

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >